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

submit.md

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

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.
   • 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".

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

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• This workaround was answered to this thread in Stackoverflow.

Thank you for the comment. About In this post it seems "Execute as" can only be me or "Users accessing the web app"., in the above sample, I wrote Please select "Me" for "Execute as". This is the importance of this workaround.. So I cannot understand your comment. I apologize for my poor English skill.

It's fine. Maybe I didn't explain it well. So what I intend is to make the app script can only be accessed or edited by specific developers, so "Execute as" can add me or add my teammates. In the mean time, I don't want non-tech people can access the script, but they can use the spreadsheet and the functionality the app script provides, without authorizing scopes.

As shown above, I can only add "Me", no ways to add specific people. Actually I am trying to find a way to collaborate on the app script with other developers, so the edit ability on the app script should not be shared with users.

Thank you for replying. I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I would be grateful if you can forgive my poor English skill.

No problem.