CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Laissez Faire
Laissez Faire

Reputation: 101

Message Box if Cell Range is Today

I'm new to Google App Script and new to coding for Google Sheets. I'm attempting to:

Have a pop-up box show if any date in a range of cells is equal to today.

Here's the code I have so far:

function onOpen() 
{
var ss = SpreadsheetApp.getActiveSheet();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Required Items List');
var ss = SpreadsheetApp.getActive();
var range = sheet.getRange('Required Items List!E8:G22');
var data = range.getValue();
var today = new Date();

    Logger.log(data);
    Logger.log(today);

      if (data == today) 
      { 
        Browser.msgBox('Send Required Items Reminders Today!', Browser.Buttons.OK);
      }

    else {}
}

Range Example

It appears by data variable on reads the first cell for E8. I'm sure its a simple solution, I'm just missing some logic for it. Any help would be greatly appreciated.

Upvotes: 0

Views: 2424

Answers (1)

YNK
YNK

Reputation: 129

You may code something like this :

function onOpen(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastrow = ss.getLastRow();
  var lastcol = ss.getLastColumn();
  var today = Utilities.formatDate(new Date(), "GMT+05:30", "''yyyy-MM-dd");
  var flag = 'false';
  for(var i =8; i<=22; i++){
    for(var j =5; j<=7; j++){
      var data = sheet.getRange(i, j, i, j).getValue();
      var shDate = Utilities.formatDate(new Date(data), "GMT+05:30", "''yyyy-MM-dd");
      if (shDate == today){
        Browser.msgBox('Send Required Items Reminders Today!', Browser.Buttons.OK);
        flag = 'true';}
        if(flag == 'true')
          break;
    }
    if(flag == 'true')
      break;
  }
}

Hope this will help you.

Thanks.

Upvotes: 1

Related Questions