How can I convert epoch time in seconds to human readable from API JSON response in Apps Script befo-CodePudding

Current Apps Script Code:

function BTC_1hour() {

var url = 'https://openapi-sandbox.kucoin.com/api/v1/market/candles?type=1min&symbol=BTC-USDT&startAt=1566703297';
var response = UrlFetchApp.fetch(url); // store API fetch in variable named response
var JSONresponse = JSON.parse(response);
var formatData = JSONresponse.data;

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('1h');
sheet.getRange(4,1,formatData.length, formatData[0].length).setValues(formatData);

}

Output of Logger.log(response) to show API response:

{"code":"200000","data":[["1670414400","16805.8","16858.5","16858.5","16736.5","0.1021428","1716.165507409"],["1670410800","8000","8000","8000","8000","0.14","1120"],["1670407200","16827.9","12922.8","16827.9","12922.8","0.11241178","1848.427525934"],["1670403600","16805.1","16867.2","16889","16730","0.224152","3763.9561529"],["1670400000","16809","16830.3","25790.340298","16751.9","0.15817498","2685.85805974755522"],["1670396400","16822","16822","16822","16822","0.00118891","19.99984402"],["1670392800","16988.5","16988.5","16988.5","16988.5","0.00008","1.35908"],["1670389200","16966.4","16966.4","16966.4","16966.4","0","0"],["1670385600","16966.4","16966.4","16966.4","16966.4","0","0"],["1670382000","16966.4","16966.4","16966.4","16966.4","0.0253421","429.96420544"],["1670378400","17021","17021","17021","17021","0","0"],["1670374800","17021","17021","17021","17021","0.0000117","0.1991457"],["1670371200","17036.8","17077.6","17077.6","17036.8","0.00665796","113.430810288"],["1670367600","16986","16986","16986","16986","0.00100275","17.0327115"],["1670364000","16950.3","16936.2","16950.3","16936.2","0.0147845","250.4433039"],["1670360400","16944.6","16940.1","16944.6","16940.1","0.00910371","154.233732771"],["1670356800","16988.1","16927","17016.4","16000","0.22622738","3833.810489916"],["1670353200","16989.9","16915.4","16989.9","16915.4","0.007","118.66855"]]}

This all prints to Google Sheets as expected but I'd like to be able to convert the first cell/string of data that is epoch time in seconds from the API response to human readable formatting before printing it to the Google Sheet. What is the best way to go about this?

I tried to work with a for loop but struggled to understand if this was the right course.

CodePudding user response：

From your showing script and sample data, how about the following modification?

From:

var formatData = JSONresponse.data;

To:

var formatData = JSONresponse.data.map(([a, ...v]) => [new Date(Number(a) * 1000), ...v]);

In this modification, it supposes that the 1st element of the array is the Unix time.
By this modification, the UNIX time is converted to the date object. By this, when the value is put into the Spreadsheet, you can use it as the date object. So, you can give your expected format on Spreadsheet. Or, if you want to convert the Unix time to the string value with your expected format, you can also use var formatData = JSONresponse.data.map(([a, ...v]) => [Utilities.formatDate(new Date(Number(a) * 1000), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss"), ...v]);. Please select one of them for your actual situation.

Reference:

map()