search

bassarisse / google-spreadsheet-to-json

Simple tool to export Google Spreadsheets to JSON files, though Node API or CLI
The Unlicense
134 stars 33 forks source link

Date conversion logic? #24

Open pmjohann opened 6 years ago

pmjohann commented 6 years ago

Somehow dates are converted to days passed since year 1900. E.g.: a cell value of 2018-05-15 (Sheets GUI recognizes it as date type) get represented as 43235 in the JSON output, which is the amount of days passed from 1900-01-01 until 2018-05-15. Is this behavior on purpose, can it be configured somehow maybe?

bassarisse commented 6 years ago

Thanks for bringing this up. Never tested the tool with date cells, so I'll have to dig deeper.

We should probally add some parameters to define how the dates must be returned (ISO, timestamp, etc).

pmjohann commented 6 years ago

Alright, thanks, please keep us posted! :)

fontzter commented 6 years ago

As a workaround, I mapped the array in an intermediate then statement:

.then(res => { return res.map(item => { item.dateField = new Date( Date.UTC(0) + (item.dateField - 2) * 24 * 60 * 60 * 1000 ).toLocaleDateString('en-US', { timeZone: 'UTC' }) return item }) })

or more cleanly as a function: const formatGsDates = (items, dateColumns) => items.map(item => { dateColumns.forEach(col => { if (item[col]) { item[col] = new Date( Date.UTC(0) + (item[col] - 2) * 24 * 60 * 60 * 1000 ).toLocaleDateString('en-US', { timeZone: 'UTC' }) } }) return item })

and just run the results through the function in a then: .then(res => formatGsDates(res, ['dateField', 'anotherDateField']))

This formats it to a string in my locale format. You can change or set it to an epoch or w/e you wish.

HTH