Check a checkbox if a column contain an specific text on Google sheets

Elmar Onassis 0
Viewed 38

I hope someone can help me with this.

I want to achieve the following condition: When column B contains "10:00 PM" set columns "M" and "N" as true (checkbox).

I tried this code:

   function sutenn() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Status')
  var rg = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn());
  const vs = rg.getDisplayValues();//this was they key this returns a string not a date
  let o =vs.map((r, i) => {
    if ((r[1] == "10:00 PM" && (r[6] == "")) || ((r[2] == "Sunday") || (r[3] == "Sunday" ))) {
      r[12] = true;
    } else {
      r[12] = false
    }
    return [r[12]];
  });
  
  sh.getRange(2,13,o.length, o[0].length).setValues(o);//setting the checkboxes
}

But it didn't work. Does someone know what should I do to achieve this condition?

conditional-statements if-statement google-sheets google-apps-script
1 Answers

Try it this way:

function sutenn() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet0')
  var rg = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn());
  const vs = rg.getDisplayValues();//this was they key this returns a string not a date
  let o =vs.map((r, i) => {
    if (r[1] == "10:00 PM") {
      r[12] = true;
    } else {
      r[12] = false
    }
    return [r[12]];
  });
  sh.getRange(2,13,o.length, o[0].length).setValues(o);//setting the checkboxes
}

I just generated some test data and added some checkboxes and this is what it looked like after running

enter image description here

I rewrote it in a different way so that you can hopefully achieve the additional conditions that you have not articulated very well on you own.

function ltesto() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getDisplayValues();
  let o = vs.map((r, i) => {
    let [A, B, C, D, E, F, G, H, I, J, K, L, M, N] = r;
    if (B == "10:00 PM") {
      M = true;
    } else if (G == '' && C == "Sunday" && D == "Sunday") {
      M = true;
    } else {
      M = false;
    }
    return [M];
  });
  sh.getRange(2, 13, o.length, o[0].length).setValues(o);
}

If you wish to add addtional logical conditions the please suppy me with a truth table.

user30hu0
Related