Import BigQuery Data to Google Sheets

In this guide, we’ll connect Google BigQuery to Google Sheets, using the API Connector add-on for Sheets.

Contents

• Before You Begin
• Part 1: Set up Project
• Part 2: Set up Consent Screen
• Part 3: Get Credentials
• Part 4: Connect BigQuery to API Connector
• Part 5: Create your BigQuery API Request URL
• Part 6: Pull BigQuery API Data to Sheets
• Part 7: API Documentation

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Set up Project

1. Navigate to https://console.cloud.google.com/. To make things easier, set this up using the same Google account for which you’ll be fetching BigQuery data (we’re creating an internal app that can only be used by yourself).

1. Click the project dropdown menu
   
2. Click New Project
   
3. Give it a name and click Create
   

Part 2: Set up Consent Screen

1. Still in your project, navigate to APIs & Services > OAuth Consent screen
   
2. Choose User Type = Internal and click Create
   
3. Fill out the OAuth Consent Screen with an app name and your contact emails. You can leave the rest of it blank.
   
4. On the next screen, click Add or Remove Scopes
   
5. Choose the /auth/bigquery scope. Scroll down and click Update
   
6. You’ll now see your scope listed under the sensitive scopes heading. Click Save and Continue.
   
7. The next and final screen just summarizes your settings. Click Back to Dashboard.

Part 3: Get Credentials

1. Still in your project, click APIs & Services > Credentials
   
2. Now click +Create Credentials > OAuth client ID
3. Fill in the form with the following settings:
   • Application type: Web application
   • Name: BigQuery API Connector
   • Authorized Redirect URL: https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback
     
4. We’re done, so click Create. You’ll be presented with your Client ID and Client Secret. Keep these handy as we’ll need them in a moment.
   
   

Part 4: Connect BigQuery to API Connector

1. Open up Google Sheets and click Extensions > API Connector > Manage Connections
2. Scroll to the bottom of the sidebar and click Add Custom OAuth
3. Fill in the Custom OAuth modal like this:
   1. Name: Custom BigQuery
   2. Authorization Base URL: https://accounts.google.com/o/oauth2/v2/auth?scope=https://www.googleapis.com/auth/bigquery
   3. Token URL: https://oauth2.googleapis.com/token
   4. Client ID: provided by Google
   5. Client Secret: provided by Google
      
4. Click Save, then click Manage Connections
5. You should now see your new BigQuery connection listed. Click Connect.
   
6. You’ll be prompted to log in and approve the connection. Click Allow.
   
7. You can verify that your connection is now active on the Manage Connections screen.

Part 5: Create Your API Request URL

You can see all the available endpoints in BigQuery’s documentation, but for this example we’ll start with listing the content of a table in rows as shown here:

So the full API URL would look something like this. Substitute in your own project, dataset, and table IDs:

 https://bigquery.googleapis.com/bigquery/v2/projects/project-id-12345/datasets/XYZ_Company/tables/script_googleapis_com_console_logs_20200701/data

Part 6: Pull BigQuery API Data into Sheets

We’re finally here….Let’s get some data!

1. Back in API Connector, click Extensions > API Connector > Open
2. In the Create tab, enter the API URL we just created.
   
3. Under OAuth, select your custom BigQuery connection from the drop-down menu.
   
4. cUnder Headers, enter Key = Content-Type, Value = application/json
   
5. Create a new tab and click Set current to use that tab as your data destination.
6. Name your request and click Run. A moment later you’ll see your BigQuery data populate your Google Sheet:
   
7. BigQuery sends back field names separately from the values, which leads to rather meaningless column headers, so I recommend that you rename them through the visual field editor (just click Edit Fields at the bottom of your request).
   

Part 7: API Documentation

Official API documentation: https://cloud.google.com/bigquery/docs/reference/rest