Workaround: Making Users Edit Protected Cells using Google Apps Script

report / GasTips / spreadsheet / workaround
Google Apps Script / Spreadsheet

Gists

Abstract

One day, you might have a situation where you are required to make users edit the protected cells using Google Apps Script. This report introduces a workaround for achieving this situation. The key factors for achieving this are as follows. 1. Run the script as the owner of Spreadsheet even when the script is run by a user. 2. In order to run the script as the owner, the installable triggers and the Web Apps are used.

Introduction

At Google Spreadsheets, the sheets and cells can be protected. By protecting a sheet in a Google Spreadsheet shared with some users, editing the cells in the protected sheet by the users except for the owner of the Spreadsheet can be avoided. Namely, when the users who have no permission to edit the sheet try to edit the cells of the protected sheet, the cells cannot be edited. In this case, even when the users run a script for editing the cells of the protected sheet with a button on the Spreadsheet and/or the custom menu, the user cannot edit the cells using the script. On the other hand, when the owner of the Spreadsheet runs the script, the cells in the protected sheet can be edited by the script. However, there might be a situation in which you who is the owner of Spreadsheet want to make users edit the cells by a script while you don’t want to make users manually edit the cells. Also, I sometimes saw questions with the same situation on Stackoverflow. In this report, I would like to introduce a workaround for achieving such a situation.

Sample situation and principle

Here, it introduces a sample situation for explaining this workaround. In order to test this workaround, please do the following flow.

  1. Create a new Google Spreadsheet. In this case, you are the owner of Spreadsheet.
  2. Share your Spreadsheet with a user “A” as an editor.
  3. Protect a sheet by being able to be edited by only the owner while only cell “A1” is unprotected.

This flow can be automatically run by the following sample script. Please copy and paste the following script to the script editor, and set an email address of a sample shared user, and run the function createSampleSpreadsheet.

function createSampleSpreadsheet() {
  const sharedUserEmail = "###@gmail.com"; // Please set an email address of a sample shared user.

  const ss =
    SpreadsheetApp.create("sampleSpreadsheet").addEditor(sharedUserEmail);
  const sheet = ss.getSheets()[0].setName("Sheet1");
  const a1 = sheet.getRange("A1").insertCheckboxes();
  const p = sheet.protect();
  p.removeEditors(p.getEditors());
  if (p.canDomainEdit()) {
    p.setDomainEdit(false);
  }
  p.setUnprotectedRanges([a1]);
}

After a sample Spreadsheet is created, please log in to the sample shared user and edit cell “A1” and a cell except for “A1”. By this, you can confirm that “A1” can be edited and you cannot edit a cell except for “A1”. This is a sample Spreadsheet.

As the next step, it tests a simple sample script. Please copy and paste the following script to the script editor of the created Spreadsheet, and save the script, and open Spreadsheet again. By this, onOpen is automatically run, and you can see the created custom menu.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("sample")
    .addItem("Run sample 1", "sample")
    .addToUi();
}

function sample() {
  SpreadsheetApp.getActiveSheet().appendRow([new Date()]);
}

By the owner of Spreadsheet, please run Run sample 1 from the custom menu. By this, you can see the date object is appended to the cell “A2”.

As the next step, by the shared user of Spreadsheet, please run Run sample 1 from the custom menu. By this, you can see an error like Exception: You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit.. Because the script is run by the user who is not the owner of Spreadsheet, and the protected sheet cannot be edited by the users except for the owner of Spreadsheet. The shared user cannot edit the cells except for “A1”.

Here, when you want to make the shared user edit the cell in the protected sheet, from the above situation, it is found that when the script is run by the owner instead of the shared user, the script will work fine. In order to forcibly execute a function by the owner, it is considered the following 2 patterns.

Fig 1. Schematic illustration for understanding “Use installable triggers” and “Use Web Apps” of this workaround.

About “Use installable triggers” in Fig. 1, when the installable trigger is used, the script is run as the owner even when user “A” fires the trigger. There are several triggers in the installable triggers. Ref The official document of the installable triggers says Installable triggers always run under the account of the person who created them.. This is the current specification.

About “Use Web Apps”, when the Web Apps is used, the script is run as the owner even when user “A” accesses the Web Apps. Ref

In the next sections, I would like to introduce the sample scripts.

These sample scripts use the above-created sample Spreadsheet.

Pattern 1: Use installable triggers

In this pattern, in order to make a shared user edit a cell except for “A1” using a script, the installable trigger is used. In order to test this, please do the following flow.

1. Sample script

Please add the following script to the script editor of the Spreadsheet created in the above section. In this script, the function sample() is shown in the above section.

function installedOnEdit(e) {
  const { range } = e;
  if (range.getA1Notation() != "A1" || !range.isChecked()) return;

  // --- Please put your script here.
  sample();
  // ---

  range.uncheck();
}

2. Install OnEdit trigger

Please install the OnEdit trigger to the function installedOnEdit. Ref

3. Testing

When you (the owner of Spreadsheet) check the checkbox of “A1”, the function installable OnEdit is automatically run by the installable OnEdit trigger. And, the function sample is run. By this, you can see that the date object is appended to column “A”.

Also, even when the shared user checks the checkbox of “A1”, the function sample is run by the installable OnEdit trigger. And, you can see that the date object is appended to column “A”.

Here, the reason that the shared user can edit the protected cell is due to that the function sample is run as the owner of the Spreadsheet by the installable trigger.

Pattern 2: Use Web Apps

In this pattern, in order to make a shared user edit a cell except for “A1” using a script, the Web Apps is used. In order to test this, please do the following flow.

1. Deploy Web Apps.

The detailed information can be seen in the official document.

Please set this using the script editor.

  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”.
  5. Please select “Anyone with Google account” for “Who has access to the app:”.
  6. Please click “Deploy” button.
  7. On the script editor, at the top right of the script editor, please click “click Deploy” -> “Test deployments”.
  8. Copy Web Apps URL. It’s like https://script.google.com/macros/s/###/dev. This URL is used with the below sample script.

2. Sample script

Please add the following script to the script editor of the Spreadsheet created in the above section. And, please set your Web Apps URL to webAppsUrl. In this script, the function sample() is shown in the above section.

const doGet = (_) => runWebApps(true);

function runWebApps(e) {
  if (!e) {
    const webAppsUrl = "https://script.google.com/macros/s/###/dev"; // Please set your Web Apps URL.
    UrlFetchApp.fetch(webAppsUrl, {
      headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
    });
    return;
  }

  // --- Please put your script here.
  sample();
  // ---
}

// ### Please don't remove the following comment line.
// DriveApp.getFiles(); // This comment line is used for automatically detecting a scope of "https://www.googleapis.com/auth/drive.readonly". This scope is used for requesting Web Apps.
// ###

And, please modify onOpen as follows.

function onOpen() {
  SpreadsheetApp.getUi().createMenu("sample")
  .addItem("Run sample 1", "sample")
  .addItem("Run sample 2", "runWebApps")
  .addToUi();
}

By this, when the Spreadsheet is reopened, you can see 2 menus in the custom menu.

3. Testing

In order to test this script, when you (the owner of Spreadsheet) run “Run sample 2” in the updated custom menu, you can see that the date object is appended to column “A”.

Also, when the shared user runs “Run sample 2” in the updated custom menu, you can see that the date object is appended to column “A”.

Here, the reason that the shared user can edit the protected cell is due to that the function sample is run as the owner of the Spreadsheet by Execute as: Me of the Web Apps.

Demonstration

When the above workaround is used, the following result is obtained.

Fig. 2 Demonstration of this workaround.

In this case, the user who is not the owner of Spreadsheet does the test as follows.

  1. Confirm that the user cannot edit the cells except for “A1”.
  2. Run the function sample by “Run sample 1” of the custom menu. In this case, the user cannot put the value to the cell “A2” using a script.
  3. Run the function installedOnEdit by clicking the checkbox of “A1”. In this case, the user can put the value to the cell “A2” using a script because the script is run as the owner.
  4. Run the function runWebApps by “Run sample 2” of the custom menu. In this case, the user can put the value to the cell “A3” using a script because the script is run as the owner.

Summary

In this report, a workaround for making users edit the protected cells using a script was introduced. The key factors for achieving this are as follows.

  • Run the script as the owner of Spreadsheet even when the script is run by a user.
  • In order to run the script as the owner, the installable triggers and the Web Apps are used.

This workaround can be used with the current specification. If the specification is changed in the future update, this workaround might not be able to be used. Please be careful about this. If this report was useful for your situation, I’m glad.

Appendix

As additional information, when the Web Apps is used as mentioned in the section “Pattern 2: Use Web Apps”, the email address of each user can be also obtained. The sample script is as follows. In this sample, when runWebApps is run by the user, the email address of the user is obtained and put into the cell.

const doGet = (e) => runWebApps(e);

function runWebApps(e) {
  if (!e) {
    const webAppsUrl = "https://script.google.com/macros/s/###/dev"; // Please set your Web Apps URL.
    UrlFetchApp.fetch(
      `${webAppsUrl}?email=${Session.getActiveUser().getEmail()}`,
      { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }
    );
    return;
  }

  // --- Please put your script here.
  SpreadsheetApp.getActiveSheet().appendRow([new Date(), e.parameter.email]);
  // ---
}

 Share!