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.
- 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
- 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:
- 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 Add-ons > 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.
- 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:
PART 6: PULL BIGQUERY API DATA INTO SHEETS
We’re finally here….Let’s get some data!
- Back in API Connector, click Add-ons > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- Under Headers, enter Key = Content-Type, Value = application/json
- Under OAuth2 Authentication, select your custom BigQuery connection from the drop-down menu.
- 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. 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.