CODEWITHSUNDEEP
google-apps-script
DauleDK
DauleDK

Reputation: 3423

Is it possible to batch protect sheets in Google-sheets API v4?

My objective is to protect hundreds of sheets in Google sheets, using the API here.

Only problem is that I can't find a method for protecting the sheet - only adding protected/unprotected ranges?

Solution

Thanks to Cameron; by providing the following batch update with just the sheetId specified, protects a sheet:

const ss = SpreadsheetApp.getActive();
  const sheetToProtect = ss.getSheetByName(<name_of_sheet_to_protect>);

  Sheets.Spreadsheets.batchUpdate(
    {
      includeSpreadsheetInResponse: false,
      requests: [
        {
          addProtectedRange: {
            protectedRange: {
              range: {
                sheetId: sheetToProtect.getSheetId(),
              },
            },
          },
        },
      ],
    },
    ss.getId()
  );

Upvotes: 1

Views: 778

Answers (1)

Cameron Roberts
Cameron Roberts

Reputation: 7367

I just realized your key requirement of bulk updating isn't covered by my original answer.

I believe you can use the Bulk Update API to protect an entire sheet. The GridRange can contain just a Sheet Id ({sheetId: 0} is a valid GridRange), the other 4 parameters specify "or not set if unbounded".

So an unbounded range on Sheet 0 will cover the entire sheet.

-- original answer --

If using Google Apps Script, you are better off using the Spreadsheet service, which provides a handy wrapper to the REST API you linked in your question.

The SpreadsheetApp Sheet object provides a "protect()" method that protects the entire sheet.

From the documentation:

// Protect the active sheet, then remove all other users from the list of editors.
var sheet = SpreadsheetApp.getActiveSheet();
var protection = sheet.protect().setDescription('Sample protected sheet');

// Ensure the current user is an editor before removing others. Otherwise, if the user's edit
// permission comes from a group, the script throws an exception upon removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

Upvotes: 3

Related Questions