CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsmax-execution-timeout
pancake
pancake

Reputation: 13

Google Sheets Macro to Remove Protections from Range "Exceeded maximum execution time"

I used a macro to apply protections to many of the cells in a range of several thousand cells in Sheets. Since the only way to manually delete protections is one at a time, I need a macro to unprotect every cell in the same range. I have found code for a few macros to remove protections, but none of them have worked for me. They 'exceed maximum execution time' without seeming to remove any protections. My account is the owner of the sheet, so I do have permission to delete the protections. I don't necessarily need to understand why my current code isn't working, just what will work.

Here is a link to a version of the file with everything cleared out except for the relevant range. The macro that applied the protections timed out, so not every cell in the range has protection, but before running it in chunks, I figured I would try and figure out how to remove the existing protections since it will need to be done later anyway. None of the cells with vowels or 'c' or 'g' were locked, and those ones in fact all have drop-downs if that matters any.

Protections Example

I ran this code that I found online to try to remove all protections in the sheet, but it timed out and nothing happened.

function RemoveSheetProtections() {
  var ss = SpreadsheetApp.getActive();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    if (protection.canEdit()) {
      protection.remove();
    }
  }
}

I also ran this code that I found, but again it timed out and nothing happened.

function RemoveRangeProtections() {
  var ss = SpreadsheetApp.getActive();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    if (protection.canEdit()) {
      protection.remove();
    }
  }
}

I also ran this code to try and remove the protections from a small range (only 406 cells), but again, it exceeded the maximum execution time without any protections being removed. The full range the macro needs to apply to for now is J2:W2793.

function RemoveProtections() {
    var ss = SpreadsheetApp.getActive();
    var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protections.length; i++) {
        if (protections[i].getRange().getA1Notation() == 'J2:W30') {
            protections[i].remove();
        }
    }
  }

Upvotes: 1

Views: 317

Answers (1)

Tanaike
Tanaike

Reputation: 201573

I believe your goal is as follows.

  • You want to remove all protected ranges from a Google Spreadsheet using Google Apps Script.
  • You want to reduce the process cost of your script.

In this case, how about using Sheets API? When Sheets API is used, a sample script is as follows.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const obj = Sheets.Spreadsheets.get(spreadsheetId, { fields: "sheets(protectedRanges(protectedRangeId))" });
  const requests = obj.sheets.reduce((ar, s) => {
    if (s.protectedRanges && s.protectedRanges.length > 0) {
      s.protectedRanges.forEach(({ protectedRangeId }) => ar.push({ deleteProtectedRange: { protectedRangeId } }));
    }
    return ar;
  }, []);
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

  • When this script is run, all protected ranges of all sheets in a Google Spreadsheet are removed.

  • When I tested this script using your provided Spreadsheet, the process time was about 50 seconds.

  • If you want to use this script for the specific sheets in a Google Spreadsheet, please modify the above script as follows.

    • From

        const obj = Sheets.Spreadsheets.get(spreadsheetId, { fields: "sheets(protectedRanges(protectedRangeId))" });

    • To

        // Please set the specific sheet names you want to use in an array as follows.
  const obj = Sheets.Spreadsheets.get(spreadsheetId, { fields: "sheets(protectedRanges(protectedRangeId))", ranges:["Sheet1", "Sheet2",,,] });

References:

Upvotes: 2

Related Questions