Sending Queries from Google Apps Script

EGJ Moorington

unread,

Sep 16, 2024, 12:52:26 PM 9/16/24

to Google Visualization API

Hello!

I'm developing a web app with Google Apps Script, and I want to send a Query using google.visualization.Query to create a google.visualization.DataTable automatically from data fetched from a Google SpreadSheets file.

However, upon sending the query, I'm getting the following errors in the console:

- Access to XMLHttpRequest at ' https://docs.google.com/spreadsheets/d/ spreadsheetID ?sheet= sheetName &tq=select%20 column &tqx=reqId%3A0' from origin 'https:// ... script.googleusercontent.com ' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

- GET https://docs.google.com/spreadsheets/d/ ... net::ERR_FAILED 302 (Found)

- Uncaught Error: Error handling Query: XhrHttpError: Request Failed, status=0, url= https://docs.google.com/spreadsheets/d/ ...

I've noticed that 'Access-Control-Allow-Origin' does not figure in the "Restrictions in IFRAME mode" list, but on the page talking about data queries, it is mentioned that when sending queries from within Apps Script, IFRAME mode should be used, which I am using.

Any help would be greatly appreciated, thanks!

Here's a list of some of the documentation I've been following:
Preparing data for Charts .
Data Queries .
Restrictions in IFRAME mode .

Here's the relevant code:
js/tables.html:

< script >

const url = " https://docs.google.com/spreadsheets/d/ spreadsheetID "

function drawTable () {

const dataTable = new google . visualization . DataTable ()

sendQuery ( " sheetName " )

}

function sendQuery ( sheet ) {

var u = ` ${ url } ?sheet= ${ sheet } `

console . log ( u )

var query = new google . visualization . Query ( ` ${ url } ?sheet= ${ sheet } ` )

query . setQuery ( "select column " )

query . send ( handleQueryResponse )

}

function handleQueryResponse ( response ) {

console . log ( response . getMessage ())

console . log ( response . getDetailedMessage ())

if ( response . isError ()) {

alert ( 'Error in query: ' + response . getMessage () + ' ' + response . getDetailedMessage ());

return

}

console . log ( response . toString ())

}

</ script >

page.html:

<!DOCTYPE html >

< html >

< head >

< base target = "_top" >

< meta name = "viewport" content = "width=device-width, initial-scale=1" >

< ?!= include('css/style') ?>

< script type = "text/javascript" src = " https://www.gstatic.com/charts/loader.js " ></ script >

< ?!= include('js/tables') ?>

< script type = "text/javascript" >

google . charts . load ( 'current' , { 'packages' :[ 'table' ]});

google . charts . setOnLoadCallback ( drawTable );

</ script >

</ head >

< body >

< div id = "table_div" ></ div >

</ body > </ html >

code.gs :

// Variables

var page = "index"

var output = HtmlService . createHtmlOutput ( '<p></p>' )

function doGet ( e ) {

// Get information from the url

const p = e . parameter . page

page = p

return HtmlService . createTemplateFromFile ( e . parameter [ 'page' ])

. evaluate ()

. setSandboxMode ( HtmlService . SandboxMode . IFRAME )

}

// Get the URL for the Google Apps Script running as a WebApp

function getScriptUrl () {

var url = ScriptApp . getService (). getUrl ()

return url

}

function include ( filename ) {

return HtmlService . createHtmlOutputFromFile ( filename ). getContent ();

}

Ray Thomas

unread,

Sep 16, 2024, 10:23:05 PM 9/16/24

to Google Visualization API

Are you testing this locally or from a web server? If you're testing this locally unless something from the likes of https://medium.com/@dtkatz/3-ways-to-fix-the-cors-error-and-how-access-control-allow-origin-works-d97d55946d9 works for you, it is unlikely the charts will display. They should be fine if the app is on an actual server.

I've got about 30 charts with the data coming from Google Sheets and I can't see a single one of them while I'm writing them because of CORS. They all display properly once they are are uploaded to the web server.

The spreadsheet has to be set to "Anyone on the internet with this link can view" or you can use OAuth . The Charts API documentation has it's own little section about using Google Sheets .

EGJ Moorington

unread,

Sep 17, 2024, 12:44:25 AM 9/17/24

to Google Visualization API

My spreadsheet is stored in drive, set to "Anyone on the internet with this link can view". The Google Apps Script project is also in drive, and I was doing the testing on the "Test deployment" Web App. I will try testing on a "Deployment version" later, but based on what you're saying, shouldn't it be working?

EGJ Moorington

unread,

Sep 17, 2024, 1:31:48 PM 9/17/24

to Google Visualization API

Deploying as a "New deployment" instead of testing on the test deployment didn't solve the issue either. Might be worth noting that my Google Apps Script doesn't have the Spreadsheet as a container.

EGJ Moorington

unread,

Sep 21, 2024, 5:39:31 PM 9/21/24

to Google Visualization API

Solved the problem;

My issue was not adding "/gviz/tq" after the spreadsheet ID and before the parameters.

This is how the URL should look like. You can add parameters to it afterwards.
" https://docs.google.com/spreadsheets/d/ spreadsheetID /gviz/tq "

Reply all

Reply to author

Forward