Fetching data from external API to Google Sheets using Google Script

Question

I'm fetching data from an external API into a Google Sheet and it's working fine. The problem is that I need to make a kind of "loop" to bring the data I need. To exemplify, I put the OrderId at a row and the script brings the status from an API.

It works fine with the first line but what should I change on my script to make it more dynamic? Bring all the data from every row at column 'A'?

Below the piece of the script that inserts the data in the sheet:

function getOrder() {

var sheet = SpreadsheetApp.getActiveSheet()
var order = sheet.getRange(3,1).getValue()  
var url = ("https://myendpoint.com/orders/"+order);

try
{
  var response = UrlFetchApp.fetch(
    url,
    {
      "headers":{
        "Accept":"application/json"
      }
    }
  );

  var fact = response.getContentText();
  var data = JSON.parse(fact);

  Logger.log(data["status"]);
  sheet.getRange(3,2).setValue(data["status"]);
}
catch(error)
{ 
  Logger.log(error);
  sheet.getRange(3,2).setValue("Error"); 
}

I'm not a developer so I am struggling to make it work =) Appreciate the help

Hello, I'm not sure if I understood your problem. When you said "it works fine with the first line", are you referring to the range(3,2)? So each value of data["status"] depends of the value in column A for each row? Also, sharing an example of your Sheet would be very helpful. — Jescanellas
Hi @Jescanellas! Yes, you got exactly what I mean. Each value of data["status"] depends of the value in column A for each row. Column A I put the orderId (3,1) and the Colunm B I return the result (OrderStatus) that depends on each row.. I think I need to make a kind of loop in the script or something to make it work for the whole column. Here's the sheet: docs.google.com/spreadsheets/d/… Thanks for the help — Renato França

Jescanellas Jescanellas · Accepted Answer · 2020-01-07T11:34:23

Thanks for your reply. As you guessed, this can be solved with a loop. In this case a for fits perfectly as it's meant to "run the same code over and over again, each time with a different value." You can read about them here.

First we get the last row of the sheet (I'm assuming the Sheet only contains the data you showed).
Use getValues to store the Order Ids in a 2D array, as getValue only gets the value of the top-left cell of the Range.
Declare the url variable each time inside the for loop. As you see I'm using orders[i][0] where i is the Row, and 0 is the column. If you used Column B too, the next column would be 1. A representation of this "2D" array:

function getOrder(){


  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); //Value is 15
  var orders = sheet.getRange(3, 1, lastRow - 2).getValues(); //Deduce 2, as it gets the values from Row 3 + 15 rows.


  for (var i = 0; i < orders.length; i++){

    var url = ("https://myendpoint.com/orders/"+orders[i][0]);

    try 
    {
      var response = UrlFetchApp.fetch(
        url,
        {
          "headers":{
            "Accept":"application/json"
          }
        }
      );

      var fact = response.getContentText();
      var data = JSON.parse(fact);

      Logger.log(data["status"]);
      sheet.getRange(i+3, 2).setValue(data["status"]);

    } 
    catch(error)
    {
      Logger.log(error);
      sheet.getRange(i+3, 2).setValue("Error");
    }

  }
}

The rest of the code is pretty much the same, except the setValue, where we use the i iterator to write in the correct row. We add 3 as the array index starts at 0 but the data starts at Row 3.

Hope this helps!

Fetching data from external API to Google Sheets using Google Script

1 Answers