Import Google BigQuery Data to Google Sheets

In this guide, we’ll connect Google BigQuery to Google Sheets, using the API Connector add-on for Sheets. This guide uses API Connector’s custom OAuth manager, which is a paid feature; please upgrade to access or install API Connector for a free trial.

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

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 Add-ons > 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. Close and re-open the sidebar to 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 Add-ons > API Connector > Open.
2. In the Create tab, enter the API URL we just created.
   
3. Under Headers, enter Key = Content-Type, Value = application/json
   
4. Under OAuth2 Authentication, select your custom BigQuery connection from the drop-down menu.
   
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. Therefore you may want to rename them with a bit of JMESPath. The specific JMESPath expression depends on the structure of your data, so feel free to leave a comment or send a message if you’d like some help.