Who is due to move on

// Scoutworks → who is due to move on. https://developers.scoutworks.app/recipes/moving-on

const BASE = 'https://api-demo.scoutworks.app/api/v1';

// The top age of each section, in months. The first word found in a section's name wins.
const SECTIONS = [
  { word: 'squirrel', months: 6 * 12 },
  { word: 'beaver', months: 8 * 12 },
  { word: 'cub', months: 10 * 12 + 6 },
  { word: 'explorer', months: 18 * 12 },
  { word: 'scout', months: 14 * 12 },
];
const LOOK_AHEAD_MONTHS = 6;

function movingOn() {
  const today = new Date();
  const soon = addMonths(today, LOOK_AHEAD_MONTHS);
  const rows = [];

  const members = readAll('/members');
  // A key without members.contacts:read gets no dateOfBirth field at all.
  if (members.length && !('dateOfBirth' in members[0])) {
    throw new Error('This key needs the members.contacts:read scope to read dates of birth.');
  }

  members.forEach(function (m) {
    if (!m.dateOfBirth) return; // No date of birth recorded.
    const main = m.sections[0];
    const rule = main && SECTIONS.find(function (s) {
      return main.name.toLowerCase().indexOf(s.word) !== -1;
    });
    if (!rule) return;

    const topAge = addMonths(new Date(m.dateOfBirth + 'T00:00:00Z'), rule.months);
    if (topAge > soon) return;

    rows.push([
      safe(m.preferredName || m.firstName),
      safe(m.lastName),
      safe(main.name),
      m.dateOfBirth,
      topAge.toISOString().slice(0, 10),
      topAge <= today ? 'Over the age now' : 'Within ' + LOOK_AHEAD_MONTHS + ' months',
      m.sections.length > 1 ? 'Yes' : '',
    ]);
  });

  rows.sort(function (a, b) { return a[4] < b[4] ? -1 : a[4] > b[4] ? 1 : 0; });
  writeTab('Moving on', [
    ['First name', 'Last name', 'Section', 'Date of birth', 'Reaches top age', 'When', 'Already in next section'],
  ].concat(rows));
  SpreadsheetApp.getActive().toast(rows.length + ' young people to plan for.');
}

function addMonths(date, months) {
  const d = new Date(date.getTime());
  d.setUTCMonth(d.getUTCMonth() + months);
  return d;
}

// Replaces a tab's contents with these rows.
function writeTab(name, rows) {
  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName(name) || book.insertSheet(name);
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  sheet.setFrozenRows(1);
}

// Every item in a list, following nextCursor to the last page.
function readAll(path) {
  const items = [];
  let cursor = null;
  do {
    let url = BASE + path + (path.indexOf('?') === -1 ? '?' : '&') + 'limit=200';
    if (cursor) url += '&cursor=' + encodeURIComponent(cursor);
    const body = fetchScoutworks(url);
    items.push.apply(items, body.data);
    cursor = body.nextCursor;
  } while (cursor);
  return items;
}

// Calls the API. Waits and tries again if the rate limit is reached.
function fetchScoutworks(url) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('SCOUTWORKS_API_KEY');
  if (!apiKey) throw new Error('Add SCOUTWORKS_API_KEY in Project Settings → Script Properties.');
  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;
}