else if string match loop google script (spreadsheet)

Question

I am working on a project where I designed a checkout button that automatically fill cells in a shared spreadsheet. I am having issues with the logic in a "else if" loop. I would like to have a UI that tells the user when the string was not matched. So far I was able to create the menu, prompt and matching functions for my spreadsheet, but I am stuck in the else if statement that returns the not found message.

if (button == ui.Button.OK) 
{

text = result.getResponseText().toUpperCase();

for(n=0;n<data.length;++n)
{ // iterate row by row and examine data in column A
  if(data[n][0].toString().match(text)==text)
  {
    data[n][13] = newDate;
    data[n][14] = newTime;
    data[n][15] = 'YES';
    data[n][16] = 'YES';
    //data[n][0] = data[n][0].setBackgroundColor('red');


    var result2 = ui2.prompt(
    'Checkout process in progess',
    'Please enter yout initials: (eg. MR)',
    ui2.ButtonSet.OK);
    var text2 = result2.getResponseText().toUpperCase();
    data[n][17] = text2;

  }  
Logger.log(data)
sh.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet

}

}

this is the code I wrote for the UI prompt, but when I use it inside the for loop it runs in every loop, not only when I reach the end of the row.

else if(data[n][0].toString().match(text) != text)
{
  ui.alert('room number not found; please fill in manually.'); 
  break;
}

any help will be highly appreciated, this is my first time coding in google apps, so far I really like it. please give me some ideas.

Serge insas Serge insas · Accepted Answer · 2014-06-27T17:17:00

It would have been easier if you had shown the whole code but I think you don't even need to use an else if statement, a simple else would be sufficient since you have only one condition on a single value.

Apart from that, you need to have another approach to set background colors : the array you use has only spreadsheet values, it is not a range object anymore... what I'd suggest would be to have a second array with backGroundColors and update the value is that array.

Code would be like this :

  var BgColors = sheet.getDataRange().getBackGroundColors();
  if (button == ui.Button.OK){
    text = result.getResponseText().toUpperCase();
    for(n=0;n<data.length;++n){
      // iterate row by row and examine data in column A
      if(data[n][0].toString().match(text)==text){
        data[n][13] = newDate;
        data[n][14] = newTime;
        data[n][15] = 'YES';
        data[n][16] = 'YES';
        BgColors[n][0] = '#ff0000';// update color
        var result2 = ui2.prompt(
          'Checkout process in progess',
          'Please enter yout initials: (eg. MR)',
          ui2.ButtonSet.OK);
        var text2 = result2.getResponseText().toUpperCase();
        data[n][17] = text2;
      }else{
        ui.alert('room number not found; please fill in manually.'); 
        break;
      }
      Logger.log(data)
      sh.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet
      sh.getRange(1,1,BgColors.length,BgColors[0].length).setBackGroundColors(BgColors); // write back to the sheet
    }
  }

else if string match loop google script (spreadsheet)

1 Answers