Importing Google Sheets into a BigQuery Table

[DataDave]

Hi

I am importing a table into a BigQuery Dataset from a Google Sheet, using the URL from Google Drive.

Is there a way to import only certain columns from the google sheet and not all of them? There are more columns in the sheet than I need in the BigQuery Table. I need to use the google sheet as a datasource as this is being populated from Netsuite and I need to set up a Schedule within BigQuery to pull in the new data.

Any advice or guidance will be most welcomed.

Many Thanks

[lsolatorio · Accepted solution]

Hi @DataDave,

You can achieve this by setting the Sheet Range when creating a table in your dataset.

Let me guide you with a quick walk-through:

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
6. Create your table

You can now run a basic query against the imported table, external data source limitations apply.

I hope these steps helped you.

Usable resource:
- Create external tables

[catherinemiao]

will this table in Bigquery being updated when the spreadsheet is updated?

[ftufekci]

Yes, nearly realtime.

[fabst]

Hi guys

Is it possible to specify more than one range like "Sheet1!A:C;Sheet1!G:K"?

Thanx

[sarah_hussain-1]

Is it mandatory to specify the column and row range or can you just specify the sheet name?

[JulienInLA]

@lsolatorio 

When a bigQuery table uses a google sheet as source, we can't preview data as if the data were actually not stored in BQ. It's only visible through a query. Is there a way to have data actually loaded into bigQuery and keep the connection with the spreadsheet in case it is updated? I have software connecting to such a bigQuery table to access the data but it can retrieve any data. Thank you for your help! 

[annebaby99]

Hello ! I have created a table imported from the google sheets and it successfully created. However, I want to know why I don't have a preview on it?

[MichaelTomar]

Hello,

You can go to the BigQuery console, navigate to your dataset --> click on the Create table --> set Create table from to Drive --> paste the URL of your Google Sheet in the Select Drive URI field. For File Format, choose Google Sheet. In the Sheet Range field, specify the columns you want to import.

Alternatively, if you need a kind of automation you can use 3rd party tools like Skyvia. This one supports more than 200 connectors, including Netsuite, and allows to easily integrate data between BigQuery and Google Sheets, or directly between BigQuery and NetSuite with no coding. You can integrate just specific columns and even apply filtering, if needed.

I hope it helps you to solve your issue!

[Max__Makhrov]

Hi @DataDave ,

With the Cooltables add-on for Google Sheets, you can achieve this in 3 clicks:

1. Click "Link to BigQuery"
2. Click "all columns"
3. Select the columns you want

Other parameters will be auto-detected, including:

• Row data start
• Correct range reference
• Column names

To get the add-on, open the Google Sheets Extensions menu. Select "Get add-ons" and search for "Cooltables".

I hope this will be useful.