javascriptgoogle-sheetsgoogle-apps-script

Protect a range once all the words are created so noone can edit them


In Apps Script I made a code in which if a checkbox in cell C5 is true, then creates some words in the range C12:N41. That part of the code works perfectly, but what I want to do is to protect that range once all the words are created so noone can edit them. I added that code but it's not working. My code of the protection part is:

    function createTable() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cellC4 = sheet.getRange('C4');

   if (cellC4.getValue() === true) { 
    tablaData = [
    [One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
    [One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
    [One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
    [One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
    [One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve],
    [One,Two,Three,Four,Five,Six,false,Eight,Nine,Ten,false,Twelve]
    ];
    
    var startCell = sheet.getRange('C12');
    
        for (var i = 0; i < tablaData.length; i++) {
          startCell.offset(i, 0, 1, tablaData[i].length).setValues([tablaData[i]]);
          for (var j = 0; j < tablaData[i].length; j++) {
            if (tablaData[i][j] === false) {
              startCell.offset(i, j).insertCheckboxes();
            }
          }
        }
    
        var protection = sheet.getRange('C12:N41').protect();
        
        protection.setDescription('Protection of C12:N41');
    }

Solution

  • Modification points:

    When these points are reflected in your script, it becomes as follows.

    Modified script:

    In this modification, it supposes that the values of tablaData are valid values. Please be careful about this.

    function createTable() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var cell = sheet.getRange('C5');
      if (cell.isChecked()) {
    
        tablaData = [
          [One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
          [One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
          [One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
          [One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
          [One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve],
          [One, Two, Three, Four, Five, Six, false, Eight, Nine, Ten, false, Twelve]
        ];
    
        sheet.getRange(12, 3, tablaData.length, tablaData[0].length).setValues(tablaData);
        const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : ""; // ref: https://stackoverflow.com/a/53678158
        const offsetR = 12;
        const offsetC = 3;
        const rangeList = tablaData.reduce((ar, r, i) => {
          r.forEach((c, j) => {
            if (c === false) {
              ar.push(`${columnIndexToLetter_(j + offsetC - 1)}${i + offsetR}`);
            }
          });
          return ar;
        }, []);
        if (rangeList.length > 0) {
          sheet.getRangeList(rangeList).insertCheckboxes(rangeList);
        }
        var protection = sheet.getRange('C12:N41').protect();
        protection.setDescription('Protection of C12:N41');
        protection.addEditor(Session.getEffectiveUser()); // This line might not be required to be used.
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
      }
    }
    

    Note:

    References: