Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Paste an Array into the last row in Google Sheets

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

  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);
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading