IssueTracker

Hello Seth,

Would you please provide a code sample and the input string for Utilities.formatDate?

Thanks,
Anton

I have set spreadsheet timezone, script timezone, and tried using both EST and GMT-5 in the Utilities.formatDate() function.   The date returned is always 1 day less than the date acquired from the spreadsheet (the first use of formatDate, acting on "today" works fine).   Here's the function code:

function longRangePlan() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var today = new Date();
  today = Utilities.formatDate(today, "EST", "MM/dd/yyyy");   //Strip off the time values we dont need
  var startRow = 2;                                           // First row of data to process
  var numRows = 82;                                           // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 35);   // Fetch the range of cells A2:AE61
  var data = dataRange.getValues();                           // Fetch values for each row in the Range.
  var fileName = Browser.inputBox("Save long range planning as:");                // Prompts the user for the file name
  if (fileName.length !== 0) {                                // Check that the file name entered wasn't empty


    var docNew = DocumentApp.create(fileName);                  // Create LRP document
    docNew.insertParagraph(0,"Long Range Planning - "+myName+" - "+today).setBold(true);  //Bold the title
    var myTable = docNew.insertTable(1);                           // Insert a table
    var tableRow = myTable.appendTableRow().setBold(true);
    tableRow.appendTableCell("Name");
    tableRow.appendTableCell("Meeting");
    tableRow.appendTableCell("Date");
    tableRow.appendTableCell("Time");  
    for (i in data) {                                          //And prepare to populate the doc
      var row = data[i];
      var day = new Date(row[12]);
      var sixMonth = Utilities.formatDate(day, "EST", "MM/dd/yyyy");          //Strip off the time values we dont need
      var day1 = new Date(row[14]);
      var annualReview = Utilities.formatDate(day1, "EST", "MM/dd/yyyy");     //Strip off the time values we dont need
      var day2 = new Date(row[16]);
      var twentyfourMonth = Utilities.formatDate(day2, "EST", "MM/dd/yyyy");  //Strip off the time values we dont need
      var day3 = new Date(row[18]);
      var TPM = Utilities.formatDate(day3, "EST", "MM/dd/yyyy");              //Strip off the time values we dont need
      if (row[25] >= 0 && row[25] <= 270){                                    //If there's a 6 month between now and 9 months...
        var tableRow = myTable.appendTableRow().setBold(false);               //populate doc
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("6 Month Review");
        tableRow.appendTableCell(sixMonth);
        tableRow.appendTableCell(row[13]);
      }    
      if (row[26] >= 0 && row[26] <= 270){
        var tableRow = myTable.appendTableRow().setBold(false);
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("Annual Review");
        tableRow.appendTableCell(annualReview);
        tableRow.appendTableCell(row[15]);
      }  
      if (row[27] >= 0 && row[27] <= 270){
        var tableRow = myTable.appendTableRow().setBold(false);
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("24 Month Review");
        tableRow.appendTableCell(twentyfourMonth);
        tableRow.appendTableCell(row[17]);
      }      
      if (row[28] >= 0 && row[28] <= 270){
        var tableRow = myTable.appendTableRow().setBold(false);
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("TPM");
        tableRow.appendTableCell(TPM);
        tableRow.appendTableCell(row[19]);
      }  
    }
      for (i in data){
      var row = data[i];
      if (row[12]== "" && row[14] == "" && row[16] =="" && row[18] == "" && row[24]<=1095 && row[23] != 1){   //If things need to be
       docNew.appendParagraph(row[4]+" "+row[5]+" appears to have no meetings scheduled.");                   //scheduled, add info
      }
      if (row[26]<= 0 && row[25] <= 0 && row[25] < row[26] && row[29] >= 180 && row[29]<=1094){
         docNew.appendParagraph(row[4]+" "+row[5]+" needs a 6 month review scheduled.");
      }
      if (row[25]<= 0 && row[26] <= 0 && row[25] > row[26] && row[30] >= 180 && row[30]<=1094){
        docNew.appendParagraph(row[4]+" "+row[5]+" needs an annual review scheduled.");
      }        
      if (row[27]<=0 && row[24]>=517 && row[24]<=730){
        docNew.appendParagraph(row[4]+" "+row[5]+" needs a 24 month meeting scheduled.");
      }    
      if (row[22] == 0 && row[18] == "" && row[28]<=0 && row[24]>=700 && row[24]<=1095){                                           // row 23 is chron age in days
        docNew.appendParagraph(row[4]+" "+row[5]+" needs a TPM scheduled.");
      }
      if (row[22]==1 && row[24]<=1460 && row[26]<= 0 && row[25] <= 0 && row[25] < row[26]&& row[29] >= -185 && row[29]<=1094){
         docNew.appendParagraph(row[4]+" "+row[5]+" needs a 6 month review scheduled.");
      }
      if (row[22]==1 && row[24]<=1460 && row[26]<= 0 && row[25] <= 0 && row[25] > row[26]&& row[30] >= -185 && row[30]<=1094){
         docNew.appendParagraph(row[4]+" "+row[5]+" needs an annual review scheduled.");
      }
    }
  }
  else {
    Browser.msgBox("Error: Please enter a valid file name.");                 //If the user didn't put in a filename,
  }                                                                           //throw an exception
  docNew.saveAndClose();
}