How to parse/transform values such as epoch to datetime??
CharlieTemplar opened this issue · 2 comments
Would be nice to transform epoch timestamps to human readable datetime.
eg 1628337947 = Saturday, 7 August 2021 12:05:47
=ImportJSON("https://github.com/bradjasper/ImportJSON/files/6950617/sample-null.json.txt","/results")
I added a new column next to the timestamps and used this formula where "A1" is the first timestamp in your results.
=(LEFT(A1,10)/86400)+DATE(1970,1,1)
Hi there, I needed something and was researching for an answer.
Since I couldn't find one, what I thought was to use the transformFunc
parameter available in ImportJSONAdvanced
and this is what I ended up with:
function _test() {
var url = "https://github.com/bradjasper/ImportJSON/files/6950617/sample-null.json.txt";
var query = "/results" ;
var parseOptions = "allHeaders,epoch2date-2";
var json = ImportJSONAdvanced(url, {}, query, parseOptions, includeXPath_, _myDataTransform);
Logger.log(json);
}
function _myDataTransform(data, row, column, options) {
defaultTransform_(data, row, column, options);
// ignore the header
if (row) {
var optionsCol = hasOption2_(options, "epoch2date");
// check if there's a column to be converted and the separator is there
if (optionsCol > -1 && options[optionsCol].search("-")) {
var valueCol = parseInt(options[optionsCol].split("-")[1]);
// check if the current column is the same as the column informed to have the timestamp
if (column == valueCol) {
// transform the timestamp into a human readable string
data[row][column] = new Date(parseInt(data[row][column])).toLocaleString();
}
}
}
}
/**
* Returns the index if the given set of options contains the given option.
* Returns false if the option is not found in the set.
*/
function hasOption2_(options, option) {
return options.findIndex((opItem) => { return opItem.startsWith(option);}, option)
}
Not elegant, but allows me to use parseOptions to tell importJSON that I want to convert a epoch to a human readable format and instruct which column I want it to convert.
Hope it helps you or someone else looking for something like this.