CODEWITHSUNDEEP
dategoogle-apps-scriptgoogle-sheetsformat
SorryI8YourPi
SorryI8YourPi

Reputation: 77

In Google Apps Script, my date is switching from a normal format to what seems like an id number

I am using google apps script for a google sheet that is meant to sort ranges of cells into different sheets by date. I've entered a date in the cell A2, and then when I use my function, it sorts the sheet with the same date as what is in A2, and if there isn't one, then it will create a new sheet with that date. The problem that I am coming across is not with the cells getting to the right place, but for some reason the date changes to what seems to be an id of sorts. For example, for the date 01/01/2001, the first couple worked fine, and then for the next couple attempts it switched to have "36892" where the date should be.

I have not tried anything, as I am unsure of what to try. If I knew the command, I would just switch the format back after moving the cells to the right place.

function findDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  ss.setActiveSheet(ss.getSheets()[0])
  var range = sheet.getRange("A2");
  var A2 = SpreadsheetApp.getActiveSheet().getRange('A2').getValue();
  var templateSheet = ss.getActiveSheet();  
  var val = Utilities.formatDate(new Date(A2), "GMT+1", "MM/dd/yyyy");
  range.setValue(new Date(A2)).setNumberFormat("MM/dd/yyyy");
  A2 = SpreadsheetApp.getActiveSheet().getRange('A2').getValue();
  if( ss.getSheetByName(val) == null)
  {
    //if returned null means the sheet doesn't exist, so create it
    ss.insertSheet(val, ss.getSheets().length, {template: templateSheet});
  }
  else
  {
    var sheet1 = ss.getSheetByName("GUI");
    var sheet2 = ss.getSheetByName(val);
    sheet1.getRange("A2:D2").copyTo(sheet2.getRange(sheet2.getLastRow()+1,1,1,4), {contentsOnly:true});
  }
    ss.setActiveSheet(ss.getSheets()[0]);
    sheet.getRange("A2:D2").clearContent();
}

This is my function. Since I don't know where the problem lies, I copied the whole thing, sorry about the bulk.

Date       Event    Time            Comments
01/01/2001 Movie    22:00           a
01/01/2001 Movie    11:00 PM        a
36892      Movie    0.7083333333    a
36892      Movie    12:00 PM        a

This was the result from adding the same date each time, and sorting them to the correct sheet using the function. The time is being odd, I really have no clue why, but I don't really care at this point, that's a future problem (unless anyone has any clue why it changes format too). The date, however, doesn't seem to be some random number as the same date gave the output of the same number. I would have expected it to look like:

Date       Event    Time            Comments
01/01/2001 Movie    10:00 PM        a
01/01/2001 Movie    11:00 PM        a
01/01/2001 Movie    (I forget)      a
01/01/2001 Movie    12:00 PM        a

Upvotes: 1

Views: 115

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

Answer:

You need to remove {contentsOnly:true} from your copyTo() method.

Reasoning:

By setting the contentsOnly boolean to true you're telling sheets to copy what it sees in the cell, not what you see.

In Sheets, all days are serialized starting from the 1st January 1900 with a day of 1, so copying 01/01/2001 is day with serial number 36892.

As for the date - it's being read as a fraction of how far through the day it is - at 17:00h and with 24 hours in the day the date will be seen as 17/24 or 0.708333333333.

Upvotes: 1

Related Questions