Convert date object from Google sheets to Firebase timestamp

Question

I've got a date object in Google Sheet which I want to import to firestore as a timestamp object, but it doesn't work when I do it directly. I am using Google Apps Script.

This stores an empty map in firestore:

 data.date = new Date();

What should I do to convert the date object to timestamp.

This is my full code

function uploadData() {
   var firestore = FirestoreApp.getFirestore (email, key, projectId);
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheetname = "Sheet1";
   var sheet = ss.getSheetByName(sheetname); 
   var sheetLR = sheet.getLastRow();
   var sheetLC = sheet.getLastColumn(); 
   var dataSR = 2; 
   var sourceRange = sheet.getRange(2,1,sheetLR-dataSR+1,sheetLC);
   var sourceData = sourceRange.getValues();
   var sourceLen = sourceData.length;

  // Loop through the rows
   for (var i=0;i<sourceLen;i++){
     if(sourceData[i][1] !== '') {
       var data = {};

       data.date = new Date(); //Want to convert this date to timestamp object for firestore
       data.time = sourceData[i][1];
       data.batch = sourceData[i][2];
       data.topic = sourceData[i][3];

       firestore.createDocument("classRoutine", data);
     }

  }
}

This may help you: https://stackoverflow.com/questions/221294/how-do-you-get-a-timestamp-in-javascript — Renaud Tarnec, Jun 02 '20 at 10:50
It works but returns a large integer as the data type in firestore, how do I change that — Nehal, Jun 02 '20 at 12:23
As far as I can see by looking at the [code](https://github.com/grahamearley/FirestoreGoogleAppsScript) for the library, you should be able to pass a `Date` object. What is the value of `dateSt`? — Frank van Puffelen, Jun 02 '20 at 13:22
Forget the dateSt variable. new Date() doesn't work either which is supposed to return the current date. — Nehal, Jun 02 '20 at 14:21
@Nehal could you pls tell how the date object you are having is like this 3/2/2020 9:06:07 or only date field. — Nidhin Kumar, Jun 04 '20 at 06:28
@Nidhinkumar I tried every format google sheets allow, nothing woks — Nehal, Jun 04 '20 at 09:12
@Nehal - what stops you from converting to "A timestamp in RFC3339 UTC "Zulu" format, accurate to nanoseconds. Example: "2014-10-02T15:01:23.045123456Z"."? You can do this with [`toISOString()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toISOString) method and a bit of formatting (since Timestamp requires accuracy to nanoseconds) — Oleg Valter is with Ukraine, Jul 03 '20 at 18:58

score 1 · Answer 1 · answered Jun 04 '20 at 09:59

Suppose if your date object is like 3/2/2020 9:06:07 and from that, you need to extract only the date 2020-03-02 and for that date, if you want to get the timestamp 1583087400000 you can try the below solution.

       var dateSt = sourceData[i][0].toString();
       var stDate = new Date(dateSt);
       var stringfied = JSON.stringify(stDate);
       var updatedDt = stringfied.slice(1,11);

       //now u will have the date as 2020-03-02

       var myDate= updatedDt;
       myDate=myDate.split("-");
       var formattedDate=myDate[0]+"/"+myDate[1]+"/"+myDate[2];

       //Now the date is converted to 2020/03/02 (it should be in YYYY/MM/DD)

       var timestamp = new Date(formattedDate).getTime();

       //Now will have the timestamp for that particular date as 1583087400000
       data.date = timestamp;

Suppose if u don't have a date in your google sheet and you want to create it from the app script means you can try this second solution

       var dateSt = new Date().toISOString().slice(0,10);
       //dateSt will have the date in 2020-06-04
       var myDate = dateSt;
       myDate = myDate.split("-");
       var formattedDate=myDate[0]+"/"+myDate[1]+"/"+myDate[2];
       //formatted date will be 2020/06/04
       var timestamp = new Date(formattedDate).getTime();
       //timeStamp will be 1591209000000
       data.date = timestamp;

This stores the timestamp as integer, but I want to save it as timestamp for firestore. — Nehal, Jun 04 '20 at 17:32

Convert date object from Google sheets to Firebase timestamp

1 Answers1

Linked