User Runs Script for Range Protected by Owner using Google Apps Script

submit.md

User Runs Script for Range Protected by Owner using Google Apps Script

There is a situation that it wants to make users run a script for the range protected by the owner using Google Apps Script. This is a sample script that an user runs a script for the range protected by the owner using Google Apps Script.

Demo

This demonstration shows the following situations.

• Spreadsheet is shared with an user.

• The cell "A1" is protected by the owner. Other users cannot be edited.

• Spreadsheet has a Google Apps Script like below sample script.

  • The script updates the cell "A1".

• In this demonstration, the user who is not the owner clicks the buttons.

  • When the button of "directRunScript" is clicked, directRunScript() of the following sample script is directly run. In this case, an error occurs because the cell "A1" is protected by the owner.

    • I would like to introduce to avoid this issue.

  • When the button of "runScript" is clicked, runScript() of the following sample script is directly run. In this case, no error occurs because the script is run as the owner even when the user who is not the owner is run.

Workaround

When there is a Spreadsheet including the protected range by the owner of Spreadsheet, when an user who is not the owner runs the script for updating the protected range, an error occurs. The reason of this error is due to that the user who is not the owner runs the script. In this workaround, the script is always run as the owner using Web Apps. By this, even when the user runs the script, the script works without the error.

Usage

1. Prepare script.

Please copy and paste the following script to the script editor and save it. And, please set the endpoint for the developer mode of Web Apps to url. Ref

// User runs this function.
function runScript() {
  const activeSheet = SpreadsheetApp.getActiveSheet().getSheetName();
  const url = "https://script.google.com/macros/s/###/dev"; // In the current stage, ScriptApp.getService().getUrl() returns the endpoint of no developer mode. So in order to use this script, I thought that the endpoint for the developer mode might be suitable for manually setting.
  UrlFetchApp.fetch(`${url}?sheetName=${activeSheet}`, {
    headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
  });

  // DriveApp.getFiles()  // This is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for the access token.
}

// When runScript() is run, this function is run.
const doGet = (e) => ContentService.createTextOutput(mainScript(e));

// This script is run by Web Apps.
function mainScript(e) {
  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(e.parameter.sheetName)
    .getRange("A1")
    .setValue("updated value");
  return "ok";
}

// Sample script for directly running.
function directRunScript() {
  mainScript({ parameter: { sheetName: "Sheet1" } });
}

2. Deploy Web Apps.

1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
2. Select "Me" for "Execute the app as:".
   • By this, the script is run as the owner.
3. Select "Anyone" for "Who has access to the app:".
   • In this case, the access token is required to request to Web Apps.
4. Click "Deploy" button as new "Project version".
5. Automatically open a dialog box of "Authorization required".
   1. Click "Review Permissions".
   2. Select own account.
   3. Click "Advanced" at "This app isn't verified".
   4. Click "Go to ### project name ###(unsafe)"
   5. Click "Allow" button.
6. Click "OK".

3. Test this workaround.

1. Please protect the cell "A1" of the Spreadsheet and share the Spreadsheet with an user.

2. Please run the function directRunScript() by an user who is not the owner. By this, you can confirm an error.

3. Please run the function runScript() by an user who is not the owner. By this, you can confirm the script works without the error.

Note

• Please use this script with enabling V8.

• In the current stage, under the default settings, the URL retrieved by ScriptApp.getService().getUrl() is like https://script.google.com/macros/s/###/dev. By this, even when the script of Web Apps is changed, it is not required to redeploy the Web Apps as new version. But in this case, the access token is required to request to the Web Apps.

References

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• Related questions
  • https://stackoverflow.com/q/64086095
  • https://stackoverflow.com/q/64666605
  • https://stackoverflow.com/q/64673622

Thanks a lot, it helped for me. However, I had to hard coded url of the app after deploy

@Rokedd Thank you for your comment. ScriptApp.getService().getUrl() had returned the endpoint for the developer mode before. So I have used this. But, recently, the specification was changed. Now, the method doesn't return the endpoint for the developer mode. So I updated my sample script.

Hi @tanaikech , thank you for this script it is very useful.

I am trying to modify it so that I can detect what row number that was selected whilst the button was pressed to make an update on a protected cell in that row.

It seems when you call get row on active cell in the mainscript, it always returns 1. Is there any way to pass this variable over?

EDIT: I think I could workaround by making some sort of scheduled jobs sheet by inputting all the row data I need in there just before (during runScript on the button click). And then having a function call that data in mainscript, and delete the line when it's completed.

Do you think there is a more efficient way to do this?

Thanks.