Search API Connector Documentation

Print

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

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
    bigquery-img1
  2. Click New Project
    bigquery-img2
  3. Give it a name and click Create
    bigquery-img3

Part 2: Set up Consent Screen

  1. Still in your project, navigate to APIs & Services > OAuth Consent screen
    bigquery-img4
  2. Choose User Type = Internal and click Create
    bigquery-img5
  3. Fill out the OAuth Consent Screen with an app name and your contact emails. You can leave the rest of it blank.
    bigquery-img6
  4. On the next screen, click Add or Remove Scopes
    bigquery-img7
  5. Choose the /auth/bigquery scope. Scroll down and click Update
    bigquery-img8
  6. You'll now see your scope listed under the sensitive scopes heading. Click Save and Continue.
    bigquery-img9
  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
    bigquery-img10
  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
      bigquery-img12
  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.
    bigquery-img13

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
      bigquery-img15
  4. Click Save, then click Manage Connections
  5. You should now see your new BigQuery connection listed. Click Connect.
    bigquery-img16
  6. You'll be prompted to log in and approve the connection. Click Allow.
    bigquery-img17
  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:
bigquery-img18

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.
    bigquery-img19
  3. Under OAuth, select your custom BigQuery connection from the drop-down menu.
    bigquery-img20
  4. cUnder Headers, enter Key = Content-Type, Value = application/json
    bigquery-img19b
  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:
    bigquery-img21
  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

Leave a Comment

Jump To...