I have a Google Apps script which opens and processes an existing spreadsheet and writes data to JSON. This works fine for me from the Google Script editor but I can't get it to open the spreadsheet when I deploy it as a web app.
To illustrate I've created this simple test version:
- A Google spreadsheet which "Anyone on the internet can find and edit" (though I'll need tighter control on the real spreadsheet).
- A Google script (see below) with doGet to deploy the HTML file.
- An HTML file (see below) which contains the form and the Javascript to open the spreadsheet.
The script and HTML file are saved in one Google App Script Project.
I "Publish > Deploy as a web app" with "Execute the app as: Me," and give it the necessary permissions.
When I "Test web app for your latest code" I get the HTML form displayed. When I click "OK" on the form I see the first alert "before opening spreadsheet."
But I never see the second alert "after opening spreadsheet." On attempting to open the spreadsheet the page redirects to a blank userCodeAppPanel URL and goes no further.
I'm guessing it's to do with authentication??? Any clues much appreciated!
doGet Google app script:
function doGet() {
return HtmlService.createHtmlOutputFromFile("opensheet.html")
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
HTML form:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function getData() {
var sheetFileId = "1feVgACmd5_g9II2ll_1u7AVt8jIVg2LbKp13k8UQw6w";
alert("before opening spreadsheet");
var sheetFile = SpreadsheetApp.openById(sheetFileId);
alert("after opening spreadsheet");
}
</script>
</head>
<body>
<p>Update web app from spreadsheet?</p>
<form onsubmit='getData()'>
<input type='submit' value='Go'>
</form>
</body>
</html>