Data Not Loading Correctly with BigQuery Connected to Google Sheet

[jpjaymetdcx]

My data source is a Google Sheet loaded into a BigQuery table. However, there are multiple columns that don't have the expected value.

Expected data in Google Sheet

Actual output :

I've also tried to do this via gspread library, and when I check the worksheet it is still not correctly valued.

How do I proceed?

 

[lsolatorio]

Hi @jpjaymetdcx, 

There might be a mismatch with the formatting or data type configuration prior to table creation hence the inaccurate data output.

Here's a high-level table creation guide that may help you

1. Select your dataset in the explorer panel
2. Click on the [+] CREATE TABLE button in the details panel
3. In the Source section, choose the following
   * Create Table From: Drive
   * Select Drive URI: Link address/ URL of your Google Sheet file from Drive
   * File Format: Google Sheet
   * Sheet Range: Specify your column/s here (ex: 'Sheet1'!A2:C)
4. Set your destination Project, Dataset and Table
5. Choose your Schema definition
   * This is where you can configure your fields and data types
6. Create your table

You can then write your queries and save your data in multiple formats like CSV or Google Sheet.

I hope this works for you as well.


Usable resource:
- Create external tables

[Roderick]

This is a really helpful breakdown, thanks @lsolatorio! Please let us know if this works out for you @jpjaymetdcx.

[jpjaymetdcx]

Hi @lsolatorio &  @Roderick ,I have created another table with these steps, and I am still getting the same issues. See screenshot below.

Apparently these columns' values came from an array formula. Is this a known limitation? How do I handle these columns, then?

[lsolatorio]

Hello @jpjaymetdcx, 

I tried applying simple and array formulas to my dummy data in Google Sheets then imported them in BigQuery the same procedure as mentioned in my previous response. Data import was successful, I was able to query and save/ export data without any issue.

ss_1_source_data

ss_2_imported_query_result

ss_3_extracted_data

I suspect that the problem is coming from your Google Sheet source, you may have a formula that needs additional permission to load the data and/ or will have multi-dimensional result such as IMPORTRANGE or QUERY which is why the data shows "Loading.." because BigQuery has no access to it hence can't process your data properly.

Hope this helps.