Scan column row by row, if there is a checkbox ignore it, if there is no checkbox then continue the function - Google apps script

Question

I have 5 columns where the values ​​are imported from another service (values ​​is 1 or 0).

So what my function does is that it filters if each cell of each column has the value "1", then it converts it to "yes". If it has a value of "0", then it converts it to a value of "no".

After this, a checked or unchecked checkbox is created depending on the value "yes" or "no".

Now this function is executed every time I open the google sheet document. And this is where I have the error.

Every time the document is opened, it goes through all the rows of all the columns again, and it does not ignore if the cells already have a checkbox, therefore the checkboxes are created again and they are all completely unchecked ignoring the previous value rule "yes" or "no".

So I need to create a cell validation on said columns so that it checks first if there is a checkbox in the cell. If there is a checkbox, do nothing and move on to the next cell. If there is no checkbox, then apply the rules and create the checkbox (checked or unchecked according to the previous rules).

I am a total beginner. I would appreciate a little help please.

This is my current code:

function hechoCheckbox() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Todos los eventos');
  const lastRow = sheet.getLastRow();

  let rango = sheet.getRange(2, 14, lastRow -1, 5);
  let rangoValues = rango.getValues();

  rangoValues.forEach((fila)=> {

    // semana pasada
    if (fila[0] == 1) {
      fila[0] = 'yes'
    } else {
      fila[0] = 'no';
    }

    // semana actual
    if (fila[1] == 1) {
      fila[1] = 'yes'
    } else {
      fila[1] = 'no';
    }

    // proxima semana
    if (fila[2] == 1) {
      fila[2] = 'yes'
    } else {
      fila[2] = 'no';
    }

    // mes actual
    if (fila[3] == 1) {
      fila[3] = 'yes'
    } else {
      fila[3] = 'no';
    }

    // trimestre actual
    if (fila[4] == 1) {
      fila[4] = 'yes'
    } else {
      fila[4] = 'no';
    }

  })

  rango.setValues(rangoValues);
  rango.insertCheckboxes('yes');
}

Answer 1

In your script, as a simple modification, how about adding a condition for checking the cell value is yes as follows?

Modified script:

function hechoCheckbox() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Todos los eventos');
  const lastRow = sheet.getLastRow();
  let rango = sheet.getRange(2, 14, lastRow - 1, 5);
  let rangoValues = rango.getValues();
  rangoValues.forEach((fila) => {
    // semana pasada
    if (fila[0] == 1 || fila[0] == 'yes') {
      fila[0] = 'yes'
    } else {
      fila[0] = 'no';
    }
    // semana actual
    if (fila[1] == 1 || fila[1] == 'yes') {
      fila[1] = 'yes'
    } else {
      fila[1] = 'no';
    }
    // proxima semana
    if (fila[2] == 1 || fila[2] == 'yes') {
      fila[2] = 'yes'
    } else {
      fila[2] = 'no';
    }
    // mes actual
    if (fila[3] == 1 || fila[3] == 'yes') {
      fila[3] = 'yes'
    } else {
      fila[3] = 'no';
    }
    // trimestre actual
    if (fila[4] == 1 || fila[4] == 'yes') {
      fila[4] = 'yes'
    } else {
      fila[4] = 'no';
    }
  })
  rango.setValues(rangoValues);
  rango.insertCheckboxes('yes');
}

• As another approach, I thought that in your situation, the forEach can be also modified as follows.

  function hechoCheckbox() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Todos los eventos');
    const lastRow = sheet.getLastRow();
    let rango = sheet.getRange(2, 14, lastRow - 1, 5);
    let rangoValues = rango.getValues().map(r => r.map(c => [1, "yes"].includes(c) ? "yes" : "no"));
    rango.setValues(rangoValues);
    rango.insertCheckboxes('yes'); //  or rango.insertCheckboxes('yes', 'no'); 
  }