0

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

mark
  • 4,678
  • 7
  • 36
  • 46
  • Can you please either tell us what format you are needing, or supply a link to the documentation for the particular Google API or library you are using? Or both? Thanks. – Matt Johnson-Pint Apr 02 '21 at 22:13
  • @MattJohnson-Pint any format that is useable by Google Sheets timeline would be ok https://support.google.com/docs/answer/9146783 – mark Apr 02 '21 at 22:21
  • It looks like removing the comma was sufficient: `new Date(timestamp).toLocaleString().replace(',','')` – mark Apr 02 '21 at 22:36
  • `toLocaleString` creates a locale-specific output. Results will vary on the culture/language settings of the user. I don't recommend that for values fed to a spreadsheet. – Matt Johnson-Pint Apr 02 '21 at 22:40
  • [The example here](https://developers.google.com/chart/interactive/docs/gallery/timeline) shows passing `Date` objects, not strings. – Matt Johnson-Pint Apr 02 '21 at 22:41
  • My question may have been unclear, but I'm not using any API, I'm pasting text into the web version of Google Sheets, so I can only use plain text – mark Apr 02 '21 at 22:53
  • About `I'm pasting text into the web version of Google Sheets, so I can only use plain text`, in this case, you manually copy the values to Google Spreadsheet? If it's so, where is `timestamps.map(timestamp => new Date(timestamp).toLocaleString())` used at? In order to correctly understand about your current situation, can you provide your current script? – Tanaike Apr 03 '21 at 00:38
  • Yes I manually copy the values into Google Sheets. I've generated a list of timestamps and values in JavaScript, now I want to manually copy and paste them into Google Sheets in order to create a Timeline visualization. Here's an example JavaScript script: [{timestamp: 1617413162282, value: 1}, {timestamp: 1617413239904, value: 2}].map(el => `\``${new Date(el.timestamp).toLocaleString()}\t${el.value}`\``).join("\n") – mark Apr 03 '21 at 01:30
  • Thank you for replying. From your replying, I proposed an answer. Could you please confirm it? If that was not the direction you expect, I apologize. If the putted date and time values in the Spreadsheet is shifted from your local timezone, please check this thread. [Ref](https://stackoverflow.com/q/66194990) – Tanaike Apr 03 '21 at 04:57

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks, I think this will work. However when I attempted to run in the script editor I got "An unknown error has occurred, please try again later." But it's ok, no need to debug, my comment [here](https://stackoverflow.com/questions/66925833/how-to-format-javascript-timestamp-for-google-sheets?noredirect=1#comment118301905_66925833) was good enough to solve my issue – mark Apr 03 '21 at 06:23
  • @mark Thank you for replying. I have to apologize for the inconvenience and my poor English skill. Unfortunately, I cannot understand about your current situation from your replying. Can I ask you about the detail of your current situation? By the way, I cannot replicate your issue of `An unknown error has occurred, please try again later.`. But, when the error of An unknown error has occurred, please try again later. occurs, when creates new Google Spreadsheet and use my script in the new Spreadsheet, will the situation be changed? – Tanaike Apr 03 '21 at 07:51