A string of form nnnn-nnnn is displayed in a spreadsheet as a date or otherwise incorrectly

Question

I have a script I have been using in my test environment to programmically create a tracking number by parsing the year from timestamp and padding the response index.

function setTrackingNumber(ss, lastRowInx, createDateColumn) //This block generates and stores a tracking number in Column AU on the backend
{
  var padTrackNo = "" + lastRowInx;
  var trackSize =  4;
  var trackingNumberColumn = createDateColumn-3; //trackingNumberColumn is currently in AU (Column 47) Calculating using it's relative position to createDateColumn Position

  if (ss.getRange(lastRowInx, trackingNumberColumn).getValue() == "") // so that subsequent edits to Google Form don't overwrite original tracking number
  {
    if (padTrackNo > trackSize)
    {
      var padTrackNo = pad(padTrackNo, trackSize);
    }
    else {} //do nothing

    var shortYear = setShortYear(ss, lastRowInx, createDateColumn);
    var trackingNumber = shortYear + "-" + padTrackNo;
    var createTrackingNumber = ss.getRange(lastRowInx, trackingNumberColumn);
    createTrackingNumber.setValue(trackingNumber);
   }
  else {} //should do nothing
  return;
}//This is the end of the setTrackingNumber function

function setShortYear(ss, lastRowInx, createDateColumn)
{
  var newCreateDate = ss.getRange(lastRowInx,createDateColumn).getValue();
  var strDate = "" + newCreateDate;
  var splitDate = strDate.split(" "); 
  var trimYear = splitDate[3];
  var shortYear = trimYear;

  return shortYear;
}//This is the end of the shortYear function

function pad(padTrackNo, trackSize)
{
   while (padTrackNo.length < trackSize)
    {
      padTrackNo = "0"+padTrackNo;
    }
  return padTrackNo;
}//This is the end of pad function

That gets me test result which is as expected ex. 2016-0005. However when we added it to another production sheet it seemed to work with test data and then production data showed up like a date 3/1/2016. production result - first cell.

I thought it must just be formatting the string as a date because of the numbers so I tried formatted the column as plain text but that just changed the date to a plain text version of the date.

I thought this might be similar to needing to specify the format like I did in this question Appending initial timestamp from Google Form to end of record in order to permanently store create date onFormSubmit at @SandyGood 's suggestion so I tried setting the number format as [0000-0000] by changing

createTrackingNumber.setValue(trackingNumber);

to

createTrackingNumber.setValue(trackingNumber).setNumberFormat("0000-0000");

which resulted in the [production result - second cell] which again doesn't match the expected result.

Oddly, some submissions seem to work just fine like [production result - third cell]. Over the past 3 days and approximately 10 records it has been fine, then hinky, then fine, they hinky, then fine again. I am not really sure what else to try to debug this odd behaviour.

Note: I had to parse the date as a string as I was having trouble getting it to parse the date correctly from the create date which is taken from initial timestamp.

score 0 · Accepted Answer · edited May 23 '17 at 12:32

To my understanding, "2016-0005" is not a number but a string, so the cell containing it should be formatted as plain text. With a script, this can be done by

range.setNumberFormat('@STRING@')

(source), and this must be done before you set the value to the cell. Like this:

createTrackingNumber.setNumberFormat('@STRING@').setValue(trackingNumber);

A string of form nnnn-nnnn is displayed in a spreadsheet as a date or otherwise incorrectly

1 Answers1