Below I have working code that fills in the correct column that I want with the information that I want, the issue is I am going to have 2,000 rows I need to fill, and this fills in one cell at a time in descending order. Is there a way to do a batch update instead of doing 1 cell at a time in the column?
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);;
}}
CodePudding user response:
Try this:
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;
});
}