Who is going on trips and camps

Before a trip or a camp, leaders need one list: who is going, which section they are in, and who to contact. This recipe builds it in a Google Sheet for every trip and camp in the next six months. 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.

Code.gs

// Scoutworks → who is going on trips and camps. https://developers.scoutworks.app/recipes/trips-and-camps

const BASE = 'https://api-demo.scoutworks.app/api/v1';
const MONTHS_AHEAD = 6;
const GOING = ['rsvp_yes'];

function tripsAndCamps() {
  const from = new Date();
  const to = new Date();
  to.setMonth(to.getMonth() + MONTHS_AHEAD);

  const events = readAll(
    '/events?from=' + encodeURIComponent(from.toISOString()) + '&to=' + encodeURIComponent(to.toISOString())
  ).filter(function (ev) { return ev.type === 'activity_trip' || ev.type === 'camp_residential'; });

  // Parents come from the members list. Read it once, not once for each event.
  const parents = {};
  readAll('/members').forEach(function (m) { parents[m.id] = m.parents || []; });

  const rows = [['Event', 'Starts', 'Ends', 'First name', 'Last name', 'Section', 'Parents and carers', 'Parent emails']];
  events.forEach(function (ev) {
    readAll('/events/' + ev.id + '/attendees').forEach(function (a) {
      if (GOING.indexOf(a.rsvp) === -1) return;
      const family = parents[a.member.id] || [];
      rows.push([
        safe(ev.title),
        ev.startsAt.slice(0, 10),
        ev.endsAt ? ev.endsAt.slice(0, 10) : '',
        safe(a.member.firstName),
        safe(a.member.lastName),
        safe(a.section ? a.section.name : ''),
        safe(family.map(function (p) { return p.name || ''; }).join(', ')),
        safe(family.map(function (p) { return p.email || ''; }).filter(String).join('; ')),
      ]);
    });
  });

  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName('Trips and camps') || book.insertSheet('Trips and camps');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
  sheet.setFrozenRows(1);
  book.toast(events.length + ' trips and camps, ' + (rows.length - 1) + ' places.');
}

// 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 to try it, or your own key.

3. Run it

Choose tripsAndCamps in the toolbar and press Run. Open the Trips and camps tab. Use Data → Create a filter to show one event at a time. To refresh it each morning in the weeks before a camp, add a trigger: choose Triggers (the clock), then Add Trigger. Function: tripsAndCamps. Event source: Time-driven. Type: Day timer.

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:

Scope	Why
`events:read`	The events, and who is going to each one.
`members:read`	To read the members list.
`members.contacts:read`	To add parents’ names and emails. Without it, those two columns stay empty.

This sheet names children and their parents. Keep it private to the leaders on the trip. Delete the tab after the event.

Notes

Health information is not here. The API never sends medical details, dietary needs or emergency phone numbers. Take those from Scoutworks itself.
Maybe and waiting list. To add young people who said maybe or are on the event’s waiting list, change GOING to ['rsvp_yes', 'rsvp_maybe', 'rsvp_waitlist']. Add a column with a.rsvp so you can tell them apart.
Meetings too. Remove the .filter(…) line to list every event, including normal meetings. This makes one extra request for each event, so keep MONTHS_AHEAD small.
Joint events. A trip run by another group that yours has a place on is listed too. Only your own young people are in its list.

Start here

Recipes for leaders

Recipes by tool

How the API works

Tools

Who is going on trips and camps

1. Add the script

2. Add your key

3. Run it

4. Switch to your group

Notes

​1. Add the script

​2. Add your key

​3. Run it

​4. Switch to your group

​Notes

1. Add the script

2. Add your key

3. Run it

4. Switch to your group

Notes