Print

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.

Note: Google Sheets now has a native connector for BigQuery located at Data > Data Connectors > Connect to BigQuery, so you can just use that (info). But if you're interested in knowing more about the API, or in seeing the process of setting up a project in Google Cloud, read on!

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
  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:
    • OAuth Grant Type: Authorization Code
    • 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
      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: Pull BigQuery API Data into Sheets

We're finally here....Let's get some data! 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

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • ApplicationCustom
    • MethodGET
    • Request URLhttps://bigquery.googleapis.com/bigquery/v2/projects/your_project_id/datasets/your_dataset_id/tables/your_table_id/data
    • OAuth: Custom BigQuery
    • Headers:
      • Content-Type:application/json
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. Name your request and click Run. A moment later you’ll see your BigQuery data populate your Google Sheet:
    bigquery-img21
  5. 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 6: API Documentation

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

Leave a Comment

Jump To