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>
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') ?>
<!-- Logic for charts tables -->
<?!= 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
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();
}