Search API Connector Documentation

Print

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

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 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
      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. 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:
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 Add-ons > API Connector > Open.
  2. In the Create tab, enter the API URL we just created.
    bigquery-img19
  3. Under Headers, enter Key = Content-Type, Value = application/json
    bigquery-img19b
  4. Under OAuth2 Authentication, select your custom BigQuery connection from the drop-down menu.
    bigquery-img20
  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. 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.

Previous Import Google Analytics Management Data to Google Sheets
Next Import Google Calendar Data to Google Sheets

Leave a Comment

Table of Contents