Looking to set a checkbox value to TRUE in a Google Sheet based on the value of another column

user_13083362 0
Viewed 247

Hello StackOverflow Community,

I'm currently looking to create a formula that I can execute via a weekly trigger in Google Sheets.

I have a formula that works for 1 row, and I'm trying to get it to work over a range of rows.

Here is the current formula:

function SetPHCheckboxesToActive(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  if (sheet.getRange('A7').getValue() == "Entered") {
    sheet.getRange('C7').setValue('TRUE');
  } else {
    sheet.getRange('C7').setValue('FALSE'); 
  }

}

This essentially looks at the value in Column A, and if that value equals "Entered", then the checkbox in Column C will be checked. Otherwise it will remain unchecked.

What I'm looking to do here is, execute this same function over 35 rows (the range of Column A values in A7:A41) and then update the corresponding row in the checkbox column (C7:C41).

I suspect I will need to use some sort of array function to accomplish this, but unfortunately I was not skilled enough to get that to work for me.

I appreciate any input or guidance on this.

Thank you, Mike C

google-apps-script google-sheets
2 Answers

Explanation:

  • You can use getValues and setValues to set and get multiple values.
  • You can map and a ternary operator to make the code shorter (you get rid of for loops and ifs.

Solution:

function SetPHCheckboxesToActive(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];  
  const input = sheet.getRange('A7:A41').getValues();
  const output = input.map(([r])=>[r=="Entered"?true:false]);
  sheet.getRange('C7:C41').setValues(output);
}
user_112252910

Set the data validation for your checkboxes to use custom cell values, 1 and 0

enter image description here

Then change your script as follows:

function SetPHCheckboxesToActive(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheet = ss.getSheets()[0];
  var sheet = ss.getActiveSheet();
  if (sheet.getRange('A7').getValue() == "Entered") {
    sheet.getRange('C7').setValue(1);
  } else {
    sheet.getRange('C7').setValue(0); 
  }
}

My testing shows it works as expected

enter image description here

user_89418940
Related