big query and google drive

[anigordel]

I wanna connect big query and google drive. when i create the table i can link the google sheet, but auto detected schema does not work and i can't see the data at all.
any ideas how can i do it?

[Aris_O]

Hi @anigordel,

Welcome back to Google Cloud Community!

BigQuery supports external tables over both personal Drive files and shared files. For more information on Drive, see Drive training and help.

This articles might help you:
https://cloud.google.com/bigquery/docs/connected-sheets?_ga=2.59542056.-480590913.1678892362
https://cloud.google.com/bigquery/docs/external-data-drive

[RobinKon12]

Hi,

Ensure that the Google Drive file is shared with the BigQuery service account (use the email format bigquery-[project-id]@developer.gserviceaccount.com). This allows BigQuery to access the file.

Also, check the sheet structure:
- Ensure that your Google Sheet has a clean and consistent format. The first row should contain column headers, and all subsequent rows should contain data.
- Remove any blank rows or columns, as these can interfere with schema detection.

If you don't want a complex manual configuration, you can take a look at different data integration tools. I can recommend Skyvia, as I use it myself and am satisfied with its functionality and simplicity. It has a mapping option, and you can configure it as you want.

[Nandha100]

Hi @anigordel,

Before Loading make sure there should not be any bad data in the file and column header and rows should be proper.

Data Types: Ensure that the data types in your sheet are consistent. if there is any mixed datatypes then you have go with manual schema selection.

Empty Schema or No Data: If you're not seeing data or the schema is not auto-detected, here are some things to check:

• Empty Rows: Make sure your sheet doesn't have blank rows in between the data.
• Mixed Data Types: Google Sheets may sometimes not detect the correct schema if a column has mixed data types. For example, if a column has both numbers and text, BigQuery may not auto-detect it correctly.
• Headers Format: Ensure there are no special characters or formulas in the header row that could confuse BigQuery’s schema detection.