CODEWITHSUNDEEP
javascriptloopsgoogle-apps-scriptgoogle-sheetsurlfetch
Craig
Craig

Reputation: 55

How to loop through multiple API calls in Google Apps Script?

I'm putting together a spreadsheet that pulls in all the comments from a handful of different documents (called "Cards"). Unfortunately, the API I'm using only lets you call one card at a time, so I need to loop through as many cards as it takes.

I have the card IDs in a list in the spreadsheet, and I figured out how to call one card, but I have no idea how to repeat the API call with the next card ID and append the comments in the next empty row.

Here's a snippet from my code that calls the API and places it in the spreadsheet (FYI, the Util. functions contain all the parameters, the base URL, and the pagination):

const DATA_SHEET = "Data";
const USERNAME_CELL = "B1";
const API_TOKEN_CELL = "B2";
const CARD_ID = "A3"

const FIRST_OUTPUT_ROW = 4;
const FIRST_OUTPUT_COL = 1;

var Util = {};

Util.getNextPage = function(response) {
  var url = response.getAllHeaders().Link;
  if (!url) {
    return "";
  }
  return /<([^>]+)/.exec(url)[1];
};

Util.formatUrl = function(urlOrPath) {
  var baseUrl = "https://api.getguru.com/api/v1";
  if (urlOrPath.startsWith("http")) {
    return urlOrPath;
  }
  if (urlOrPath.startsWith("/")) {
    return baseUrl + urlOrPath;
  } else {
    return baseUrl + "/" + urlOrPath;
  }
};

Util.get = function(path, username, apiToken) {
  var params = {
    "method": "GET",
    "muteHttpExceptions": true,
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Basic " + Utilities.base64Encode(username + ":" + apiToken),
      "x-guru-application": "spreadsheet",
      "X-Amzn-Trace-Id": "GApp=spreadsheet"
    }
  };
  
  // The input url can either be a full URL or just the path, so we call formatUrl to prepend the base URL as needed.
  var response = UrlFetchApp.fetch(Util.formatUrl(path), params);
  var data = JSON.parse(response.getContentText());
  
  // Check if there's another page of results.
  var nextPage = Util.getNextPage(response);
  if (nextPage) {
    data.nextPage = nextPage;
  };
  
  return data;
};

Util.getAll = function(url, username, apiToken, callback) {
  var data = [];
  
  while (url) {
    var page = Util.get(url, username, apiToken);
    
    var startIndex = data.length;
    page.forEach(function(a) {
      data.push(a);
    });
    
    // Get the url of the next page of results.
    url = page.nextPage;
    
    if (callback) {
      // The second parameter is whether we're done or not.
      // If there's a url for the next page that means we're not done yet.
      callback(data, startIndex, page.length, url ? false : true);
    }
  }
  
  return data;
};

function getComments() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(DATA_SHEET);
  var cardId = sheet.getRange(CARD_ID).getValue();
  var username = sheet.getRange(USERNAME_CELL).getValue();
  var apiToken = sheet.getRange(API_TOKEN_CELL).getValue();

  var pathUrl = "/cards/"+cardId+"/comments"
  
  var results = Util.getAll(pathUrl, username, apiToken);

  results.forEach(function(comment, commentIndex) {
    sheet.getRange(FIRST_OUTPUT_ROW + commentIndex, FIRST_OUTPUT_COL).setValue(comment.dateCreated);
    sheet.getRange(FIRST_OUTPUT_ROW + commentIndex, FIRST_OUTPUT_COL + 1).setValue(comment.content);
  });
}

Upvotes: 0

Views: 384

Answers (1)

Wicket
Wicket

Reputation: 38160

Google Apps Script uses JavaScript as programming language. You can use the JavaScript loop statements:

  • for
  • while
  • do..while
  • for..in
  • for..of

Also Array has several methods to iterate over its elements.

Another option is to use UrlFetchApp.fetchAll to fetch multiple requests at once.

Reference

Resources

Upvotes: 1

Related Questions