CODEWITHSUNDEEP
javascriptloopsgoogle-apps-script
user13599639
user13599639

Reputation: 37

Google Script loop overwriting all rows

I've written a script to read data from a website using an API and i'd like to write the output in a google sheet. There are 4 data items per ID of a json object and i'd like to write them to 4 columns, C - F, starting in row 2 until row 32.

Upvotes: 1

Views: 379

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal as follows.

  • You want to put the values of [current_price,forecast,demand,available_shares] from the cell "C2" on the active sheet.

For this, how about this answer?

Modification points:

  • In your script, the value is put to the Spreadsheet with for (i = 2; i < 33; i++) {} every each stockId. By this, the old value is overwritten by the next value. I think that this is the reason of your issue.
  • I think that in your case, the following flow can be used.
    1. An array is prepared before the for loop.
    2. Put the value of [current_price,forecast,demand,available_shares] to the array.
    3. When the for loop is finished, the array is put to the Spreadsheet.

By this flow, the value of each stockId is put to the array, and then, the array is put to the Spreadsheet. When above points are reflected to your script, it becomes as follows.

Modified script:

function myFunction() {

  let values = [];  // Added

  for (let stockId = 1; stockId < 32; stockId++) {
    if (stockId == 24) continue;
    var response = UrlFetchApp.fetch("https://api.torn.com/torn/" + stockId + "?selections=stocks&key=" + API);
    var content = response.getContentText();
    var json = JSON.parse(content);
    var current_price = json["stocks"][stockId]["current_price"];
    var forecast = json["stocks"][stockId]["forecast"];
    var demand = json["stocks"][stockId]["demand"];
    var available_shares = json["stocks"][stockId]["available_shares"];

    values.push([current_price,forecast,demand,available_shares]);  // Added
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  // Added
  ss.getRange("C2:F" + (values.length + 1)).setValues(values);  // Added
}

Reference:

Upvotes: 1

Related Questions