0

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);
  });
}
4
  • 1
    Code is not reproducible. Please provide minimal reproducible example Commented Jan 28, 2023 at 23:39
  • 1
    I have to apologize for my poor English skill. Unfortunately, I cannot imagine your Spreadsheet from your question. So, can you provide the sample input and output situations you expect? By the way, what is Util.getAll? Commented Jan 29, 2023 at 0:30
  • The Util functions contain all the parameters, the base URL, and the pagination. I've updated my post to include the whole code. Commented Jan 29, 2023 at 2:47
  • Besides the code you should add an usage example, some sample data, the corresponding expected result and show what you have tried. P.S. If before seeing my answer you didn't know what is said on it / not able to try to use a loop, then you should post a new question. Commented Jan 29, 2023 at 3:45

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.