CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Renato França
Renato França

Reputation: 13

Fetching data from external API to Google Sheets using Google Script

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

Upvotes: 1

Views: 4399

Answers (1)

Jescanellas
Jescanellas

Reputation: 2608

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.

  1. First we get the last row of the sheet (I'm assuming the Sheet only contains the data you showed).
  2. 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.
  3. 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:

enter image description here

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!

Upvotes: 1

Related Questions