Automatically updating google spreadsheet with data from bigquery

0
votes

Followed the instructions here and integrated a google spreadsheet with bigQuery. My query pulls data from the past hour, so it is important to keep the spreadsheet updated.

Is there a way for the sheet to automatically run the script and update the data on refreshing the page? Right now, I need to perform a few clicks to do it.

2
google-apps-scriptgoogle-sheetsgoogle-bigquerygoogle-spreadsheet-api
Refreshing the page triggers the "onOpen()" simple trigger, or an installed "on open" trigger. So, you need an "On Open" trigger, either the simple or installed. Apps Script Documentation - Triggers - Alan Wells
Thx. I tried that, but that gives an authentication error if I refresh the page. But if I follow the manual steps, the data get refreshed. - arun

2 Answers

1
votes

Addition to @Sandy Good's answer.

You need to use onOpen(e), it runs when a user opens a spreadsheet, document or form that has permission to edit. 

function onOpen() {
// Add a custom menu to the spreadsheet.
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.createMenu('Custom Menu')
.addItem('First item', 'menuItem1')
.addToUi();
}

You received authorization error since script needs authorization. Apps Script requires user authorization to access data.

If a script uses services that can access private data, you'll see one of the authorization dialog.

You may revoke scripts access by following these steps:

  • Visit the permissions page for your Google account. (To navigate to this page in the future, visit Google.com, then click your account picture in the top-right corner of the screen. Next, click Account, then Security, then View all in the account permissions section.)

  • Click the name of the script whose authorization you want to revoke, then click Revoke access on the right.

For more information regarding authorization, please follow the Official Google Documentation: https://developers.google.com/apps-script/add-ons/lifecycle

0
votes

Since BigQuery has already been integrated to Google Spreadsheet, here's what you have to do with your Script:

1. Send HTTP request using Datasets: update with added path parameters datasetId and projectId.

Sample HTTP request:

PUT https://www.googleapis.com/bigquery/v2/projects/projectId/datasets/datasetId

However, please note of the following when using Datasets: update:

  • It requires authorization as stated in Authenticating requests to the Google BigQuery API
  • The specified access list completely overwrites the existing access list. If you specify an empty access list, you will revoke access to everyone except yourself
  • This request requires authorization with at least one of the following scopes:

Scope

https://www.googleapis.com/auth/bigquery

https://www.googleapis.com/auth/cloud-platform

2. To update your data automatically, you can set up a Trigger function from inside your Script which executes based on your preferences with these reserved function names:

  • onOpen(e) runs when a user opens a spreadsheet, document, or form that he or she has permission to edit.
  • onEdit(e) runs when a user changes a value in a spreadsheet.
  • onInstall(e) runs when a user installs an add-on.
  • doGet(e) runs when a user visits a web app or a program sends an HTTP GET request to a web app.
  • doPost(e) runs when a program sends an HTTP POST request to a web app.

Again, you should also note that because simple triggers fire automatically, they are subject to several restrictions which you can go through in the documentation.