google sheet — protect sheet and unprotect all non-formulas within with google apps script

0
votes

I'm new to Google Apps Script and trying to write a script to perform the following:

  1. Protect Active sheet, then within a specified Range on that sheet:
  2. Determine all "Formula cells/ranges" (and therefore the "non-Formula cells/ranges"), then
  3. Unprotect the non-Formula Range[]

Here's what I'm currently working with (pls refer to the section where I'm stuck)

function protectFormulas() {

// DEFINE WORKSHEET
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getActiveSheet();
  
// PROTECT ACTIVE SHEET
    var protection = ws.protect().setDescription('Protect Fomulas');
  
// Establish Range and Formulas within [currently my range is the entire sheet]
    var range = ws.getDataRange();
    var formulas = range.getFormulas();  
    
// *****THIS IS WHERE I'M STUCK - how to determine the Range[] Array within "Formulas" ??  [aka ignore the notes]
    //var result = new Array(formulas.length)
    //for (var i = 0; i < formulas.length; i++) {
        //IF formulas [i] is blank
            //add formulas R1C1 to Result Array [how to?]
        //ELSE
            // do nothing
    //}
//  *****END OF WHERE I'M STUCK


// UNPROTECT select Ranges  
  var unprotected = ws.range;
  protection.setUnprotectedRanges(result);

  }

Please help!

1
google-apps-scriptgoogle-sheetsrange

1 Answers

2
votes
function protectFormulas() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  let up = [];
  let protection=sh.protect();
  const range = sh.getDataRange();
  const formulas = range.getFormulas();
  formulas.forEach((r, i) => {
    r.forEach((c, j) => {
      if (c == '') {//if not a formula then add range to up
         up.push(sh.getRange(i+1,j+1));
      }
    });
  });
  protection.setUnprotectedRanges(up);//unprotect ranges in up
}