CODEWITHSUNDEEP
javascriptdatetimegoogle-sheets
mark
mark

Reputation: 5070

How to format JavaScript timestamp for Google Sheets?

How can I convert a timestamp (in milliseconds since epoch) using JavaScript into a date and time format that is useable by Google Sheets Timeline chart.

My first attempt was to map the timestamps using timestamps.map(timestamp => new Date(timestamp).toLocaleString()), but Google Sheets is not accepting this as a valid date and time format:

enter image description here

Upvotes: 0

Views: 1406

Answers (1)

Tanaike
Tanaike

Reputation: 201388

I believe your current situation and goal as follows.

  • You put the values by manually copying and pasting the values retrieved by a script of [{timestamp: 1617413162282, value: 1}, {timestamp: 1617413239904, value: 2}].map(el => `${new Date(el.timestamp).toLocaleString()}\t${el.value}`).join("\n"). The values of timestamp and value are put to the columns "A" and "B", respectively.
  • You want to use the copied values of timestamp as the date object.

In your situation, the values of timestamp are put as the string. I thought that this might be the reason of your issue. In this case, in order to put the values of timestamp as the date object, I would like to propose to put the values using Google Apps Script. When Google Apps Script is used, the values of timestamp can be put as the date object. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet you want to use. And, please set the variables of ar and sheetName, and run the function of myFunction with the script editor. By this, the values are put to the sheet of sheetName. In this case, the values of timestamp are put as the date object.

function myFunction() {
  const ar = [{timestamp: 1617413162282, value: 1}, {timestamp: 1617413239904, value: 2}]; // Please set the values you want to put.
  const sheetName = "Sheet1"; // Please set the sheet name of the sheet you want to put the values.

  const values = ar.map(({timestamp, value}) => [new Date(timestamp), value]);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Note:

  • I think that your values can be also put using other languages. But I thought that in this case, when Google Apps Script is used, the process might be simpler.

References:

Upvotes: 1

Related Questions