修改Apps Script的map()方法过滤Kucoin API响应仅保留前5列

原代码 #

function BTC_1hour() {

var url = 'https://openapi-sandbox.kucoin.com/api/v1/market/candles?type=1hour&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.map(([a, ...v]) => [new Date(Number(a) * 1000), ...v])

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

}

Kucoin API返回响应 #

{"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"]]}

修改方案 #

有两种简单方法可以实现只保留转换后的日期+后续4列（共5列）：

方法1：解构时直接指定需要的列

明确提取数组的前5个元素，自动排除第6、7列：

var formatData = JSONresponse.data.map(([a, b, c, d, e]) => [new Date(Number(a) * 1000), b, c, d, e])

方法2：截取数组前4项

对展开后的剩余数组做截取，只保留前4项，加上转换后的日期正好是5列：

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

说明 #

• 方法1更直观，适合列数固定的场景，一眼就能看到保留的每一列
• 方法2更灵活，后续调整保留列数时只需修改slice的参数即可
• 修改后写入表格的代码无需调整，formatData的列数会自动适配，setValues会正常写入前5列数据

内容的提问来源于Stack Exchange，提问作者Welchums