CODEWITHSUNDEEP
firebasegoogle-apps-scriptgoogle-cloud-firestoregoogle-sheets-api
Nehal
Nehal

Reputation: 1489

Convert date object from Google sheets to Firebase timestamp

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

  }
}

Upvotes: 0

Views: 350

Answers (1)

Nidhin Kumar
Nidhin Kumar

Reputation: 3579

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;

Upvotes: 1

Related Questions