using UrlFetchApp.fetch vs. fetchAll in google apps script

make multiple, asynchronous api calls simultaneously using UrlFetchApp.fetchAll in apps script.

using UrlFetchApp.fetch vs. fetchAll in google apps script

context

mostly when running through a list of resources against which an api call needs to be triggered, we end up using the UrlFetchApp's fetch service; however, in case if the list is well-defined and needs to be consumed as a whole, you could make use of the fetchAll service instead, which may seem a bit complex to begin with but lets you get things done at a much faster pace.

preview

using fetch vs. fetchAll
using fetch vs. fetchAll

prior reading

this set up relies heavily on being able to manipulate arrays in javascript and so, it would be good to brush-up on the following topics -

i also use object destructuring when fetching data from a spreadsheet, as shown in the tweet below -

destructuring JavaScript object

architecture

there are 3 things i'd recommend to consider before starting to make use of the UrlFetchApp.fetchAll service -

  1. building those multiple requests
  2. parsing the right data points from the responses
  3. mapping the responses to their corresponding rows

codebase

to try the set up shown in the preview above 👆🏽, feel free to make a copy of the spreadsheet being used here.

function fetchAllData() {
  const baseUrl = `https://api.hashify.net/hash/md5/hex?value=`;
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const [header, ...data] = ss.getDataRange().getValues()
    .filter(row => row != '' ? row : null);
  const requests = data.map(column => column[header.indexOf("Word")])
    .map(word => `${baseUrl}${word}`);
  const responses = UrlFetchApp.fetchAll(requests);
  const hashes = responses.map(response => JSON.parse(response.getContentText()).Digest);
  hashes.map((hash, rowIndex) => ss.getRange(1 + rowIndex + 1, header.indexOf("HashAll") + 1).setValue(hash));
}
using the UrlFetchApp.fetchAll service

in fairness, it would also be good to have its counterpart — the UrlFetchApp.fetch service too -

function fetchData() {
  const baseUrl = `https://api.hashify.net/hash/md5/hex?value=`;
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const [header, ...data] = ss.getDataRange().getValues()
    .filter(row => row != '' ? row : null);
  for (let i = 0; i < data.length; i++) {
    let row = data[i];
    let word = row[header.indexOf("Word")];
    let response = UrlFetchApp.fetch(`${baseUrl}${word}`);
    let hash = JSON.parse(response.getContentText()).Digest;
    ss.getRange(1 + i + 1, header.indexOf("Hash") + 1).setValue(hash);
    SpreadsheetApp.flush();
  }
}

performance

a couple years ago, Kanshi Tanaike (a fellow GDE) had run some benchmarking tests to analyse the speed, process computation cost for this topic so do give that a read too 🙌🏽