Is it possible to batch process range protections in Google Apps Script?

Question

I have to create a dozen protected ranges in a sheet. I have code that works but is very slow because it contacts the server for each range. I know it's possible to work on a local copy of the data if there's some cell processing involved. Is it possible for range protections also?

If it's not, would caching help?

The below code uses the username from the first row as an editor for a bunch of rows in the same column.

  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadSheet.getSheets();

//Set protections per column, we start from the 4th.
  for (var i = 4; i <= sheets[3].getLastColumn(); i++){
    ///Get the username.
    var editor = sheets[3].getRange(1, i).getDisplayValue();

    //Set the protection.
    var protection = sheets[3].getRange(3, i, 22, 1).protect();
    protection.setDescription(editor);

    //Handle the case of deleted/unknown usernames.
    try{
      protection.addEditor(editor + '@domain.com');
    } catch(error){
      protection.addEditor('user@domain.com');
    }
  }

I've found a solution for a similar issue https://stackoverflow.com/a/37820854 but when I try to apply it to my case I get an error "TypeError: Cannot find function getRange in object Range" so I must be doing something wrong.

  var test = [];
  for (var i = 4; i <= sheets[3].getLastColumn(); i++){
    test.push(sheets[3].getRange(3, i, 22, 1));
  }
  var editor;
  for (var i = 0; i<test.length; i++){
    var editor = test[i].getRange(1, 1).getDisplayValue();
  }

score 0 · Answer 1 · answered Aug 30 '19 at 10:05

The syntax for the method getRange() is getRange(row, column, numRows, numColumns), while you counter variable i loops through the COLUMNS instead of ROWS.

If your intention is to loop through all columns and add an editor to each one, it should be something like

for (var i = 4; i <= sheets[3].getLastColumn(); i++){
    ///Get the username.
    var editor = sheets[3].getRange(1, i).getDisplayValue();

    //Set the protection.
    var protection = sheets[3].getRange(startRow, i, rowNumber, columnNumber).protect();
    protection.setDescription(editor);

    //Handle the case of deleted/unknown usernames.
    try{
      protection.addEditor(editor + '@domain.com');
    } catch(error){
      protection.addEditor('user@domain.com');
    }
  }

You're right, I got this eventually, but was too focused on the poor performance to notice this at first. Thanks anyway. — Karol Jędrzejczyk, Sep 02 '19 at 07:55

score 0 · Answer 2 · answered Aug 30 '19 at 11:50

0

Its possible to do batch processing.

But you'll have to use Advanced Google Services. Check out the Sheets Advanced service and the Sheets API documentation.

answered Aug 30 '19 at 11:50

TheAddonDepot

8,408
2
20
30

Is it possible to batch process range protections in Google Apps Script?

2 Answers2