> ## 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.

# Your members in Google Sheets

> A sheet that fills itself with your members and refreshes every morning. One script, no add-ons.

This recipe adds a **Members** tab to a Google Sheet and fills it from Scoutworks. A timer refreshes it every morning. It takes about five minutes.

## 1. Add the script

<Steps>
  <Step title="Open the script editor">
    In your Google Sheet, choose **Extensions → Apps Script**.
  </Step>

  <Step title="Paste the script">
    Delete what is there and paste the script below. Choose **Save**.
  </Step>
</Steps>

```javascript Code.gs theme={null}
// Scoutworks → Google Sheets. https://developers.scoutworks.app/recipes/google-sheets

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

  const rows = [['First name', 'Last name', 'Sections', 'Patrol', 'Joined']];
  let cursor = null;

  do {
    let url = 'https://api-demo.scoutworks.app/api/v1/members?limit=200';
    if (cursor) url += '&cursor=' + encodeURIComponent(cursor);
    const body = fetchScoutworks(url, apiKey);

    body.data.forEach(function (m) {
      rows.push([
        safe(m.firstName),
        safe(m.lastName),
        safe(m.sections.map(function (s) { return s.name; }).join(', ')),
        safe(m.patrol ? m.patrol.name : ''),
        m.joinedAt.slice(0, 10),
      ]);
    });
    cursor = body.nextCursor;
  } while (cursor);

  const book = SpreadsheetApp.getActive();
  const sheet = book.getSheetByName('Members') || book.insertSheet('Members');
  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}

// 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;
}
```

<Warning>
  Keep the `safe()` function. Names on the waiting list come from a public form, so anyone can type one. Without `safe()`, a name like `=IMPORTXML(…)` would run as a formula in your sheet.
</Warning>

## 2. Add your key

<Steps>
  <Step title="Open Script Properties">
    In the script editor, choose **Project Settings** (the cog), then **Script Properties → Add script property**.
  </Step>

  <Step title="Add the key">
    Property: `SCOUTWORKS_API_KEY`. Value: the [sandbox key](/guides/sandbox) to try it, or your own key. Choose **Save script properties**.
  </Step>
</Steps>

The key lives in Script Properties, not in a cell. People you share the sheet with cannot see it.

## 3. Run it

Choose **importMembers** in the toolbar and press **Run**. Google asks for permission the first time: it needs to reach an external service and to edit this sheet. Then open the **Members** tab.

## 4. Refresh it every morning

<Steps>
  <Step title="Add a trigger">
    In the script editor, choose **Triggers** (the clock), then **Add Trigger**.
  </Step>

  <Step title="Set the timer">
    Function: `importMembers`. Event source: **Time-driven**. Type: **Day timer**. Time: **6am to 7am**. Choose **Save**.
  </Step>
</Steps>

If a refresh fails, Google emails you the error. The trigger’s **Failure notification settings** choose how often.

## 5. Switch to your group

In the script, change `api-demo.scoutworks.app` to `api.scoutworks.app`. In Script Properties, replace the sandbox key with your own. Your key needs the `members:read` scope.

## Other lists

Change the URL and the columns to read another list:

| List         | URL                                                                      | Scope               |
| ------------ | ------------------------------------------------------------------------ | ------------------- |
| Events       | `https://api-demo.scoutworks.app/api/v1/events?limit=200`                | `events:read`       |
| Badges       | `https://api-demo.scoutworks.app/api/v1/badges?limit=200&status=awarded` | `badges:read`       |
| Waiting list | `https://api-demo.scoutworks.app/api/v1/waiting-list?limit=200`          | `waiting-list:read` |

The [API reference](/api-reference/introduction) lists every field.