Parent contact list

Sometimes you need a list of parents outside Scoutworks. For example, to invite parents to a meeting from the group’s own email account, or to give a camp leader the emails of the parents in their section. This recipe builds that list in a Google Sheet.

Each parent appears once in each section, even with two children in it.
A parent with children in two sections appears in both.
A Bcc row for each section holds every address, ready to paste.

It takes about five minutes.

This list does not know which parents have unsubscribed. For news to parents, send the email from Scoutworks instead, which does. Use this list for the jobs Scoutworks email cannot do for you.

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 → parent contact list. https://developers.scoutworks.app/recipes/parent-contacts

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

function parentContacts() {
  // section name → parent id → { name, email, children }
  const bySection = {};

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

  members.forEach(function (m) {
    const section = m.sections[0] ? m.sections[0].name : 'No section';
    const parents = (bySection[section] = bySection[section] || {});
    (m.parents || []).forEach(function (p) {
      if (!parents[p.id]) parents[p.id] = { name: p.name || '', email: p.email || '', children: [] };
      parents[p.id].children.push(m.preferredName || m.firstName);
    });
  });

  const rows = [['Section', 'Parent or carer', 'Email', 'Children']];
  Object.keys(bySection).sort().forEach(function (section) {
    const parents = Object.keys(bySection[section]).map(function (id) { return bySection[section][id]; });
    parents.sort(function (a, b) { return a.name.localeCompare(b.name); });
    parents.forEach(function (p) {
      rows.push([safe(section), safe(p.name), safe(p.email), safe(p.children.join(', '))]);
    });
    const emails = parents.map(function (p) { return p.email; }).filter(String);
    rows.push([safe(section), 'Bcc', safe(emails.join('; ')), emails.length + (emails.length === 1 ? ' address' : ' addresses')]);
  });

  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName('Parent contacts') || book.insertSheet('Parent contacts');
  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 to try it, or your own key.

3. Run it

Choose parentContacts in the toolbar and press Run. Open the Parent contacts tab. To email a section, copy the cell next to its Bcc row and paste it into the Bcc box of a new email. Always use Bcc, not To. Parents must not see each other’s addresses.

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.

This sheet holds the names and emails of every parent in your group. Keep it private to the leaders who need it. Delete the tab when you no longer need it. Your group’s data protection policy applies to this copy too.

Notes

The API sends a parent’s name and email only. It never sends phone numbers, addresses, health information or notes.
A member’s main section decides where their parents appear. A young person moving on counts in their main section only.
The same parent has the same id for each child, so brothers and sisters share one row.

Start here

Recipes for leaders

Recipes by tool

How the API works

Tools

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