4

My function works well for one row of my sheet (row 4).
But I want to check all the rows in columns 2 and 7 where checkboxes are.
I thought about a loop but I would like to use arrays for a better and more efficient code.

ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getSheetByName("Feuille 1");

function alertbox(){
  var box1= sheet.getRange(4, 2).getValue();
  var box2 =sheet.getRange(4, 7).getValue();

  if (box1 ==true && box2 == true) {
    var activcell = sheet.getActiveCell();
    showAlert(activcell);
  }

};
function showAlert(activcell) {
  var ui = SpreadsheetApp.getUi();

  var result = ui.alert(
     'Conflit entre un autre hôtel qui a réservé aux même dates que vous !',
     'Vous êtes sûr de continuer ?',
      ui.ButtonSet.YES_NO);

  // utiliser les réponses des utilisateurs
  if (result == ui.Button.YES) {
    //  clicked "Yes".
    ui.alert('Voyez avec l autre hôtel !');
  } else {
    // User clicked "No" or "X".
    ui.alert('Ok on enlève votre choix...');
    activcell.setValue(false);
  }
}
jean74
  • 81
  • 4

2 Answers2

0

There are a couple of ways to build the array...

1st way would be to get the last row of the sheet and use that in the getRange method

var lastRow = sheet.getLastRow();    
var box1 = sheet.getRange(1, 2, lastRow).getValues();

2nd option would be to use the a1Notation in getRange and remove blanks with the array.filter

var box1 = sheet.getRange("B:B").getValues().filter(String);

I would suggest using the first so that you know that your array will be the same length.

After you get your 2 arrays I would suggest using a for loop to check box1 and box2

for(var a = 0; a <= box1.length; a++){
   if(box1[a] == true && box2[a] == true){
      var activcell = sheet.getActiveCell(); //Switch from getActiveCell() to maybe a box3 to identify what you are trying to alert
      showAlert(activcell);
   }
}

Here are some resources

wjwelch1
  • 79
  • 1
  • 12
0

I tried the loop you propose but i did some corrections :

  • because the values are a 2d array:if (box1[r][0] == true && box2[r][0] == true){

  • because the loop beginning at 0 i put minus one to the length : for (var r=0; r <= box1.length - 1; r++){

but now it works fine and many thanks to your help.

jean74
  • 81
  • 4