Integrate Google Spreadsheet with Big Query by Apps Script
Google Data Studio is a good option to visualise the Big Query data. But Google SpreadSheet sometimes works better especially when you handle a lot of data columns.
You may want to share one Google spreadsheet with colleagues with the designed formulas and keep updating it with the latest data on Big Query.
Here is how to integrate the BigQuery into Google Sheet by using the Google Apps Script.
Save the complex query as a view on Big Query
An analytic SQL query could be quite long if you join and process multiple tables, and it is better to manage it on BigQuery instead of embedding the SQL in the Apps Script. You can save your query as a “View” on Big Query’s query editor.
For example, if you save the complex query as “table_name,” it can be simply queried as:
SELECT * FROM project_name.dataset_name.table_name;
Overwrite the data rows on the Google sheet
When dealing Big Query data on Google Sheet, you need to use multiple sheets (tabs) on one spreadsheet, one for the data source and others for the presentation.
For example, if you have the “latest” sheet as the data source, you could QUERY the data from the other sheets to present the data.
Then you can overwrite the whole data in the datasheet pulling from Big Query. To trigger the query, we are going to add a custom menu to the spreadsheet.
Google Apps Script to Integrate Big Query into Google Sheet
Here is the Google Apps Script to add your custom menu to the Google Sheet. To add this script to your google sheet:
- Select “Tools” > “Script Editors” on the spreadsheet menu.
- Paste the following code to the script editor.
- Change YOUR_PROJECT_ID, YOUR_DATA_SOURCE_SHEET_NAME and YOUR_PROJECT_NAME.DATASET_NAME.TABLE_NAME in the script.
Then edit a manifest.js by
- On the Script Editor, “View” > “Show manifest file”
- Edit and save as follows:
Once done, save the script and reload the spreadsheet, then you will see the “BigQuery” menu in the navigation.
When you select the “Update data”, the Google sheet will fetch the query to BigQuery and replace the data source sheet with the latest data from Big Query.
When you first select the menu, it will request the permissions on this sheet and it may also show “Access Not Configured. BigQuery API has not been used in project XXXX” error. In that case, click “Details” in the error message, and follow the link in the error message to enable the Big Query for the Apps Script. The user also needs to have the “BigQuery Data Viewer” and “BigQuery Job User” permissions which you can add at Google Cloud “IAM and admin” screen.