Are you looking to connect Google BigQuery to Google Sheets? Look no further! In this comprehensive guide, we will show you how to establish this connection in just a few simple steps.
Google BigQuery is a “serverless” cloud data warehouse that automatically scales computing and storage resources to handle petabytes of data. When combined with Google Sheets and Looker, the business intelligence platform acquired by Google in 2020, BigQuery becomes one of Google’s leading data analysis solutions.
Two Best Methods to Connect BigQuery to Google Sheets
1. Connected Sheets
Google has recently introduced a feature called “Connected Sheets,” which unifies their various data analysis solutions. It is a built-in connector specifically designed for BigQuery within Google Sheets.
To set up this connection, make sure you have the necessary configuration for your Google Cloud account:
- You need a Google Workspace Enterprise account to use Connected Sheets with BigQuery.
- Create a new Google Cloud project by accessing the Google Cloud Console and the Project Selection Page.
- Verify that billing is enabled for your Google Cloud project.
Once the configuration is complete, open a Google Sheets spreadsheet. Click on “Data” in the top menu, followed by “Data Connectors,” and finally, “Connect to BigQuery.”
Next, select the new Google Cloud project.
For this example, we will import a public dataset from BigQuery into Google Sheets. Select “Public Datasets.”
Type “Chicago” in the search box and choose the “chicago_taxi_trips” dataset. Then, select the “taxi_trips” table and click “Connect.”
The BigQuery data will now appear in your spreadsheet. In this particular example, the imported dataset contains 202 million rows.
You can import more data from Google BigQuery into Sheets using the extraction feature. This allows you to work on a subset of your data in a separate spreadsheet.
Click the “Extract” button at the top of the table. Select the “New Sheet” option and click “Create.”
Choose the filters, sorting order, columns, and the number of rows you want to import in the extraction editor.
In the example below, we will import 25,000 rows with columns for fares, tips, tolls, and trip start dates sorted in descending order.
Click “Apply” to import the BigQuery data into Google Sheets.
Although the built-in BigQuery connector in Google Sheets is perfect for projects with large amounts of data, experienced users may find that the Coefficient tool offers greater ease of use, flexibility, and superior operational features for their business use cases.
2. API Connector by Mixed Analytics
Technical users can utilize the API Connector developed by Mixed Analytics to connect BigQuery to Google Sheets. This add-on allows for manual integration of any API with Google Sheets.
Start by installing the API Connector from the Google Workspace Marketplace.
Next, create a new project on the Google Cloud platform. Try to use the same Google account you use for BigQuery.
Configure the consent screen by accessing “APIs & Services” and then “OAuth consent screen.” Select “Internal” as the user type and click “Create.”
Provide the application name, user support email, and developer email in the OAuth consent screen, leaving the other fields empty.
Click on “ADD OR REMOVE SCOPES” on the next screen and select the /auth/bigquery scope.
Scroll down and click “Save.” Your scope should now be listed under “Your sensitive scopes.”
Click “SAVE AND CONTINUE.”
The final screen displays a summary of your settings. Click “Return to the dashboard” to proceed to the next step.
In the same project, click on “APIs & Services,” then “Credentials.” Next, click “+ Create credentials” and select “OAuth client ID.”
Fill in the required fields, as shown in the screenshot below.
Click “Create” when you are done. Take note of your client ID and client secret, as you will need them for the next step.
Access “Extensions” in the top menu of Google Sheets. Then, click on “API Connector” and “Manage Connections.”
Scroll down the API Connector sidebar and select “Add Custom OAuth.” Provide the required information as shown below.
Click “Save,” then click “Manage Connections.” Click “Connect,” followed by “Authorize” in the popup window to connect and authorize the connection.
Now it’s time to create the URL for your API request. For example, you can start by listing the contents of a table in rows. The complete API URL would look like this:
Follow this format and use your own dataset, project, and table identifiers. You can now extract data from the BigQuery API into Google Sheets.
Go back to API Connector and launch the extension. Enter the API URL you created in the previous step. Choose your custom BigQuery connection from the OAuth dropdown.
Enter “Key = Content-Type, Value = application/json” under “Headers.”
Next, create a new tab. Click “Set as current” to use this specific tab as the destination for your data. Name your query, then click “Run.” You will see the BigQuery data displayed in Google Sheets.
Unlock a New Dimension of Analysis: Connect Google BigQuery to Google Sheets
Connecting BigQuery to Sheets is possible through various methods, ranging from one-click applications to code-based tools. While Connected Sheets by Google is a solid choice for use cases with large amounts of data, experienced users may prefer the Coefficient tool for its simplicity, flexibility, and advanced operational features for their business use cases.
To learn more about best practices in data analysis and BigQuery integration, visit our website Crawlan.com for additional resources.
