Avoid inserting checkbox into empty cells in forEach loop - Google apps script

I have a list of ranges where depending on the cell value, a checked checkbox (Completada) or an unchecked checkbox (No completada) should be inserted.

The problem I have is that the script places checkbox in the entire list of ranges, it does not ignore empty cells or cells that do not contain those specific values.

It's probably a very slight problem, but I can't fix it.

Here is a picture of what I need: ibb.co/Fq9ssp8

This is my actual code:

function checkboxVistaMensual_comparacion() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName('Calendario');  // Hoja Destino

  var rangesCheckbox = targetSheet.getRangeList(['F6:F98', 'I6:I98', 'L6:L98', 'O6:O98', 'R6:R98', 'U6:U98', 'X6:X98']);
  var rangesCheckboxVal = rangesCheckbox.getRanges();
  
  var c = 'Completada';
  var nc = 'No completada';

  var valores = []
  rangesCheckboxVal.forEach((row)=> {

    if ( row.valueType == SpreadsheetApp.ValueType.CHECKBOX ) return;
    if ( row == '' ) return;

    if ( row == c ){
      valores.push(c);
    } else {
      if ( row == nc ){
      valores.push(nc);
      }
    }

  })  

  rangesCheckbox.insertCheckboxes(c, nc);
}

Modification points:

• I think that in your script, no processes are done in the forEach loop. I think that this is the reason for your current issue of the script places checkbox in the entire list of ranges,.
• I think that in your situation, if you want to insert the checkboxes to the cells of ['F6:F98', 'I6:I98', 'L6:L98', 'O6:O98', 'R6:R98', 'U6:U98', 'X6:X98'] by skipping the cells with the checkbox, "Completada", "No completada" and the empty, the maijor change is required to be done.
• And, in your situation, if the checkbox is put into each cell using RangeList, the process cost becomes high. So, I thought that it might be required to also modify this situation.

When these points are reflected in your script, how about the following modification?

Modified script:

function checkboxVistaMensual_comparacion() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName('Calendario');
  var rangesCheckbox = targetSheet.getRangeList(['F6:F98', 'I6:I98', 'L6:L98', 'O6:O98', 'R6:R98', 'U6:U98', 'X6:X98']);
  var rangesCheckboxVal = rangesCheckbox.getRanges();
  var c = 'Completada';
  var nc = 'No completada';
  rangesCheckboxVal.forEach(r => {
    var values = r.getValues();
    var dataValidations = r.getDataValidations();
    var { dataValidation, values } = values.reduce((o, [v], i) => {
      if (v.toString() != "" && ![c, nc].includes(v) && !(dataValidations[i][0] && dataValidations[i][0].getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX)) {
        o.dataValidation.push([SpreadsheetApp.newDataValidation().requireCheckbox(c, nc).build()]);
        o.values.push([null]);
      } else if (dataValidations[i][0] && dataValidations[i][0].getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
        o.dataValidation.push([dataValidations[i][0]]);
        o.values.push([v]);
      } else {
        o.dataValidation.push([null]);
        o.values.push([v]);
      }
      return o;
    }, { dataValidation: [], values: [] });
    r.setValues(values).setDataValidations(dataValidation);
  });
}

• In this script, about the cells of ['F6:F98', 'I6:I98', 'L6:L98', 'O6:O98', 'R6:R98', 'U6:U98', 'X6:X98'], the checkboxes are put into the cells which have not empty, no values of "Completada" and "No completada", and no checkbox.

• In order to reduce the process cost, in this case, the checkboxes are inserted using setDataValidations.

Note:

• If you want to change the skipped values, please modify ![c, nc].includes(v).

References:

• Class DataValidationBuilder
• setDataValidations(rules)

Added:

From your following reply,

> It doesn't work for me either. The code itself is fine, however what I need is that the cells that contain the value "Completado" become checked checkboxes. Cells containing "No completado" become unchecked checkbox. Cells that are empty or have another value, do not insert checkcbox. With your current code, it does nothing.

> And if I remove the phrase indicating ![c, nc].includes(v), then checkboxes are inserted in the cells with "Completada" and "No completada" value, however, the cells with "Completada" value do not stay checked. Here is a picture of what I need: ibb.co/Fq9ssp8

How about the following sample script?

In your script, you use Completada and No completada. But in your reply, Completado and No completado are used. In this modification, Completada and No completada are used. Please be careful about this.

Sample script:

function checkboxVistaMensual_comparacion() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName('Calendario');
  var rangesCheckbox = targetSheet.getRangeList(['F6:F98', 'I6:I98', 'L6:L98', 'O6:O98', 'R6:R98', 'U6:U98', 'X6:X98']);
  var rangesCheckboxVal = rangesCheckbox.getRanges();
  var c = 'Completada';
  var nc = 'No completada';
  rangesCheckboxVal.forEach(r => {
    var values = r.getValues();
    var dataValidation = values.map(([v]) => [[c, nc].includes(v) ? SpreadsheetApp.newDataValidation().requireCheckbox(c, nc).build() : null]);
    r.setDataValidations(dataValidation);
  });
}

• By this, the cells with Completada and No completada from ['F6:F98', 'I6:I98', 'L6:L98', 'O6:O98', 'R6:R98', 'U6:U98', 'X6:X98'] are converted to the checkboxes.