So what’s the problem? You may have been playing with Google BigQuery and you may have noticed that you can use an already existing Google Sheet as data source for your BigQuery tables:
There are multiple problems with this “native connector”:
- If your Google Sheet has multiple sheets, only one sheet can be linked to Big Query.
- The linked sheet is ALWAYS the FIRST sheet (gid=0 in the URL). You cannot change which sheet is being linked to BigQuery.
- It’s quite slow to setup when you have many files to “link/upload”.
This sucks, right? What if you want to link=upload multiple sheets from one Google Sheet to BigQuery? Install our FREE add-on. Read more below.
All the testing was done on documents with locale set to United States. We strongly recommend you to set your documents to United States as well in order to make sure numbers get detected as numbers during the upload to BigQuery. Format detection in BigQuery is quite sensitive to formatting and it’s something we cannot really affect inside our uploader.
We decided to create a free add-on for Google Sheets which solves all the problems:
- You can upload multiple sheet into BQ from one Google Sheet
- You can choose whichever sheets you want to upload
- It’s quite fast to configure upload of multiple sheets
On the other hand, since we perform “uploads”, there is not live link between Big Query and your Google Sheet. Every time your Google Sheet changes, you need to upload again. This can be advantage for some people, but can be disadvantage for the others.
Expected Format of Your Tables
Assume you want to upload a table (or multiple tables) sitting in your Google Sheet similar to this format:
There are few important things to remember:
- The first row of your table in Google Sheets is always used as header for your BigQuery table.
- If you are using special characters in the column names, they will likely be removed during upload.
- Please make sure that you don’t have duplicate column names, it will cause unexpected issues (entire columns being overwritten).
- Please make sure that your columns always have a name in the first row otherwise the upload job will fail.
The Setup Process
The setup process is quite simple so lets get to it.
1. Get Our Add-on from Chrome Web Store
Press the blue “FREE” button:
Few moments later, you should see Google Sheets => BigQuery Uploader under “Add-ons” menu across all your Google Sheets files:
2. Setup BqUploadConfigSheet
After you “install” our add-on, it will start appearing in the Add-ons menu across all Google Sheets files linked with your account. When you open your Google Sheets file from which you want to upload some sheets to BigQuery, go to Add-ons > BigQuery Uploader for Sheets > Create Config Sheet:
After few moments, a new sheet called BqUploadConfigSheet will be created in your Google Sheet document. It will look like this:
(rows 2 and 3 are sample values filled by me, you will see only the header row)
Now it’s time to fill out the all the columns. 1 row filled out = 1 sheet to upload. Here is a sample file with 2 sheets being uploaded to BigQuery.
Columns which need to be filled in:
Sheet Name – sheet which you want to upload to BigQuery
BQ Project – It’s project ID from your BigQuery. Just select your project ID from the dropdown menu. If you just created a new project and you don’t see it in the dropdown menu, press “Refresh” button:
BQ Dataset – just type your dataset ID. It’s this string:
Alternatively, you can navigate to your dataset in Big Query web UI, have a look at the URL: https://console.cloud.google.com/bigquery?project=mythical-reports&p=mythical-reports&d=biq_query_upload_sample_file&page=dataset. “biq_query_upload_sample_file” from d=biq_query_upload_sample_file part is your dataset name.
BQ Table – just the name of the table to which you want to upload your data. Read “Truncate Existing Tables?” and “Max Bad Records” to see what happens during various upload scenarios.
Truncate Existing Tables? – this dropdown lets you choose whether you want to delete existing table and upload the sheet as new table (TRUE) OR if you just append the data from your sheet to existing table in BigQuery (FALSE).
If you say FALSE in Truncate column and your table in Google Sheets has different column set than the table already sitting in BigQuery, then the upload will fail (it’s limitation in Google’s side, we cannot really do anything about it).
Max Bad Records – this option allows you to define number of bad values within the table. If the real number of bad values is higher than what you type here, the upload will fail. Again, it’s something from Google’s world. You can read more about bad records here. Basically, if your table is nice and clean, i. e. you only have numbers in column A, text strings in column B, dates in column C, you don’t have fill out this column. If you have 10,000 dates and 10 strings in column A, then it’s good idea to enter Max Bad Records number – we would go for the maximum allowed which is 10,000,
Upload All Columns As STRING? – If you say TRUE, then all columns from your sheet will be uploaded as STRING. This is handy in cases where your data is total mess and Max Bad Records number set to 10000 is not helping. No matter if you are mixing dates, strings, floats, integers in one column, the upload will go through OK if your set this option to TRUE.
Data type detection note
Unless you say TRUE in Upload All Columns As STRING?, we use auto detection of data types provided by Google. So if Google sees 100 date values out of 100 value in a column, it will set this column to “DATE” and so on for integers, floats or strings in different columns. If Google sees mixed data types in one column, Google will start guessing data type for these mixed columns and this guessing can produce unexpected upload failures.
3. Trigger Upload to BigQuery
After your config sheet is ready to go, you just press the “Upload Sheets to BigQuery!”:
4. Check Upload Status on BqLogSheet
New sheet called BqLogSheet will be created after you run your first upload. It will look like this:
If everything goes OK, you will see “success” in the message.
If things don’t go so well, you will see an error message. Some of them are quite self explanatory, some of them are not. If you are not sure what to do, please ask in this Facebook group.
- Experience an unexpected behavior, odd failures
- If you would like to submit a feature request
- If you want to just say thank you
Do not hesitate to contact us in this Facebook group.
What to Expect in Future?
- Option to schedule uploads