Authorizing Google Charts to access private spreadsheets

Question

I am trying to create a Web App using Google Apps Script to query data from my Google Sheet using Google Charts.

I have been able to successfully query the spreadsheet when the Google Sheet is publicly shared, however since the spreadsheet contains confidential info I would rather do it privately using authorizations.

The reason why I want to use Google Charts visualisation functions (as opposed to the server side Spreadsheet App) is because of the speed of querying large data sets.

I have tried following the steps in the above documentation. That is, creating a client id then using the gapi.auth library to authenticate myself but I continue to receive an error.

When i add the authorization library and first part of the code from the documentation (with console.log simply to see where it get's up to):

<script src="https://apis.google.com/js/auth.js?onload=init"></script>

<script> 
var clientId = '1234.apps.googleusercontent.com'; 
var scopes = 'https://spreadsheets.google.com/feeds';       

function init() { 
  
console.log("here");  
  
gapi.auth.authorize({client_id: clientId, scope: scopes, immediate: true},handleAuthResult);

}

</script>

I receive the following error:

1289869776-mae_html_user_bin_i18n_mae_html_user.js:41 dropping postMessage.. was from unexpected window

Any guidance is appreciated.

Hi Ruben, the web app will have a 'timeline' chart with filters to dynamically change the timeline, so my ultimate goal was to be able to extract the data as quickly as possible using query language. Google Charts visualisation permits this from the client side. Using server side code requires me to obtain the data range (SpreadsheetApp) then loop though the range values to obtain the exact data I need. — beano
Plus, the data contains a large amount of dates, so transferring it from server to client requires me to loop through the data again to reconstruct the dates. Does that make sense or am I overcomplicating things? — beano
Getting the whole data set is not the issue. The issue is the time it takes to get a subset of the data ( based on what filters have been input by the user in the web app). Query language allows me to very quickly get this subset of data, however the SpreadsheetApp doesn't have this ability so I am left looping through endless amounts of data to construct a data table. — beano
Unless there is a way to query data from the server side that I am not aware of. I'll keep searching. — beano

Rubén Rubén · Accepted Answer · 2018-01-08T14:17:33

Since you are creating your web app using Google Apps Script, it's not necessary to "authorize Google Charts to access private charts" because you could use Google Apps Script services and methods to get the data from the spreadsheets and pass them to Google Charts.

On Converting from UiApp + Chart Service to Html Service + Google Visualization API it's shown how to convert the old dashboard example from from UiApp + Chart Service to HtmlService + Google Visualization API. This illustrates how to create a Google Apps Script web app that builds a chart from Google Spreadsheet data without "requiring authorization" as it's "implicitly" handled by Google Apps Script.

From the above link

Two functions cooperate to retrieve the dashboard’s data and display it. As soon as the visualization API is loaded, the sendQuery() function is invoked. Using the google.script.run facility, it sends its request to the server-side getSpreadsheetData() function. This is an asynchronous operation, so two callbacks are provided, a successHandler and a failureHandler. One or the other will receive the result of the server call, depending on the outcome.

H/T to jfllmartin, author of an answer to Converting my google dashboard app from the UI service to the HTML service where the above link was shared.

Authorizing Google Charts to access private spreadsheets

2 Answers