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();
}

Hello Seth,

I have done some testing on your issue.
It appears that the formatDate works as expected BUT
the values are read incorrectly from the spreadsheet.

If your spreadsheet says 1/25/2012, they value will be read as 1/24/2012.

A temporary workaround would be to provide the time as well as the date.
For example, instead of "1/25/2012" you can write "1/25/2012 12:00pm".
If you do this the value will be read correctly and you can use formatDate to get rid of the time.

In the mean time will investigate the reason why this is happening, and provide a fix for it.

Best,
Anton

I appreciate the temporary workaround, but it's not feasible for me.   I have this script distributed across my organization and most of the users are not technically savvy, nor are they going to be understanding of the idea that dates are not correctly supported in spreadsheets.   No offense, but I'm already getting a lot of grief over this issue.

Can you give me some sort of idea of when this might be corrected?

Thank you.

Seth,

Unfortunately I can't give you an ETA yet, but I will try to get more info to you as soon as it is available.

Best,
Anton