Search API Connector Documentation
Import BigQuery Data to Google Sheets
premium
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
- 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).
- Click the project dropdown menu
- Click New Project
- Give it a name and click Create
Part 2: Set up Consent Screen
- Still in your project, navigate to APIs & Services > OAuth Consent screen
- Choose User Type = Internal and click Create
- Fill out the OAuth Consent Screen with an app name and your contact emails. You can leave the rest of it blank.
- On the next screen, click Add or Remove Scopes
- Choose the /auth/bigquery scope. Scroll down and click Update
- You’ll now see your scope listed under the sensitive scopes heading. Click Save and Continue.
- The next and final screen just summarizes your settings. Click Back to Dashboard.
Part 3: Get Credentials
- Still in your project, click APIs & Services > Credentials
- Now click +Create Credentials > OAuth client ID
- 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
- 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
- Open up Google Sheets and click Extensions > API Connector > Manage Connections
- Scroll to the bottom of the sidebar and click Add Custom OAuth
- Fill in the Custom OAuth modal like this:
- Name: Custom BigQuery
- Authorization Base URL: https://accounts.google.com/o/oauth2/v2/auth?scope=https://www.googleapis.com/auth/bigquery
- Token URL: https://oauth2.googleapis.com/token
- Client ID: provided by Google
- Client Secret: provided by Google
- Click Save, then click Manage Connections
- You should now see your new BigQuery connection listed. Click Connect.
- You’ll be prompted to log in and approve the connection. Click Allow.
- 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!
- Back in API Connector, click Extensions > API Connector > Open
- In the Create tab, enter the API URL we just created.
- Under OAuth, select your custom BigQuery connection from the drop-down menu.
- cUnder Headers, enter Key = Content-Type, Value = application/json
- Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see your BigQuery data populate your Google Sheet:
- 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