Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The flow of this workaround is as follows.

  1. Create Web Apps created by Google Apps Script and deploy it as Web Apps. As the returned value, the XML data is returned.
    • Your script can be included in this script.
  2. User put a formula of =IMPORTML("WebApps URL", "xpath") to a cell.

By this flow, you can achieve to let users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

Usage

1. Create a new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access https://script.new/. In this case, if you are not logged in to Google, the log-in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Sample script.

Please copy and paste the following script to the created Google Apps Script project and save it. This script is used for Web Apps.

function doGet(e) {
  const { value } = e.parameter;

  // do something: Please put the script you want to run.

  return ContentService.createTextOutput(
    `<result>Updated ${value}</result>`
  ).setMimeType(ContentService.MimeType.XML); // Here, you can return the value.
}

This script is a sample script. Even when you directly use this, you can test this workaround.

3. Deploy Web Apps.

The detailed information can be seen at the official document.

  1. On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
  2. Please click “Select type” -> “Web App”.
  3. Please input the information about the Web App in the fields under “Deployment configuration”.
  4. Please select “Me” for “Execute as”.
    • This is the importance of this workaround.
  5. Please select “Anyone” for “Who has access”.
  6. Please click “Deploy” button.
  7. Copy the URL of the Web App. It’s like https://script.google.com/macros/s/###/exec.

4. Testing.

In order to test this workaround, please put your Web Apps URL, the inputted value, and the formula as follows.

In this test, the sample formula is =IMPORTXML(CONCATENATE(A1,"?value=",A2),"/result").

By this flow, the user can run your script without both authorizing the scopes and showing your script. In this workaround, the value is returned as XML data. By this, Web Apps can be used with IMPORTXML.

Note

  • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
  • You can see the detail of this in the report of “Redeploying Web Apps without Changing URL of Web Apps for new IDE”.
  • My proposed script is a simple script. So please modify it for your actual situation.

References

 Share!