How to get data from BigQuery to Google Sheets automatically
What is the BigQuery Data Connector for Google Sheets?
BigQuery connectors allow users to integrate BigQuery with other platforms—either data sources or analytics tools. Google recently launched a BigQuery connector for Google Sheets which allows greater accessibility to your data. Large datasets can be intimidating for non-experts within your organization, and not all users need access to your full range of data. For big data practitioners, it is better to free up smaller amounts of data and distribute it to the members of an organization who need it, rather than requiring those who are less tech-savvy to query the system themselves. Google Sheets is intuitive and familiar for any users who are used to working with spreadsheets. Learn more about the BiqQuery Data Connector in our first post in this series.
How to connect BigQuery to Google Sheets?
1. Use Sheetgo’s Google Apps Script
Here at Sheetgo, we are all about automation and we love providing you with solutions—so we created a tool ourselves to automatically update the BigQuery data in your Sheets! We made a script for you to install in your Google Sheets so that you can get data from BigQuery to Google Sheets automatically! Read on for a tutorial on how to quickly set up the script so you can get started on more important tasks!
Step 1: Make a copy of the script
Open the template by clicking here. Click on Make a Copy to get your own copy of this script, it will automatically save to your Google Drive.
Step 2: Set up the script
Your copy of the script will open automatically in a new tab. Click Tools → Script Editor. A new tab will open in your browser. You then need to complete two steps: enable the BigQuery API (2.1) and get the Project ID (2.2).
Step 2.1: Enable the BigQuery API
To enable the Big Query API, select Resources → Advanced Google Services. A new window will pop up, make sure the Big Query API is turned On.
Step 2.2: Get the Project ID
To get the Project ID, follow these steps. The screenshots below show detailed steps.
- Click the hyperlink Google Cloud Platform API Dashboard.
- Click the ‘ENABLE APIS AND SERVICES’ button.
- Type BigQuery into the search bar and click BigQuery API.
- Click Enable
- In the browser URL copy the text project-id-##################
- Go back to the BigQuery Queries tab in your browser and paste that text into the script code after Project ID. (See screenshot below)
- You can close the tab
Step 3: Automate the data transfer
To automate, inside the script, click on the clock icon.
Then, click on > Add Trigger.
In the following screen, you can set it up the way you prefer.
Step 4: Run the script
Go back to your spreadsheet. Click Sheetgo → Run Query → Continue → Login.
That’s it! The script has been created! Now you can automatically import data from BigQuery into your Google Sheets spreadsheets!
2. Follow Google’s approach
First, record a macro to refresh your data. Open the spreadsheet that contains the data connected to the BigQuery. Then record a macro from a different tab than the one you want to refresh. After starting to record, switch to the tab you want to refresh. At the bottom left, click Refresh and after that, click Save.
Second, schedule your macro. Go to the Menu Tools > Script Editor. Inside the Script Editor, go to Edit > Current project’s triggers. On the bottom right, click on Add triggers and you can select your desired options. To finish, click on Save. That’s it, you are all set! Make sure to learn more about Google´s BigQuery in the following blog posts:
BigQuery Data Connector for Google Sheets: How to Get More Than 10,000 Rows