District totals in Google Sheets

// Scoutworks → Google Sheets, for a district. https://developers.scoutworks.app/recipes/district-totals

function importDistrictTotals() {
  const apiKey = PropertiesService.getScriptProperties().getProperty('SCOUTWORKS_API_KEY');
  if (!apiKey) throw new Error('Add SCOUTWORKS_API_KEY in Project Settings → Script Properties.');

  // The groups and nodes below yours that share data with you.
  const names = new Map();
  walk('https://api.scoutworks.app/api/v1/nodes/descendants?limit=200', apiKey, function (n) {
    names.set(n.id, { name: n.name, tier: n.tier });
  });

  // Last night's totals: one row per group and figure.
  const figures = new Map();
  let day = '';
  walk('https://api.scoutworks.app/api/v1/nodes/metrics?limit=200', apiKey, function (m) {
    if (!figures.has(m.groupId)) figures.set(m.groupId, {});
    figures.get(m.groupId)[m.metric] = m.stale ? '' : m.value;
    day = m.day;
  });

  const rows = [['Name', 'Level', 'Members', 'Waiting', 'Growth (12 months)']];
  names.forEach(function (n, id) {
    const f = figures.get(id) || {};
    rows.push([safe(n.name), n.tier, f.member_count, f.waiting_count, f.net_growth_12m]);
  });

  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName('Groups') || book.insertSheet('Groups');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  sheet.getRange(rows.length + 2, 1).setValue('Totals from the night of ' + day);
}

// Reads every page of a list.
function walk(url, apiKey, each) {
  let cursor = null;
  do {
    const body = fetchScoutworks(url + (cursor ? '&cursor=' + encodeURIComponent(cursor) : ''), apiKey);
    body.data.forEach(each);
    cursor = body.nextCursor;
  } while (cursor);
}

// Calls the API. Waits and tries again if the rate limit is reached.
function fetchScoutworks(url, apiKey) {
  for (let attempt = 0; attempt < 3; attempt++) {
    const res = UrlFetchApp.fetch(url, {
      headers: { Authorization: 'Bearer ' + apiKey },
      muteHttpExceptions: true,
    });
    const body = JSON.parse(res.getContentText());
    if (res.getResponseCode() === 200) return body;
    if (res.getResponseCode() === 429) {
      Utilities.sleep((body.retryAfter || 30) * 1000);
      continue;
    }
    throw new Error(body.error.code + ': ' + body.error.message);
  }
  throw new Error('Scoutworks is still busy. Try again in a minute.');
}

// Stops a name that starts with = + - or @ from running as a formula.
function safe(value) {
  const text = String(value);
  return /^\s*[=+\-@]/.test(text) ? "'" + text : text;
}