Skip to content

Latest commit

 

History

History
1424 lines (1036 loc) · 67.8 KB

README.md

File metadata and controls

1424 lines (1036 loc) · 67.8 KB
Raw

Taking advantage of Web Apps with Google Apps Script

MIT License

Table of contents

Overview

This is a report to take advantage of Web Apps with Google Apps Script (GAS).

Description

There is "Web Apps" as one of the applications using Google Apps Script (GAS). I sometimes use this Web app. But I have only a little information for the specification of Web Apps. So in order to take more advantage of Web Apps, I investigated and summarized this. The aim of this report is to become one of the basic information for creating various applications using Web Apps with GAS.

Deploy Web Apps

When Web Apps is deployed, you can see the following window.

New IDE for Google Apps Script has finally been released at December 7, 2020. Ref For new IDE, please open a dialog by "Deploy" -> "New deployment". And, please select "Web app" at "Select type. By this, you can see the following dialog.

On the other hand, when you use old IDE, you can see the following dialog.

For setting, generally, the following flow is used.

  • On script editor

  • Publish -> Deploy as web app...

    • Project version:
      • "New" and input the description.
    • Pattern 1: Execute the app as: (old IDE), Execute as: (new IDE)
      • Me (old IDE and new IDE)
        • Who has access to the app:
          • Only myself (old IDE and new IDE)
          • Anyone (old IDE), Anyone with Google account (new IDE)
          • Anyone, even anonymous (old IDE), Anyone (new IDE)
    • Pattern 2: Execute the app as: (old IDE), Execute as: (new IDE)
      • User accessing the web app (old IDE and new IDE)
        • Who has access to the app: (old IDE), Who has access (new IDE)
          • Only myself (old IDE and new IDE)
          • Anyone (old IDE), Anyone with Google account (new IDE)
    • Click Deploy.

  • There are Me and User accessing the web app for "Execute the app as:" and Only myself, Anyone and Anyone, even anonymous for "Who has access to the app:" as the options. When Me and User accessing the web app are selected for "Execute the app as:", each "Who has access to the app:" has 3 and 2 options, respectively. From above flow, It is found that there are 5 situations for deploying Web Apps. For each situation, there are 2 methods of GET and POST.

  • At new IDE,

    • Execute the app as: became Execute as. And the values are Me and User accessing the web app. These are the same with the old IDE.

    • Who has access to the app: became Who has access. About the values, please check the following list.

      • When Execute as is Me, the values of Who has access are Only myself, Anyone with Google account and Anyone. In this case, the values of Only myself, Anyone with Google account and Anyone of new IDE are the same with Only myself, Anyone and Anyone, even anonymous, respectively.
      • When Execute as is User accessing the web app, the values of Who has access are Only myself and Anyone with Google account. These are the same with Only myself and Anyone of the old IDE, respectively.

In this report, I would like to introduce about the specification for Web Apps deployed by 5 situations.

Redeploying Web Apps without Changing URL of Web Apps for new IDE

At March 15, 2021, one endpoint is created for one deployment. Ref By this, when you redeploy "Web Apps", the endpoint is changed. Because the deployment ID is changed. It seems that this it the new specification. In this report, I would like to introduce the method for redeploying Web Apps without changing the URL of Web Apps for new IDE.

The detail document can be seen at https://gist.github.com/tanaikech/ebf92d8f427d02d53989d6c3464a9c43.

5 situations for Web Apps

  1. Situation 1
    • "Execute the app as:" : Me
    • "Who has access to the app:": Only myself
  2. Situation 2
    • "Execute the app as:" : Me
    • "Who has access to the app:": Anyone
  3. Situation 3
    • "Execute the app as:" : Me
    • "Who has access to the app:": Anyone, even anonymous
  4. Situation 4
    • "Execute the app as:" : User accessing the web app
    • "Who has access to the app:": Only myself
  5. Situation 5
    • "Execute the app as:" : User accessing the web app
    • "Who has access to the app:": Anyone

How to access to Web Apps

There are 5 situations for the deployed Web Apps, as mentioned above. And the methods for accessing to the deployed Web Apps are divided into 2 types. Those are the method which accesses using browser and the method which accesses using Curl, Google Apps Script and so on which don't use browser. Each method is used by the owner of Web Apps and the client users. The flow which summarized them is as follows.

1. Owner accesses to Web Apps using browser

  • For Situation 1, 4
    • Owner can access and run the script of Web Apps by the login to Google.
  • For Situation 2, 5
    • Owner can access and run the script of Web Apps by the login to Google.
  • For Situation 3
    • Owner can access and run the script of Web Apps without the login to Google.

2. Client users access to Web Apps using browser

  • For Situation 1, 4
    • Client users cannot access and run the script of Web Apps.
  • For Situation 2, 5
    • Client users can access and run the script of Web Apps by the login to Google.
  • For Situation 3
    • Client users can access and run the script of Web Apps without the login to Google.

3. Owner accesses to Web Apps using Curl, Google Apps Script and so on which don't use browser

  • For Situation 1, 4
    • Owner can access and run the script of Web Apps by using the access token.
  • For Situation 2, 5
    • Owner can access and run the script of Web Apps by using the access token.
  • For Situation 3
    • If the script of Web Apps uses some scopes, owner has to authorize the scopes by own browser.
    • Owner can access and run the script of Web Apps without the login to Google.

4. Client users access to Web Apps using Curl, Google Apps Script and so on which don't use browser

  • For Situation 1, 4
    • Client users cannot access and run the script of Web Apps.
  • For Situation 2, 5
    • At first, the project that Web Apps was deployed has to be shared with client users who use Web Apps.
      • I confirmed that from April 11, 2018, it is required to be shared the project to access to Web Apps. This might be due to the update of Google.
    • If the script of Web Apps uses some scopes, client users have to authorize the scopes by own browser.
    • Client users can access and run the script of Web Apps without the login to Google.
  • For Situation 3
    • If the script of Web Apps uses some scopes, client users have to authorize the scopes by own browser.
    • Client users can access and run the script of Web Apps without the login to Google.

Required parameters for accessing to deployed Web Apps

The simple explanations for above 5 situations were summarized as the following table. Owner and client users mean the owner who deployed Web Apps and the users who use the deployed Web Apps, respectively.

Situations Script of Web Apps Authorization for scopes Owner Client users Share project
Situation 1 Run as owner. Only owner Access with access token. Cannot access. No
Situation 2 Run as owner. Only owner Access with access token. Access with access token. Yes
Situation 3 Run as owner. Only owner Access without access token. Access without access token. No
Situation 4 Run as each user. Each user Access with access token. Cannot access. No
Situation 5 Run as each user. Each user Access with access token. Access with access token. Yes

For example, at situation 5, the script of Web Apps is run as each user (owner and client users). The authorization for the scopes of scripts of Web Apps is required to run the scripts. This authorization has to be done by each user (owner and client users) using own browser. The access token is required for each user (owner and client users) to access to Web Apps. In order to access to Web Apps from client users, the project deployed Web Apps has to be shared with the users.

Authorization for scopes

  • When Web Apps is deployed as "Execute the app as:" : Me by owner, the authorization screen is automatically opened. When the owner authorizes it, owner and client users can run the scripts of Web Apps as the owner.

    • For example, in this situation, when Session.getEffectiveUser().getEmail() is run in this situation by the client users, the retrieved email is the owner's email. From this, it is found that the script is run as the owner.

    • For example, in this situation, when DriveApp.createFile(blob) is run at the script of Web Apps, the file is created in the owner's Google Drive.

  • When Web Apps is deployed as "Execute the app as:" : User accessing the web app by owner, the authorization screen is NOT opened automatically. So before owner and client users access to Web Apps, they have to manually authorize to use the scopes by own browser.

    • If some scopes are used for the scripts of Web Apps, owner and client users have to authorize them only one time using own browser, before it accesses to Web Apps.

      • The URL for authorizing is https://script.google.com/macros/s/#####/exec which is the same to URL of Web Apps. When owner and client users access to the URL using own browser, the following authorization screen is displayed.

      • When you see above screen, please click "REVIEW PERMISSIONS". And select account and authorize to use the scopes. After owner and client users authorized the scopes, they can run the script of Web Apps.

    • For example, in this situation, when Session.getEffectiveUser().getEmail() is run in this situation by the client users, the retrieved email is the each user's email. From this, it is found that the script is run as each user.

    • For example, in this situation, when DriveApp.createFile(blob) is run at the script of Web Apps, the file is created in the each user's Google Drive.

Access token for accessing to Web Apps

  • When Web Apps is deployed as "Who has access to the app:": Only myself or "Who has access to the app:": Anyone by owner, the owner and client users have to access and run the script of Web Apps with own access token.

    • At least, one of scopes for using Drive API is required to be included in the access token. For example, those are

      • https://www.googleapis.com/auth/drive.readonly
      • https://www.googleapis.com/auth/drive.files
      • https://www.googleapis.com/auth/drive

    • Even if the server script uses the scopes except for scripts to use Drive API, the scopes except for scopes for Drive API are not required to be included. Because the use of other scopes is authorized by the browser, before it accesses to Web Apps. This means that when it accesses to Web Apps by GET or POST request, only the scopes for using Drive API are required.

    • For example, when you want to access to Web Apps using GAS, even if you use {"Authorization": "Bearer " + ScriptApp.getOAuthToken()} to the headers, when the error of <TITLE>Unauthorized</TITLE> occurs, please confirm the scopes of the scripts at the script editor. If https://www.googleapis.com/auth/drive is not included in the scopes, please add it. In order to add it, for example, you can put // DriveApp.getFiles() in the scripts as a comment. By this, the script editor will automatically include https://www.googleapis.com/auth/drive.

  • If no scopes are used for the scripts of Web Apps, owner and client users can run the script of Web Apps without the authorization of scopes using own browser. But the access token is required to access to Web Apps, if it is "Who has access to the app:": Only myself or "Who has access to the app:": Anyone.

  • Only when it is "Who has access to the app:": Anyone, even anonymous, owner and client users can access to Web Apps without the access token.

Share project of Web Apps with client users

When client users access to Web Apps with Situation 2 and 5, the owner of Web Apps is required to share the project deployed Web Apps with the client users. If the project is not shared, the error message of <title>Google Drive - Access Denied</title> is returned. For example, if the project is the container-bound script with Spreadsheet, please share the spreadsheet with the client users.

For sharing the project, you can manually share it and use "VIEW" to the permissions.

Also you can share the project using GAS scripts. The sample GAS script for sharing the project with the client users is as follows. When you use this, please input email and fileId. For example, if the project is the container-bound script with Spreadsheet, the fileId is that of spreadsheet. If you want to send a notification email when the project is shared, please modify from sendNotificationEmail=false to sendNotificationEmail=true.

// DriveApp.getFiles() // This comment is used for including a scope of https://www.googleapis.com/auth/drive. Of course, you can use Manifests for the project.
var email = "### Client user's email address ###";
var fileId = "### fileId of project ###";

var url =
  "https://www.googleapis.com/drive/v3/files/" +
  fileId +
  "/permissions?sendNotificationEmail=false";
var params = {
  method: "post",
  headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
  contentType: "application/json",
  payload: JSON.stringify({
    role: "reader",
    type: "user",
    emailAddress: email,
  }),
  muteHttpExceptions: true,
};
var res = UrlFetchApp.fetch(url, params);
Logger.log(res);

Understanding Flow of Request to Web Apps Created by Google Apps Script

Here, I would like to introduce a report for understanding the flow of the request to Web Apps created by Google Apps Script. There might be a case that various applications using the Web Apps are created and the Web Apps are used as the webhook. In that case, it is considered that when you have understood the flow of requests to the Web Apps, your goal might be able to be smoothly achieved. In this report, I would like to introduce the information about it.

You can see the detail of this at the following my gist.

https://gist.github.com/tanaikech/131ba814a1f6012fd6a5ffe11789971f

How to use dev mode from outside

When you deploy Web Apps, you can see the link labeled latest code. The link is like https://script.google.com/macros/s/###/dev. The official document of Google is as follows.

This URL can only be accessed by users who have edit access to the script. This instance of the app always runs the most recently saved code — not necessarily a formal version — and is intended for quick testing during development.

When you access to the link of latest code using your browser under you login to Google, you can access to Web Apps with the dev mode. But if you want to access to Web Apps with the dev mode from outside, there are no documents for the method. Here, it introduces such method.

When it accesses to the URL of latest code from outside, the login screen is returned. This situation indicates that it is required to use the access token for accessing to the URL. So as a sample, I show you the sample curl command as follows. The requirement scope is https://www.googleapis.com/auth/drive.

Requesting to doGet()

curl -L \
  -H "Authorization: Bearer ### access token ###" \
  "https://script.google.com/macros/s/#####/dev"

Also from Standard Query Parameters, you can use the following method using the access token as the query parameter.

curl -L "https://script.google.com/macros/s/#####/dev?access_token=### access token ###"
  • Replace ### access token ### to the value retrieved by ScriptApp.getOAuthToken().
  • Replace https://script.google.com/macros/s/#####/dev to your endpoint retrieved by deploying Web Apps.

Requesting to doPost()

curl -L \
  -H "Authorization: Bearer ### access token ###" \
  -d "key=value" \
  "https://script.google.com/macros/s/#####/dev"

Also from Standard Query Parameters, you can use the following method using the access token as the query parameter.

curl -L \
  -d "key=value" \
  "https://script.google.com/macros/s/#####/dev?access_token=### access token ###"
  • Replace ### access token ### to the value retrieved by ScriptApp.getOAuthToken().
  • Replace https://script.google.com/macros/s/#####/dev to your endpoint retrieved by deploying Web Apps.
  • I used -d "key=value" for the post method. If you don't want to put the values, please replace to -d "".

Note

  • By using above method, you can use the latest script of Web Apps from outside.
  • If you want to use the dev mode as simple test, there is a simple method for retrieving the access token.
    1. Login to Google and open the script editor.
    2. Copy and paste the following script.
      • function getAccesstoken() {Logger.log(ScriptApp.getOAuthToken())} // DriveApp.getFiles()
    3. Run the function getAccesstoken() and retrieve the access token.

By this, you can test it using the retrieved access token.

Event object of Web Apps

When Web Apps is used, doGet(e) and doPost(e) are used for the GET and POST method, respectively. Here, it introduces the event object of e.

Sample script of Web Apps

The sample script for GET and POST method of Web Apps is as follows.

function doGet(e) {
  e.method = "GET";
  return ContentService.createTextOutput(JSON.stringify(e)).setMimeType(
    ContentService.MimeType.JSON
  );
}

function doPost(e) {
  e.method = "POST";
  return ContentService.createTextOutput(JSON.stringify(e)).setMimeType(
    ContentService.MimeType.JSON
  );
}

Sample client for accessing to Web Apps.

As a sample client, curl is used.

Pattern 1

Sample curl

$ curl -L "https://script.google.com/macros/s/#####/exec?key1=value1&key2=value2&key3=value3"

Result

{
  "parameter": {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
  },
  "contextPath": "",
  "contentLength": -1,
  "queryString": "key1=value1&key2=value2&key3=value3",
  "parameters": {
    "key1": ["value1"],
    "key2": ["value2"],
    "key3": ["value3"]
  },
  "method": "GET"
}

Pattern 2

Sample curl

$ curl -L -d "key1=value1" -d "key2=value2" "https://script.google.com/macros/s/#####/exec?key3=value3"

Result

{
  "parameter": {
    "key1": "value1",
    "key2": "value2",
    "key3": "value3"
  },
  "contextPath": "",
  "contentLength": 23,
  "queryString": "key3=value3",
  "parameters": {
    "key1": ["value1"],
    "key2": ["value2"],
    "key3": ["value3"]
  },
  "postData": {
    "type": "application/x-www-form-urlencoded",
    "length": 23,
    "contents": "key1=value1&key2=value2",
    "name": "postData"
  },
  "method": "POST"
}

Pattern 3

Sample curl

$ curl -L -d '{"key1": "value1", "key2": "value2"}' "https://script.google.com/macros/s/#####/exec?key3=value3"

Result

{
  "parameter": {
    "key3": "value3",
    "{\"key1\": \"value1\", \"key2\": \"value2\"}": ""
  },
  "contextPath": "",
  "contentLength": 36,
  "queryString": "key3=value3",
  "parameters": {
    "key3": ["value3"],
    "{\"key1\": \"value1\", \"key2\": \"value2\"}": [""]
  },
  "postData": {
    "type": "application/x-www-form-urlencoded",
    "length": 36,
    "contents": "{\"key1\": \"value1\", \"key2\": \"value2\"}",
    "name": "postData"
  },
  "method": "POST"
}

Pattern 4

Sample curl

$ curl -L -H "Content-Type: application/json" -d '{"key1": "value1", "key2": "value2"}' "https://script.google.com/macros/s/#####/exec?key3=value3"

Result

Although the JSON object is sent as application/json and Web Apps recognizes the data as application/json, the JSON parse of contents is not parsed. Please be careful this. In this case, it is required to parse using JSON.parse().

{
  "parameter": {
    "key3": "value3"
  },
  "contextPath": "",
  "contentLength": 36,
  "queryString": "key3=value3",
  "parameters": {
    "key3": ["value3"]
  },
  "postData": {
    "type": "application/json",
    "length": 36,
    "contents": "{\"key1\": \"value1\", \"key2\": \"value2\"}",
    "name": "postData"
  },
  "method": "POST"
}

Pattern 5

Sample curl

In this pattern, the filename is added to the URL as follows.

https://script.google.com/macros/s/#####/exec/fileName.txt

In this case, the authorization is required. Please be careful this. So when the curl command is used, the access token is required to be used as follows.

$ curl -L \
  -H "Authorization: Bearer ###"  \
  -H "Content-Type: application/json" \
  -d '{"key1": "value1", "key2": "value2"}' \
  "https://script.google.com/macros/s/#####/exec/fileName.txt?key3=value3""

Also, when you have already logged in Google account, you can directly access to the URL. But in this case, it is the GET method.

When you run above curl command, you can retrieve the following values. fileName.txt can be retrieved by pathInfo.

Result

{
  "pathInfo": "fileName.txt",
  "contextPath": "",
  "postData": {
    "contents": "{\"key1\": \"value1\", \"key2\": \"value2\"}",
    "length": 36,
    "name": "postData",
    "type": "application/json"
  },
  "contentLength": 36,
  "parameter": {
    "key3": "value3"
  },
  "parameters": {
    "key3": ["value3"]
  },
  "queryString": "key3=value3",
  "method": "POST"
}

pathInfo: Updated on February 14, 2023

In the current stage, it seems that pathInfo can be used with the access token. It supposes that the following sample script is used.

function doGet(e) {
  return ContentService.createTextOutput(JSON.stringify(e));
}

When you log in to your Google account and you access https://script.google.com/macros/s/###/exec/sample.txt with your browser, {"contextPath":"","parameter":{},"pathInfo":"sample.txt","contentLength":-1,"parameters":{},"queryString":""} can be seen.

In this case, when you access it without logging in Google account, even when Web Apps is deployed as Execute as: Me and Who has access to the app: Anyone, the log in screen is opened. Please be careful about this.

And, if you want to access with https://script.google.com/macros/s/###/exec/sample.txt using a script, please request it by including the access token. The sample curl command is as follows. In this case, the access token can be used as the query parameter. Please include one of the scopes of Drive API in the access token.

curl -L "https://script.google.com/macros/s/###/exec/sample.txt?access_token=###"

By this, the following result is returned.

{"contextPath":"","queryString":"access_token=###"},"pathInfo":"sample.txt","parameters":{"access_token":["###"]},"contentLength":-1}

Logs in Web Apps for Google Apps Script

Gists

This is a report for retrieving the logs in Web Apps for Google Apps Script, when it requests to the Web Apps.

Experimental condition

1. Sample script for Web Apps

const doGet = (e) => {
  Logger.log(`GET method: ${JSON.stringify(e)}`);
  console.log(`GET method: ${JSON.stringify(e)}`);
  return ContentService.createTextOutput(
    JSON.stringify({ method: "GET", e: e })
  );
};
const doPost = (e) => {
  Logger.log(`POST method: ${JSON.stringify(e)}`);
  console.log(`POST method: ${JSON.stringify(e)}`);
  return ContentService.createTextOutput(
    JSON.stringify({ method: "POST", e: e })
  );
};
  • This Web Apps is deployed as Execute the app as: Me and Who has access to the app: Anyone, even anonymous.

2. Sample Google Apps Script project

  1. Google Apps Script of standalone type WITHOUT linking Google Cloud Platform (GCP) Project

    • In this case, you can retrieve this standalone Google Apps Script by directly creating.

  2. Google Apps Script of standalone type WITH linking Google Cloud Platform (GCP) Project

    • In this case, you can retrieve this standalone Google Apps Script by this flow.

3. Experimental procedure

To above Web Apps of doGet and doPost, it requests with the following 4 patterns.

  1. For doGet.

    $ curl -L "https://script.google.com/macros/s/###/exec"

  2. For doPost.

    $ curl -L -d "key=value" "https://script.google.com/macros/s/###/exec"

  3. For doGet. Access token is used.

    $ curl -L -H "Authorization: Bearer ###" "https://script.google.com/macros/s/###/exec"

  4. For doPost. Access token is used.

    $ curl -L -H "Authorization: Bearer ###" -d "key=value" "https://script.google.com/macros/s/###/exec"

Result and discussions

The conditions which can confirm the logs are as follows.



Without access token With access token
Without linking GCP Apps Script Dashboard
With linking GCP Stackdriver Apps Script Dashboard and Stackdriver


From above results, it was found as follows.

  • If you use the default Google Apps Script project without linking GCP, in order to retrieve the logs which requested to the Web Apps, please access to the Web Apps using the access token, even when the Web Apps is deployed as Execute the app as: Me and Who has access to the app: Anyone, even anonymous.

  • If you use the Google Apps Script project with linking GCP, you can retrieve all logs of users who accessed to the Web Apps at Stackdriver, even when the Web Apps is deployed as Execute the app as: Me and Who has access to the app: Anyone, even anonymous.

  • In this case, the logs couldn't be seen with Logger.log for above all situations.

IMPORTANT

In the current stage, when the access token is used for XMLHttpRequest and fetch of Javascript in the request headers, the error related to CORS occurs. So, in this report, I would like to propose the workaround for resolving this issue.

Limitation of simultaneous connection to Web Apps

The limitation of simultaneous connection is also investigated. The fetchAll method added by recent Google's update was used for this investigation. I have reported that the fetchAll method is worked by the asynchronous processing. This can be used for measuring the limitation of simultaneous connection. From the result of measurement, it was found that the limitation of simultaneous connection to one Web Apps server is under 30. This is the same with scripts.run method of Apps Script API.

When "Current limitations" of "Quotas for Google Services", it says that the simultaneous executions is 30.

Error messages

When the error messages are returned from Web Apps, you can see the messages into the tag of <title>### Error message ###</title> including in HTML output. From the error messages, you can know the reason of the error using the following table. I think that there may be other errors. So if you found them, when you tell me them, I'm glad.

Execute the app as Who has access to the app Access Status code Error messages Reason
User accessing the web app Only myself,
Anyone		 Owner,
Users		 200 Authorization needed Scopes for scripts of Web Apps are not authorized.
User accessing the web app,
Me		 Only myself,
Anyone		 Owner,
Users		 200 Meet Google Drive – One place for all your files No access token.
For all settings For all settings Owner,
Users		 200 Error "Service invoked too many times in a short time: exec qps. Try Utilities.sleep(1000) between calls." is shown in Body.
User accessing the web app,
Me		 Only myself,
Anyone		 Owner,
Users		 401 Unauthorized Bad access token.
No required scopes.
User accessing the web app,
Me		 Anyone Users 403 Google Drive - Access Denied Project of Web Apps is not shared with users.
User accessing the web app,
Me		 Only myself Users 404 Google Drive -- Page Not Found Users cannot access.

CORS in Web Apps

In this section, I would like to introduce CORS in Web Apps. As a sample situation, it supposes that Web Apps is requested using GET and POST method with Javascript. The settings of Web Apps are Execute the app as: Me and Who has access to the app: Anyone, even anonymous.

The Javascript of the client side, which was used for testing, is as follows.

const url = "https://script.google.com/macros/s/###/exec";

function get() {
  fetch(url)
    .then((res) => {
      console.log(res.status);
      return res.text();
    })
    .then((res) => console.log(res));
}

function post() {
  const obj = { key: "value" };
  fetch(url, { method: "POST", body: JSON.stringify(obj) })
    .then((res) => {
      console.log(res.status);
      return res.text();
    })
    .then((res) => console.log(res));
}

Sample script 1

Script for Web Apps

const doGet = (e) => {};
const doPost = (e) => {};

Result

For both requests, the following response is returned. Error related to CORS occurs.

Access to fetch at 'https://script.google.com/macros/s/###/exec' from origin 'https://###script.googleusercontent.com' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.

Sample script 2

Script for Web Apps

const doGet = (e) => HtmlService.createHtmlOutput();
const doPost = (e) => HtmlService.createHtmlOutput();

Result

For both requests, the following response is returned. Error related to CORS occurs.

Access to fetch at 'https://script.google.com/macros/s/###/exec' from origin 'https://###script.googleusercontent.com' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.

Sample script 3

Script for Web Apps

const doGet = (e) => ContentService.createTextOutput();
const doPost = (e) => ContentService.createTextOutput();

Result

For both requests, the following response is returned. No error occurs.

200

Sample script 4

Script for Web Apps

const doGet = (e) => ContentService.createTextOutput();
const doPost = (e) => ContentService.createTextOutput();

Script for Javascript

In this case, axios is used. This sample referred Stackoverflow.

const url = "https://script.google.com/macros/s/###/exec";

async function get() {
  const res = await axios.get(url);
  console.log(res);
}

async function post() {
  const obj = { key: "value" };
  const res = await axios.post(url, obj);
  console.log(res);
}

In this case, at post(), the object is not converted to the string with JSON.stringify.

Result

  • No error occurs at get().
  • Error related to CORS occurs at post().

Removing this error

Please convert the object to the string with JSON.stringify. By this, the error can be removed. No error occurs.

async function post() {
  const obj = { key: "value" };
  const res = await axios.post(url, JSON.stringify(obj));
  console.log(res);
}

When above script didn't work, please set "Content-Type": "text/plain" to the header and test it again. On the other hand, when the following script is used, No error occurs at post().

function post() {
  const obj = { key: "value" };
  fetch(url, { method: "POST", body: obj })
    .then((res) => {
      console.log(res.status);
      return res.text();
    })
    .then((res) => console.log(res));
}

Summary

As the result, in order to prevent the error related to CORS, it was found the following important points.

  1. It is required to return ContentService.createTextOutput() and ContentService.createTextOutput("done") from doGet and doPost.

  2. For POST method, the data is required to be converted to the string and sent to Web Apps.

Reference

Confidentiality of scripts for Web Apps

  • If you don't want to publish the scripts of Web Apps, you can achieve it using Situation 3.

    • In this case, anybody can access the Web Apps.

  • If you want to make only the special users access to Web Apps, you can achieve it using Situation 2 and 5.

    • In this case, the scripts of Web Apps can be seen by the users, because the project of scripts has to be shared with the users.

Sample script of server side

The simple sample-script of server side (Web Apps) is as follows. When the client users access to this server, the server returns URL query and the request body which were sent by the client. And also a text file is created. In this script, the scope of https://www.googleapis.com/auth/drive is used for creating a text file. So when you use this script at Situation 4 and Situation 5 as mentioned above, at first, please authorize the scope by accessing to Web Apps using your browser. After the authorization was done, the text file is created by requesting the following scripts for the client side.

function feedback(e, method) {
  var id = "";
  var user = Session.getEffectiveUser().getEmail();
  if (e.parameter.key1 == "value1") {
    id = DriveApp.createFile(
      "sample.txt",
      "Created by " + user,
      MimeType.PLAIN_TEXT
    ).getId();
  }
  return ContentService.createTextOutput(
    JSON.stringify({
      result: Object.keys(e.parameters).map(function (f) {
        return f + "," + e.parameters[f];
      }),
      method: method,
      createdFile: id ? id + " was created by " + user : "Not created.",
    })
  ).setMimeType(ContentService.MimeType.JSON);
}

function doGet() {
  return feedback(e, "GET");
}

function doPost() {
  return feedback(e, "POST");
}

Sample scripts of client side

Access with access token

GET

Curl

curl -GL \
  -H "Authorization: Bearer ### your access token ###" \
  -d "key1=value1" \
  -d "key2=value2" \
  "https://script.google.com/macros/s/#####/exec?key3=value3"

Google Apps Script

// DriveApp.getFiles() // This comment is used for including a scope of https://www.googleapis.com/auth/drive.
var params = {
  method: "GET",
  headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
  muteHttpExceptions: true,
};
var url =
  "https://script.google.com/macros/s/#####/exec?key1=value1&key2=value2&key3=value3";
var res = UrlFetchApp.fetch(url, params);
Logger.log(res);

POST

Curl

curl -L \
  -H "Authorization: Bearer ### your access token ###" \
  -d "key1=value1" \
  -d "key2=value2" \
  "https://script.google.com/macros/s/#####/exec?key3=value3"

Google Apps Script

// DriveApp.getFiles() // This comment is used for including a scope of https://www.googleapis.com/auth/drive.
var params = {
  method: "POST",
  headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
  payload: { key1: "value1", key2: "value2" },
  muteHttpExceptions: true,
};
var url = "https://script.google.com/macros/s/#####/exec?key3=value3";
var res = UrlFetchApp.fetch(url, params);
Logger.log(res);

Access without access token

GET

Curl

curl -GL \
  -d "key1=value1" \
  -d "key2=value2" \
  "https://script.google.com/macros/s/#####/exec?key3=value3"

Google Apps Script

var params = {
  method: "GET",
  muteHttpExceptions: true,
};
var url =
  "https://script.google.com/macros/s/#####/exec?key1=value1&key2=value2&key3=value3";
var res = UrlFetchApp.fetch(url, params);
Logger.log(res);

POST

Curl

curl -L \
  -d "key1=value1" \
  -d "key2=value2" \
  "https://script.google.com/macros/s/#####/exec?key3=value3"

Google Apps Script

var params = {
  method: "POST",
  payload: { key1: "value1", key2: "value2" },
  muteHttpExceptions: true,
};
var url = "https://script.google.com/macros/s/#####/exec?key3=value3";
var res = UrlFetchApp.fetch(url, params);
Logger.log(res);

Sample scripts of client side by various languages

Now, you can see the following 11 sample scripts at https://gist.github.com/tanaikech/a72aab0242012362c46ec69031c720d5.

  1. curl
  2. Google Apps Script
  3. Javascript
  4. ajax
  5. Node.js
  6. axios
  7. angular
  8. go
  9. python
  10. php
  11. powershell

Status code from Web Apps

Here, I would like to introduce the status code returned from Web Apps.

Preparation:

As the preparation, Web Apps was deployed with "Execute the app as" and "Who has access to the app" as Me and Anyone, even anonymous, respectively. The sample script for Web Apps is as follows.

function doGet(e) {
  return ContentService.createTextOutput("GET: Done.");
}

Experiment:

Using Google Apps Script:

At first, the status code was checked using Google Apps Script. The deployed Web Apps was requested with the following script.

function myFunction() {
  var url_exec = "https://script.google.com/macros/s/###/exec";
  var url_dev = "https://script.google.com/macros/s/###/dev";
  var res = UrlFetchApp.fetchAll([{ url: url_exec }, { url: url_dev }]);
  res.forEach(function (e) {
    Logger.log(e.getResponseCode());
  });
}

In this case, the status code of 200 was obtained for both endpoints of exec and dev.

using Curl:

In order to retrieve the status code with curl, curl -s -o /dev/null -w "%{http_code}" http://www.example.org/ was used. This is from this thread. Here, the status code was investigated using the curl command. Because the curl can access by 2 kinds of request by the options as showing below.

  1. --include: Include the HTTP response headers in the output. The HTTP response headers can include things like server name, cookies, date of the document, HTTP version and more...

  2. --head: (HTTP FTP FILE) Fetch the headers only! HTTP-servers feature the command HEAD which this uses to get nothing but the header of a document. When used on an FTP or FILE file, curl displays the file size and last modification time only.

Using above options, the following 4 patterns were investigated.

  1. Request to the endpoint of https://script.google.com/macros/s/###/exec using the option --include.
    • curl -sL --include -o /dev/null -w "%{http_code}" "https://script.google.com/macros/s/###/exec"
    • 200 was returned.
  2. Request to the endpoint of https://script.google.com/macros/s/###/dev using the option --include.
    • curl -sL --include -o /dev/null -w "%{http_code}" "https://script.google.com/macros/s/###/dev"
    • 200 was returned.
  3. Request to the endpoint of https://script.google.com/macros/s/###/exec using the option --head.
    • curl -sL --head -o /dev/null -w "%{http_code}" "https://script.google.com/macros/s/###/exec"
    • 403 was returned.
  4. Request to the endpoint of https://script.google.com/macros/s/###/dev using the option --head.
    • curl -sL --head -o /dev/null -w "%{http_code}" "https://script.google.com/macros/s/###/dev"
    • 200 was returned.

As the result, it was found that when the Web Apps of the endpoint of https://script.google.com/macros/s/###/exec was requested with the option --head, the status code of 403 was obtained.

Result and discussions:

When the option --head is used for the curl command, from the document, this means that it requests only header and doesn't request the body. By this, it was found that the status code of 403 was returned.

Here, why was the status code of 200 returned for both options of --include and --head when it requests to the endpoint of dev? It is considered that the reason of this is due to that the login screen was returned. When the endpoint of dev is accessed, it is required to use the access token. When the access token is not used, the login screen is returned. In this case, the status code of 200 is returned. As the test case, when the access token is used for the endpoint of dev using below curl command,

curl -sL --head -H "Authorization: Bearer ###" -o /dev/null -w "%{http_code}" "https://script.google.com/macros/s/###/dev"

The status code of 403 was returned. From this result, the following results were obtained.

  • When only the header is retrieved under that the Web Apps works fine, 403 is returned.
  • When the login screen is returned, 200 is returned.

Applications for this situation

As one of applications using this situation, there is the registration of webhook at Trello's REST API.

When the Web Apps which was deployed as Execute the app as: Me and Who has access to the app: Anyone, even anonymous is registered as the webhook, an error of {"message":"URL (https://script.google.com/macros/s/###/exec) did not return 200 status code, got 403","error":"ERROR"} occurs. The reason of this error is due to above situation. So as the workaround for the registration, you can use the following flow.

  • Before you run the script, please set the Web Apps as follows. - "Execute the app as" and "Who has access to the app" are Me and Only myself, respectively.
  • After the response of like {"id":"###","description":"sample","idModel":"###","callbackURL":"https://script.google.com/macros/s/###/exec","active":true} was retrieved, please set the Web Apps as follows. - "Execute the app as" and "Who has access to the app" are Me and Anyone, even anonymous, respectively.

By this flow, the webhook can be used.

Sample script

This is a sample script of Google Apps Script for the registration of webhook URL.

var url = "https://api.trello.com/1/tokens/###/webhooks/?key=###";
var payload = {
  callbackURL: "https://script.google.com/macros/s/###/exec",
  idModel: "###",
  description: "sample",
};
var options = { method: "post", payload: payload };
var res = UrlFetchApp.fetch(url, options);
Logger.log(res.getContentText());

Concurrent access to Web Apps

  • Is there any limit on number of concurrent hits or simultaneous executions on Google App Script Web App

    • I answered to this thread in Stackoverflow. Ref

  • Benchmark: Concurrent Writing to Google Spreadsheet using Form

    • When the users try to write to a Spreadsheet using a form, the developers have to consider the concurrent submission from the form. For example, when multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such a situation was investigated.

    • As the result, when the success rate for writing concurrently to Google Spreadsheet is investigated, it was found that the concurrent writing with Web Apps created by Google Apps Script was suitable rather than Google Form. The threshold number of users for succeeding to write all data to Spreadsheet was 60 for Web Apps and 35 for Google Form, respectively. And, when Web Apps is used, it was also found that Lock Service and the long wait time was definitely required to be used for the multiple submission.

Implementing Pseudo 2FA for Web Apps

In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with "Anyone", anyone can access the Web Apps. And, there is the case that Web Apps deployed with "Anyone" is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.

You can see the detail of this report at https://gist.github.com/tanaikech/7a15164b1227e2ec2231fce24ae9daf2.

Request Web Apps using Fetch API of Javascript with the access token

In the current stage, when you want to request Web Apps with the access token, when the access token is including the request header, an error related to CORS occurs. Please be careful about this.

So, in this case, please include the access token in the query parameter. By this, the request can be worked. The sample script is as follows.

const accessToken = "###"; // Please set your access token.
const url = "https://script.google.com/macros/s/###/exec?access_token=" + accessToken;
fetch(url)
  .then((res) => res.text())
  .then((res) => console.log(res));

By this modification, you can access Web Apps with the access token.

By the way, for example, when the curl command is used, the access token can be included in the request header as follows.

$ curl -L \
-H "Authorization: Bearer ###" \
"https://script.google.com/macros/s/###/exec"

And also, the following curl command can be used.

$ curl -L "https://script.google.com/macros/s/###/exec?access_token=###"

Executing Google Apps Script with Service Account

You can see the detailed report at the following gist and Medium.

Workarounds

1. Reflecting Latest Script to Deployed Web Apps without Redeploying

This report is a workaround for reflecting the latest Google Apps Script to the deployed Web Apps without redeploying. https://gist.github.com/tanaikech/e46def22cf106b012dfa5ad359b93d24

Implementing Pseudo 2FA for Web Apps

Applications

Here, I would like to introduce the applications of Web Apps. If you can introduce your applications, please tell me. I would like to introduce them here.

1. Converting from a1Notation to GridRange

This is a sample API for converting from a1Notation to GridRange. The GridRange is often used at Sheets API. Although I have thought that a1Notation is easy to use, there are no methods for converting it. So I created this as an API. I would like to introduce this as a sample for this report.

The sample script is as follows. At first, please copy and paste it to new standalone project.

function doGet(e) {
  var output = {};
  if (e.parameter.sheetid && e.parameter.a1notation) {
    output = {
      result: a1notation2gridrange1(
        e.parameter.sheetid,
        e.parameter.a1notation
      ),
    };
  } else {
    output = { error: "Wrong parameters." };
  }
  return ContentService.createTextOutput(JSON.stringify(output));
}

// https://gist.github.com/tanaikech/95c7cd650837f33a564babcaf013cae0
function a1notation2gridrange1(sheetid, a1notation) {
  try {
    var data = a1notation.match(/(^.+)!(.+):(.+$)/);
    var ss = SpreadsheetApp.openById(sheetid).getSheetByName(data[1]);
    var range = ss.getRange(data[2] + ":" + data[3]);
    var gridRange = {
      sheetId: ss.getSheetId(),
      startRowIndex: range.getRow() - 1,
      endRowIndex: range.getRow() - 1 + range.getNumRows(),
      startColumnIndex: range.getColumn() - 1,
      endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
    };
    if (!data[2].match(/[0-9]/)) delete gridRange.startRowIndex;
    if (!data[3].match(/[0-9]/)) delete gridRange.endRowIndex;
    return gridRange;
  } catch (e) {
    return e;
  }
}

And then, please deploy this as Web Apps. The condition is as follows.

  • "Execute the app as:" : User accessing the web app
  • "Who has access to the app:": Only myself

By this condition, owner and each client user can use Spreadsheets on own Google Drive. Before you access to Web Apps, please don't forget to share the project to users. The usage is as follows. This is a curl sample. But of course, you can also use this API using Google Apps Script.

curl -GL \
  -H "Authorization: Bearer ### your access token ###" \
  -d "sheetid=### spreadsheet ID ###" \
  -d "a1notation=Sheet1%21A1%3aB2" \
  "https://script.google.com/macros/s/#####/exec"

When you use this, please do URL encode for the range.

2. URL to upload to Google Drive

https://stackoverflow.com/questions/47833728/url-to-upload-to-google-drive

3. How to get a data range of the Sheet

https://stackoverflow.com/questions/48917258/how-to-get-a-data-range-of-the-sheet

4. Enhanced Custom Function for Google Spreadsheet using Web Apps as Wrapper

https://github.com/tanaikech/Enhanced-Custom-Function-for-Google-Spreadsheet-using-Web-Apps-as-Wrapper

Sample situations

Here, I would like to introduce the sample situations with Web Apps. Those are threads of Stackoverflow. You can see the actual situations using Web Apps from them.

References

Licence

MIT

Author

Tanaike

Update History

This repository has been created on April 26, 2018. After this repository was created, the information has continued to be grown now.

TOP