如何使用Google Script批量更新Google Sheets列，而不是一次更新一个

Question

下面我有一个工作代码，它用我想要的信息填充我想要的正确列，问题是我需要填充2000行，并且按降序一次填充一个单元格。有没有一种方法可以进行批量更新，而不是在列中一次更新一个单元格？

function logDataInEveryCell() {
const data = SpreadsheetApp.getActiveSpreadsheet().getRange("A2:A51").getValues();
let JOBID = [];
for (let a = 0; a < data.length; a++) {
JOBID = data[a]

var res = UrlFetchApp.fetch("API" + JOBID + "Token")


var content = res.getContentText();
var json = JSON.parse(content);
// Driver Name
var dispatches = json["dispatches"]["items"];
var lastItem = dispatches.pop();
var dName = [lastItem.vehicle.driver.contact.name]

Logger.log(dName)
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var colValues = sheet.getRange("C2:C51").getValues();
var count = colValues.filter(String).length
sheet.getRange(count+2,3).setValue(dName);;

}}

Answer 1

试试这个：

function logDataInEveryCell() {
const ss = SpreadsheetApp.getActive();
const vs = ss.getRange("A2:A51").getValues().flat();
const osh = ss.getSheetByName("Sheet1");
osh.clearContents();
let l = 1;
vs.forEach(e => {
let res = UrlFetchApp.fetch("API" + e + "Token");
let obj = JSON.parse(res.getContentText());
let items = obj["dispatches"]["items"];
items.pop();//guess
let vo = items.map(itm => [itm.vehicle.driver.contact.name])
osh.getRange(l,1,vo.length,vo[0].length).setValues(vo);
l += vo.length;
});
}