I’m trying to paste an array into the last row of a sheet.
(The data is from an API source and I want to grab a bunch of info and add a new line at the bottom of the sheet as historical data)
I managed to piece together the correct range, but now it wants it as an integer?
(Exception: Cannot convert ‘A3’ to int.)
Here’s the part in question:
let lastRow = sheet.getLastRow() + 1;
let lastColumn = sheet.getLastColumn();
let myA = "A" + lastRow ;
let myZ = "L" + lastRow ;
sheet.getRange(myA, myZ).setValues(stData);
A simple AppendRow doesn’t work either, as it only pastes [Ljava.lang.Object;@6ddc861f
in the cell instead of the actual values.
and here’s the full code for reference. (except for the API ID):
function getST() {
const playerID = "XXX";
let url = "https://smartytitans.com/api/info/player/";
let sheet = SpreadsheetApp.getActiveSheet();
let request = url + playerID;
let response = UrlFetchApp.fetch(request);
let data = JSON.parse(response.getContentText());
let stStats = [];
stStats.push(data.data.level);
stStats.push(data.data.stats.gld);
stStats.push(data.data.stats.prest);
stStats.push(data.data.stats.invst);
stStats.push(data.data.stats.master);
stStats.push(data.data.stats.help);
stStats.push(data.data.stats.ascendUpg);
stStats.push(data.data.stats.bounty);
stStats.push(data.data.stats.collection);
stStats.push(data.data.cityName);
stStats.push(data.data.cityLevel);
stStats.push(data.data.rankGld);
let stData = [];
stData.push(stStats);
let lastRow = sheet.getLastRow() + 1;
let lastColumn = sheet.getLastColumn();
let myA = "A" + lastRow ;
let myZ = "L" + lastRow ;
sheet.getRange(myA, myZ).setValues(stData);
}
>Solution :
The Sheet.appendRow() method expects a 1D array but stData contains a 2D array. To make it work, use stStats, like this:
///...
stStats.push(data.data.rankGld);
sheet.appendRow(stStats);
}