Create an array of values that were looped through

Question

I have a spreadsheet where each row in "MASTER" sheet is a task. It has checkboxes to check if it's done.

On another sheet, "Sin despachar", the data is filtered so that only the tasks which are not done show. There I have empty checkboxes to check when an incomplete task is completed.

I wrote a script so that when a button is pressed, it loops through the whole column in "Sin despachar" to see if checkboxes are checked, and for those that are, it changes the value in a master sheet.

I would like to have a confirmation pop up that mentions all the tasks it'll edit, but for that I need to somehow retrieve that list when the script runs, and I don't know how.

So what I need is to create an array or something else that contains the names of the tasks with the checkbox checked.

Does anyone know how to do this?

The task names are in column 4, and the checkboxes are in column 18.

Thanks in advance!!

function marcarEtiquetado() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sin despachar");
  var sheetRange = s.getDataRange();
  var sheetValues = sheetRange.getValues();

  for (var i=sheetRange.getLastRow()-1; i>1; i--) {
    // si valor en columna R (etiquetado) = TRUE
    if ( sheetValues[i][17] === true) {
        var checkboxRange = s.getRange(i, 18);
        var usuariosRange = s.getRange(i, 3)

        Logger.log(usuariosRange)
        var targetSheet = ss.getSheetByName("*MASTER*");
        var targetRow = sheetValues[i][1];
        var targetRange = targetSheet.getRange( targetRow, 16);
        var targetTS = targetSheet.getRange( targetRow, 17);

        checkboxRange.setValue(false)
        targetRange.setValue(true); 
        targetTS.setValue(new Date()).setNumberFormat("dd-mm-yy hh:mm")
      }   ;
     }

   s.getRange(3, 18, s.getLastRow()-2).setValue(false)

  } 

Answer 1

You use alerts to confirm for every checked box individually either and action shall be carried out.

Sample:

function marcarEtiquetado() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sin despachar");
  var sheetRange = s.getDataRange();
  var sheetValues = sheetRange.getValues();
  var ui = SpreadsheetApp.getUi();

  for (var i=sheetRange.getLastRow()-1; i>1; i--) {
    // si valor en columna R (etiquetado) = TRUE
    if ( sheetValues[i][17] === true) {
      var checkboxRange = s.getRange(i, 18);
      var usuariosRange = s.getRange(i, 3)
      var targetSheet = ss.getSheetByName("*MASTER*");
      var targetRow = sheetValues[i][1];
      var targetRange = targetSheet.getRange(targetRow, 16);
      var targetTS = targetSheet.getRange(targetRow, 17);

      var response = ui.alert('Do you want to set the timestamp for row ' + (i+1) + '?', ui.ButtonSet.YES_NO);

      // Process the user's response.
      if (response == ui.Button.YES) {
        checkboxRange.setValue(false)
        targetRange.setValue(true); 
        SpreadsheetApp.getActiveSpreadsheet().toast("Timestamp will be set for row " + (i+1));
        targetTS.setValue(new Date()).setNumberFormat("dd-mm-yy hh:mm")
      } else {
        SpreadsheetApp.getActiveSpreadsheet().toast("Timestamp will not be set for row " + (i+1));
      }

    }   ;
  }

  s.getRange(3, 18, s.getLastRow()-2).setValue(false)

}

If this is not what you wanted and you prefer to have a single confirmation dialog for all checked boxes, you can implement a help array and string as following:

function marcarEtiquetado2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Sin despachar");
  var sheetRange = s.getDataRange();
  var sheetValues = sheetRange.getValues();
  var ui = SpreadsheetApp.getUi();
  var rowKeeper = [];
  var rowArray = ['\n'];
  for (var i=sheetRange.getLastRow()-1; i>1; i--) {
    // si valor en columna R (etiquetado) = TRUE
    if ( sheetValues[i][17] === true) {
      rowKeeper.push(i);
      rowArray.push(i+1); 
    }   
  }
  for (k = 0; k < rowArray.length-4; k += 5) {
    rowArray[k+4] = rowArray[k+4] + "\n";
  }
  var rowString = rowArray.join(" ");
  Logger.log(rowString);
  var response = ui.alert('Do you want to set the timestamp for rows ' + rowString + '?', ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (response == ui.Button.YES) {
    for (var j = 0; j < rowKeeper.length; j++){
      var i = rowKeeper[j];  
      var checkboxRange = s.getRange(i, 18);
      var usuariosRange = s.getRange(i, 3)
      var targetSheet = ss.getSheetByName("*MASTER*");
      var targetRow = sheetValues[i][1];
      var targetRange = targetSheet.getRange(targetRow, 16);
      var targetTS = targetSheet.getRange(targetRow, 17);
      checkboxRange.setValue(false)
      targetRange.setValue(true); 
      targetTS.setValue(new Date()).setNumberFormat("dd-mm-yy hh:mm")
    }
  } else {
    SpreadsheetApp.getActiveSpreadsheet().toast("No action will be carried out");
  }  
  s.getRange(3, 18, s.getLastRow()-2).setValue(false) 
}