> ## Documentation Index
> Fetch the complete documentation index at: https://developers.scoutworks.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Who is due to move on

> See which young people reach the top age of their section soon, so each section can plan who moves up and when.

Each section has an age range. This recipe finds the young people who are near the top of their range, and the date they reach it. Section leaders use it to plan who moves on each term, and how many places open up for the [waiting list](/recipes/weekly-numbers).

It fills a **Moving on** tab in a Google Sheet. It takes about five minutes.

## 1. Add the script

In your Google Sheet, choose **Extensions → Apps Script**. Delete what is there, paste the script below and choose **Save**.

```javascript Code.gs theme={null}
// 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;
}
```

## 2. Add your key

In the script editor, choose **Project Settings** (the cog), then **Script Properties → Add script property**. Property: `SCOUTWORKS_API_KEY`. Value: the [sandbox key](/guides/sandbox) to try it, or your own key.

## 3. Run it

Choose **movingOn** in the toolbar and press **Run**. Open the **Moving on** tab. The young person who reaches the top age first is at the top.

The **Already in next section** column says **Yes** when the young person is in two sections. Usually that means they have started to move on already.

## 4. Switch to your group

Change `api-demo.scoutworks.app` to `api.scoutworks.app`, and replace the sandbox key with your own. Your key needs `members:read` and `members.contacts:read`. The second scope adds dates of birth. Without it, the script stops with an error that names the missing scope.

<Warning>
  Dates of birth are personal data. Keep the sheet private to your leaders. Do not share it with a link that anyone can open.
</Warning>

## Change the rules

* **The age ranges.** `SECTIONS` uses the usual UK ranges: Squirrels to 6, Beavers to 8, Cubs to 10½, Scouts to 14 and Explorers to 18. Change `months` if your group moves young people on at other ages.
* **Section names.** The script finds a section by a word in its name, so “Red Beavers” and “1st Anytown Beavers” both count as Beavers. If a name has none of the words, add a line to `SECTIONS`. Keep `explorer` above `scout`, because “Explorer Scouts” contains both words.
* **How far ahead.** Change `LOOK_AHEAD_MONTHS` to see further ahead, for example `12` to plan a whole year.