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

Thank you.

Any updates?

Thank you.

Here is a similar Javascript workaround I used. 'toopid Chrome!

var enteredDate = new Date(document.getElementById('date').value + " 12:00:00");

Looks to be fixed in v1.10.5; the issue on our end was if we tried to do any reverse geocoding while in the background or on any thread other than the main/UI thread.

It was originally instantiating some OpenGL cache in the background in order to do it, but now it seems to handle pausing rendering properly while in the background while allowing the reverse geocoding to still happen.

Thanks to Brett and the rest of the maps/geo team for patiently squashing this one.

Has this been corrected yet?

I'm doing my best to be patient; however having a spreadsheet program which cannot handle dates correctly is ludicrous.

I cannot, nor should I have to, use any of these workarounds.