Google Script send email from a sheet in a specific date

Question

follow script is used to get a date from a google sheet and if this date is equal to today or tomorrow generate an automatic email to my address in order to remind me.

function getVal() {


var ss = SpreadsheetApp.getActiveSpreadsheet(),
       sheet = ss.getActiveSheet();
   sheet.setActiveRange(sheet.getRange("A1")); 
   var range = sheet.getDataRange(),
       formulas = range.getValues();
   var toDay = new Date();
   for (var r=0; r<formulas.length; r++) {
       for (var c=0; c<formulas[r].length; c++) {    
          //var value = sheet.getRange(r,c).getValue();
          var value = range.getCell(r, c).getValue();
          Logger.log(value);  
          if (value == "AAAA")
          {
              var index = r+2;
              value = sheet.getRange(index,c).getValue();
              while (value != "" || index >= formulas.length)
              {
                  if (DateDiff.inDays(value,toDay)==1 || DateDiff.inDays(value,toDay)==0)
                  {
                      MailApp.sendEmail(myAdress,subject, text);
                  }
                  index = index + 3;
                  value = sheet.getRange(index,c).getValue();

              } 
          }
       }
   }
}

var DateDiff = {    
    inDays: function(d1, d2) {
        var t2 = d2.getTime();
        var t1 = d1.getTime();

        return parseInt((t2-t1)/(24*3600*1000));
    },
    inWeeks: function(d1, d2) {
        var t2 = d2.getTime();
        var t1 = d1.getTime();

        return parseInt((t2-t1)/(24*3600*1000*7));
    },
    inMonths: function(d1, d2) {
        var d1Y = d1.getFullYear();
        var d2Y = d2.getFullYear();
        var d1M = d1.getMonth();
        var d2M = d2.getMonth();

        return (d2M+12*d2Y)-(d1M+12*d1Y);
    },
    inYears: function(d1, d2) {
        return d2.getFullYear()-d1.getFullYear();
    }
}

when i run the script it fail in the line : var value = range.getCell(r, c).getValue();

have you any suggestion in order to fix this bug?

thanks

Mario

Harold Harold · Accepted Answer · 2014-09-30T14:54:59

try to change the line with:

var value = formulas[r][c]

it should also do less request to google spreadsheet and run faster.

Google Script send email from a sheet in a specific date

1 Answers