Download Our FREE Google BigQuery Uploader for Google Sheets Add-On!

The Problem

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”:

  1. If your Google Sheet has multiple sheets, only one sheet can be linked to Big Query.
  2. The linked sheet is ALWAYS the FIRST sheet (gid=0 in the URL). You cannot change which sheet is being linked to BigQuery.
  3. 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.

***NOTE***

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.

The Solution

We decided to create a free add-on for Google Sheets which solves all the problems:

  1. You can upload multiple sheet into BQ from one Google Sheet
  2. You can choose whichever sheets you want to upload
  3. 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:

  1. The first row of your table in Google Sheets is always used as header for your BigQuery table.
  2. If you are using special characters in the column names, they will likely be removed during upload.
  3. Please make sure that you don’t have duplicate column names, it will cause unexpected issues (entire columns being overwritten).
  4. 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

Visit this link: https://chrome.google.com/webstore/detail/google-sheets->-bigquery/olgojkadmldkofmeekmkdcfeofjdgpej?hl=en-US

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.

Feedback Needed!

If you:

  1. Experience an unexpected behavior, odd failures
  2. If you would like to submit a feature request
  3. If you want to just say thank you

Do not hesitate to contact us in this Facebook group.

What to Expect in Future?

  1. Option to schedule uploads
  2. ???
  3. ???
  4. ???

5 thoughts on “Download Our FREE Google BigQuery Uploader for Google Sheets Add-On!

  1. Bryan Bourne Reply

    Thank you! This Add-on worked perfectly.

    I would love the ability to schedule uploads to BigQuery. Any update on this?

    • admin Post authorReply

      Hi Bryan,
      we are looking into the scheduler but no ETA for the release yet.

      DZ

  2. Alex Reply

    Hi,

    First of all thank you for this plugin, it’s really helpful!

    I’ve followed your step by step guide, the tables are created when I upload it but no data is uploaded and the logs message is “Success” for both.

    Is there anything you’d know that could cause this issue?

    Cheers,

    Alex

    • admin Post authorReply

      Hi Alex,
      the problem is related to date formating in your G Sheet. You should be using YYYY-MM-DD, currently you are using DD/MM/YYYY and BQ guessing engine does not like it so the table gets created (=success) but not rows are being uploaded at the end. Not sure why BQ API returns “success” in this scenario, we’ll look into it.

  3. Steffen Lerbs Reply

    Hello,
    i have successfully uploaded data from Google Sheets to BQ. However, the name of the created table is in small letters even-though i spelled it all in CAPITAL letters. is there a way to have it all in CAPITAL letters too?

    thanks
    Steffen

Leave a Reply

Your email address will not be published. Required fields are marked *