Technique for Protecting Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

Abstract

Google Apps Script automates tasks like managing protections in Google Spreadsheets. These protections control user access to specific cells. While scripts exist for this purpose, users still encounter challenges, prompting this report. The report aims to introduce techniques for managing protections using sample scripts, helping users understand and implement this functionality.

Introduction

Google Apps Script is a powerful tool that enables seamless management of Google Documents, Spreadsheets, Slides, Forms, APIs, and more. It is particularly effective for automating tasks within Google Spreadsheets. Google Spreadsheets offer cell protection capabilities, allowing you to define editable areas for different collaborators. Google Apps Script can effectively manage these protections. While online resources like Stack Overflow often feature questions related to spreadsheet protection, this report aims to introduce practical techniques through sample scripts. This will help users understand how to manage Google Spreadsheet protections using Google Apps Script.

Create Google Apps Script project

To prepare for testing the scripts, please follow these steps:

  1. Create a new standalone Google Apps Script project.
  2. Copy and paste the following scripts into the script editor of your project.
  3. Run the desired function within the script editor.

Important: These scripts are designed to run within the script editor. Please be cautious when using them in other situations like the triggers.

Sample situations

1. Protect the whole Google Spreadsheet using the Spreadsheet service

Before you use this script, please set the variables.

When this script is run, a Google Spreadsheet with the following protection is created in the same folder of the Google Apps Script project.

  • Spreadsheet is shared with userA.
  • All sheets are protected.
  • userA can edit only “Sheet1”.
  • The owner of the Spreadsheet can edit all sheets.
function sample1() {
  const userA = "###"; // Please set the shared user's email.

  // Preparation
  const ss = SpreadsheetApp.create("sample1");
  const ssId = ss.getId();
  ss.getSheets()[0].setName("Sheet1");
  ss.insertSheet("Sheet2");
  ss.addEditor(userA);
  SpreadsheetApp.flush();
  DriveApp.getFileById(ssId).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection
  ss.getSheets().forEach((sheet) => {
    [
      ...sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET),
      ...sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE),
    ].forEach((p) => p.remove());
    const p = sheet.protect();
    p.removeEditors(p.getEditors());
    if (sheet.getSheetName() == "Sheet1") {
      p.addEditor(userA);
    }
    if (p.canDomainEdit()) {
      p.setDomainEdit(false);
    }
  });
}

2. Protect the whole Google Spreadsheet using Drive API

In this script, Drive API is used. So, please enable Drive API v3 at Advanced Google services. Ref And, before you use this script, please set the variables.

When this script is run, a Google Spreadsheet with the following protection is created in the same folder of the Google Apps Script project.

  • Spreadsheet is shared with userA.
  • Created Google Spreadsheet is locked. That is a read-only mode. In this case, even the owner cannot edit.
function sample2() {
  const userA = "###@gmail.com"; // Please set the shared user's email.

  // Preparation
  const ss = SpreadsheetApp.create("sample2");
  const ssId = ss.getId();
  ss.getSheets()[0].setName("Sheet1");
  ss.insertSheet("Sheet2");
  ss.addEditor(userA);
  SpreadsheetApp.flush();
  DriveApp.getFileById(ssId).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection
  Drive.Files.update(
    { contentRestrictions: [{ readOnly: true, ownerRestricted: true }] },
    ssId
  );
}

3. Protect a part of cells for specific users

Before you use this script, please set the variables.

When this script is run, a Google Spreadsheet with the following protection is created in the same folder of the Google Apps Script project.

  • Spreadsheet is shared with userA and userB.
  • Cells “A1:B3” of “Sheet1” cannot be edited by userA and userB, while the owner can edit.
  • Cells “A4:B6” of “Sheet1” cannot be edited by userB, while userA can edit.
  • Cells “A7:B9” of “Sheet1” cannot be edited by userA, while userB can edit.
  • Other cells can be edited by userA and userB.
  • The owner can edit all cells.
  • In order to show the protected ranges, the background colors of cells are changed.
function sample3a() {
  const userA = "###@gmail.com"; // Please set the shared user's email 1.
  const userB = "###@gmail.com"; // Please set the shared user's email 2.

  const obj = {
    protects: [
      { ranges: ["A1:B3"], emails: [] },
      { ranges: ["A4:B6"], emails: [userA] },
      { ranges: ["A7:B9"], emails: [userB] },
    ],
  };

  // Preparation
  const ss = SpreadsheetApp.create("sample3a");
  const ssId = ss.getId();
  const sheet = ss.getSheets()[0].setName("Sheet1");
  ss.addEditors([userA, userB]);
  SpreadsheetApp.flush();
  DriveApp.getFileById(ssId).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection
  const colors = ["#f4cccc", "#d9ead3", "#c9daf8"];
  obj.protects.forEach(({ ranges, emails }, i) => {
    sheet
      .getRangeList(ranges)
      .getRanges()
      .forEach((r) => {
        r.setBackground(colors[i]);
        const p = r.protect();
        p.removeEditors(p.getEditors());
        if (emails.length > 0) {
          p.addEditors(emails);
        }
        if (p.canDomainEdit()) {
          p.setDomainEdit(false);
        }
      });
  });
}

You can reflect on your situation by modifying obj.

When there are a lot of protected ranges, the process cost becomes high with the above script using the Spreadsheet service. In that case, please try to use Sheets API. When the above script is modified with Sheets API, it becomes as follows.

In this modification, a Google Apps Script library UtlApp is used for converting A1Notation to Gridrange. So, please install it. Ref Also, please enable Sheets API at Advanced Google services. Ref

function sample3b() {
  const userA = "###@gmail.com"; // Please set the shared user's email 1.
  const userB = "###@gmail.com"; // Please set the shared user's email 2.

  const obj = {
    protects: [
      { ranges: ["A1:B3"], emails: [] },
      { ranges: ["A4:B6"], emails: [userA] },
      { ranges: ["A7:B9"], emails: [userB] },
    ],
  };

  // Preparation
  const ss = SpreadsheetApp.create("sample3b");
  const ssId = ss.getId();
  const sheet = ss.getSheets()[0].setName("Sheet1");
  ss.addEditors([userA, userB]);
  SpreadsheetApp.flush();
  DriveApp.getFileById(ssId).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection
  const colors = ["#f4cccc", "#d9ead3", "#c9daf8"];
  const sheetId = sheet.getSheetId();
  const requests = obj.protects.flatMap(({ ranges, emails }, i) => {
    sheet.getRangeList(ranges).setBackground(colors[i]);
    return ranges.map((r) => {
      return {
        addProtectedRange: {
          protectedRange: {
            range: UtlApp.convA1NotationToGridRange(r, sheetId),
            editors: { domainUsersCanEdit: false, users: emails },
          },
        },
      };
    });
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}

4. Unprotect a part of cells for the specific users

Before you use this script, please set the variables.

When this script is run, a Google Spreadsheet with the following protection is created in the same folder of the Google Apps Script project.

  • Spreadsheet is shared with userA and userB.
  • Cells “A1:B3” of “Sheet1” can be edited by userA and userB.
  • Cells “A4:B6” of “Sheet1” can be edited by userA, while userB cannot edit.
  • Cells “A7:B9” of “Sheet1” can be edited by userB, while userA cannot edit.
  • Other cells cannot be edited by userA and userB.
  • The owner can edit all cells.
  • In order to show the protected ranges, the background colors of cells are changed.
function sample4a() {
  const userA = "###@gmail.com"; // Please set the shared user's email 1.
  const userB = "###@gmail.com"; // Please set the shared user's email 2.

  const obj = {
    unprotects: [
      { ranges: ["A1:B3"], emails: [] },
      { ranges: ["A4:B6"], emails: [userA] },
      { ranges: ["A7:B9"], emails: [userB] },
    ],
  };

  // Preparation
  const ss = SpreadsheetApp.create("sample4a");
  const ssId = ss.getId();
  const sheet = ss.getSheets()[0].setName("Sheet1");
  ss.addEditors([userA, userB]);
  SpreadsheetApp.flush();
  DriveApp.getFileById(ssId).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection
  const colors = ["#f4cccc", "#d9ead3", "#c9daf8"];
  const p = sheet.protect();
  const unprotectedRanges = sheet
    .getRangeList(obj.unprotects.map(({ ranges }) => ranges))
    .getRanges();
  p.removeEditors(p.getEditors());
  if (p.canDomainEdit()) {
    p.setDomainEdit(false);
  }
  p.setUnprotectedRanges(unprotectedRanges);
  obj.unprotects.forEach(({ ranges, emails }, i) => {
    sheet
      .getRangeList(ranges)
      .getRanges()
      .forEach((r) => {
        r.setBackground(colors[i]);
        if (emails.length == 0) return;
        const p1 = r.protect();
        p1.removeEditors(p1.getEditors());
        if (p1.canDomainEdit()) {
          p1.setDomainEdit(false);
        }
        p1.addEditors(emails);
      });
  });
}

In this script, in order to protect a part of the unprotected ranges, the following step is run.

  1. Protect a sheet.
  2. Set unprotected ranges as “A1:B3”, “A4:B6”, and “A7:B9”.
  3. Protect the range of “A4:B6” for making only userA edit.
  4. Protect the range of “A7:B9” for making only userB edit.

The important point is to unprotect the ranges of “A1:B3”, “A4:B6”, and “A7:B9” in a protected sheet. Also, you can reflect on your situation by modifying obj.

When there are a lot of protected ranges, the process cost becomes high with the above script using the Spreadsheet service. In that case, please try to use Sheets API. When the above script is modified with Sheets API, it becomes as follows.

In this modification, a Google Apps Script library UtlApp is used for converting A1Notation to Gridrange. So, please install it. Ref Also, please enable Sheets API at Advanced Google services. Ref

function sample4b() {
  const userA = "###@gmail.com"; // Please set the shared user's email 1.
  const userB = "###@gmail.com"; // Please set the shared user's email 2.

  const obj = {
    unprotects: [
      { ranges: ["A1:B3"], emails: [] },
      { ranges: ["A4:B6"], emails: [userA] },
      { ranges: ["A7:B9"], emails: [userB] },
    ],
  };

  // Preparation
  const ss = SpreadsheetApp.create("sample4b");
  const ssId = ss.getId();
  const sheet = ss.getSheets()[0].setName("Sheet1");
  ss.addEditors([userA, userB]);
  SpreadsheetApp.flush();
  DriveApp.getFileById(ssId).moveTo(
    DriveApp.getFileById(ScriptApp.getScriptId()).getParents().next()
  );

  // Protection
  const sheetId = sheet.getSheetId();
  const colors = ["#f4cccc", "#d9ead3", "#c9daf8"];
  obj.unprotects.forEach(({ ranges }, i) =>
    sheet.getRangeList(ranges).setBackground(colors[i])
  );
  const requests = [
    {
      addProtectedRange: {
        protectedRange: {
          range: { sheetId },
          editors: { domainUsersCanEdit: false },
          unprotectedRanges: obj.unprotects.map(({ ranges }) =>
            ranges.map((r) => UtlApp.convA1NotationToGridRange(r, sheetId))
          ),
        },
      },
    },
  ];
  obj.unprotects.forEach(({ ranges, emails }) => {
    if (emails.length == 0) return;
    ranges.forEach((r) =>
      requests.push({
        addProtectedRange: {
          protectedRange: {
            range: UtlApp.convA1NotationToGridRange(r, sheetId),
            editors: { domainUsersCanEdit: false, users: [emails] },
          },
        },
      })
    );
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}

5. Remove all protections

Before you use this script, please set the variables.

When this script is run, all protections in a sheet are removed.

function sample5a() {
  const spreadsheetId = "###"; // Please set your Spreadsheet ID.
  const sheetName = "Sheet1"; // Please set your sheet name.

  const sheet =
    SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  [
    SpreadsheetApp.ProtectionType.SHEET,
    SpreadsheetApp.ProtectionType.RANGE,
  ].forEach((t) => sheet.getProtections(t).forEach((p) => p.remove()));
}

If there are many protections in the Spreadsheet, the above script might have high process costs. In that case, please try to use the following script. In this script, all protections in a sheet are removed using the Sheets API. So, please enable Sheets API at Advanced Google services. Ref

function sample5b() {
  const spreadsheetId = "###"; // Please set your Spreadsheet ID.
  const sheetNames = ["Sheet1"]; // Please set your sheet names you want to remove the protections.

  const obj = Sheets.Spreadsheets.get(spreadsheetId, {
    fields: "sheets(protectedRanges(protectedRangeId),properties(title))",
  });
  if (sheetNames.length > 0) {
    obj.sheets = obj.sheets.filter((s) =>
      sheetNames.includes(s.properties.title)
    );
  }
  if (obj.sheets.length == 0) return;
  const requests = obj.sheets.reduce((ar, s) => {
    if (s.protectedRanges) {
      s.protectedRanges.forEach(({ protectedRangeId }) =>
        ar.push({ deleteProtectedRange: { protectedRangeId } })
      );
    }
    return ar;
  }, []);
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}
  • In this script, when you change const sheetNames = ["Sheet1"]; to const sheetNames = [];, all protections in all sheets in a Google Spreadsheet are removed.

 Share!