How to open spreadsheet from Google app script dep

2019-09-14 16:14发布

问题:

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>

回答1:

You can open the spreadsheet in a new window with the window.open() method. The submit button needs to be changed, so that you don't get the blank screen in the web app after clicking the button.

<!DOCTYPE html>
<html>

<head>
    <base target="_top">
    <script>
        function getData() {

            alert("before opening spreadsheet");
            window.open("https://docs.google.com/spreadsheets/d/1feVgACmd5_g9II2ll_1u7AVt8jIVg2LbKp13k8UQw6w/edit#gid=0","_blank");

            alert("after opening spreadsheet");
        }

    </script>

</head>

<body>
    <p>Update web app from spreadsheet?</p>

    <form>
        <input type='button' onmouseup="getData()" value='Go'>
    </form>
</body>

</html>