With BigQuery, you get to query external data sources instead of reloading the data in BigQuery. Here’s the GCP link on the same. In loading Google Sheets as a data source with below sample data with the date format in %m%d%E4Y [mm/dd/yyyy] format.

Solution from StackOverflow: https://stackoverflow.com/questions/66605012/bigquery-error-while-reading-tablecould-not-convert-value-to-date-error-inv/66609171#comment117788593_66609171

It looks straightforward but when you try to query the dates, you’ll get this error: 'Error while reading table: Could not convert value to date. Error: Invalid date'.

The StackOverflow solution I got focused on: Option 1 - Using the right schema either at the source “YYYY-MM-DD”. In this case, it would mean changing the date format in the Google Sheet OR Option 2 - Importing the date schema as a string format and then use “PARSEDATE('%m/%d/%Y',date_column)” in the query to convert the string > date format while running queries.