CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
Rheago
Rheago

Reputation: 99

Bring the found data into the message box

When I open the sheet, I use the following codes to give an alert when the product reaches the critical limit the day before it goes on sale. For example: If "YES" is written in the cells "H4: H100" on the "CALENDAR" page, it brings the data in the "A" column in the line that says "YES" and it shows it as the individual message box.

      A Column       G Column   H Column
4      ASUS              7         YES
5       HP               8
6      DELL              5         YES
7     LENOVO             9
8      MSI               10
...

Script in the Scenario:     Browser.msgbox("ASUS product has 7 days to go on sale.");
                            Browser.msgbox("DELL product has 5 days to go on sale.");

The result I want it to be: Browser.msgbox("ASUS product has 7 days to go on sale. \\nDELL product has 5 days to go on sale.");

How can I add the found data to a single message box?

Thank you.

function alarm(findArray) {
  var findArray=findArray||["YES"];//default



  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('CALENDAR');
  var hA2=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
  var condition=ss.getSheetByName('CALENDAR').getRange('H4:H100').getValues();//default
  var CALENDARsheet = ss.getSheetByName('CALENDAR')
  var hA = [].concat.apply([], condition).filter(String);
  var cObj={};
  for(var i=0;i<hA.length;i++) {
    cObj[String(hA[i]).toLowerCase()]=i+1;
  }
  var srg=sh.getRange(2,8,sh.getLastRow()-1,1);
  for(var i=0;i<findArray.length;i++) {
    var f=srg.createTextFinder(String(findArray[i])).matchCase(true).findAll();
    var d=0;
    if(f.length>0) {
      for(var j=0;j<f.length;j++) {
       // sh.deleteRow(f[j].getRow()-d++);
        Browser.msgBox('ALERT ALERT', CALENDARsheet.getRange(f[j].getRow()-d,1).getValue() + '  product has  ' + CALENDARsheet.getRange(f[j].getRow()-d,7).getValue()  + ' days to go on sale.', Browser.Buttons.OK);

      }
    }
  }  
}

Upvotes: 0

Views: 575

Answers (1)

Iamblichus
Iamblichus

Reputation: 19319

You need to first retrieve the messages for all YES products and, after you've retrieved them all, call Browser.msgBox.

Method 1. Array join:

You can use an array that contains the messages for each product, and call msgBox after finishing the for loop, providing the string returned by joining the array with Array.prototype.join():

  var messages = [];
  for(var i=0;i<findArray.length;i++) {
    var f=srg.createTextFinder(String(findArray[i])).matchCase(true).findAll();
    var d=0;
    if(f.length>0) {
      for(var j=0;j<f.length;j++) {
        messages.push(CALENDARsheet.getRange(f[j].getRow()-d,1).getValue() + '  product has  ' + CALENDARsheet.getRange(f[j].getRow()-d,7).getValue()  + ' days to go on sale.');        
      }
    }
  }
  Browser.msgBox('ALERT ALERT', messages.join("\\n"), Browser.Buttons.OK);

Method 2. String concat:

Another option would be to concatenate all messages in a single string with String.prototype.concat(); define message as an empty string before your outer loop and replace your inner loop code with this:

message = message.concat("\\n", CALENDARsheet.getRange(f[j].getRow()-d,1).getValue() + '  product has  ' + CALENDARsheet.getRange(f[j].getRow()-d,7).getValue()  + ' days to go on sale.');

And, also after the for loop, call msgBox:

Browser.msgBox('ALERT ALERT', message, Browser.Buttons.OK);

Note:

I'd suggest you to try reworking your code a bit, it could be greatly simplified.

Upvotes: 2

Related Questions