Events in Excel

This recipe loads your events into an Excel table with Power Query. It works in Excel for Microsoft 365 and Excel 2016 or later on Windows, and in Excel for Microsoft 365 on Mac.

Quick version: the first 200 events

Start a web query

Choose Data → Get Data → From Other Sources → From Web. Choose Advanced.

Enter the address

In URL parts, enter:

https://api-demo.scoutworks.app/api/v1/events?limit=200

Add the key

In HTTP request header parameters, type Authorization in the first box. In the second box, type Bearer followed by the sandbox key:

Bearer sw_test_473adcc6c7236916021f0f24559db36576a53bab071d0f0c08a025adb510b6ab

Choose OK.

Choose Anonymous

If Excel asks how to connect, choose Anonymous, then Connect. The key in the header does the signing in.

Turn the list into a table

In the Power Query editor, select data: List, then To Table, then the expand button in the column header. Choose the columns you want. Choose Close & Load.

To refresh, choose Data → Refresh All.

Full version: every event, every page

A list comes back 200 rows at a time. To load all of them, paste this into a blank query. Choose Data → Get Data → From Other Sources → Blank Query, then Advanced Editor.

Power Query (M)

let
    ApiKey = "sw_test_473adcc6c7236916021f0f24559db36576a53bab071d0f0c08a025adb510b6ab",
    GetPage = (cursor as nullable text) =>
        Json.Document(
            Web.Contents(
                "https://api-demo.scoutworks.app/api/v1/events",
                [
                    Query = if cursor = null then [limit = "200"] else [limit = "200", cursor = cursor],
                    Headers = [Authorization = "Bearer " & ApiKey]
                ]
            )
        ),
    Pages = List.Generate(
        () => GetPage(null),
        each _ <> null,
        each if [nextCursor] = null then null else GetPage([nextCursor]),
        each [data]
    ),
    Events = List.Combine(Pages),
    Table = Table.FromRecords(
        Events,
        {"title", "type", "startsAt", "endsAt", "location", "hosted", "attendeeCount"},
        MissingField.UseNull
    )
in
    Table

Choose Done, then Close & Load.

Only the events that changed this week

Power Query does not keep anything between refreshes, so it cannot remember the last sync. It can still ask for a short list: the events that changed in the last 7 days. Paste this into a second blank query.

Power Query (M)

let
    ApiKey = "sw_test_473adcc6c7236916021f0f24559db36576a53bab071d0f0c08a025adb510b6ab", // gitleaks:allow (the public sandbox key, see /guides/sandbox)
    Since = DateTimeZone.ToText(DateTimeZone.UtcNow() - #duration(7, 0, 0, 0), "yyyy-MM-ddTHH:mm:ssZ"),
    GetPage = (cursor as nullable text) =>
        Json.Document(
            Web.Contents(
                "https://api-demo.scoutworks.app/api/v1/events",
                [
                    Query = [limit = "200", updatedSince = Since] & (if cursor = null then [] else [cursor = cursor]),
                    Headers = [Authorization = "Bearer " & ApiKey]
                ]
            )
        ),
    Pages = List.Generate(
        () => GetPage(null),
        each _ <> null,
        each if [nextCursor] = null then null else GetPage([nextCursor]),
        each [data]
    ),
    Changed = Table.FromRecords(
        List.Combine(Pages),
        {"id", "title", "status", "startsAt"},
        MissingField.UseNull
    )
in
    Changed

Each row is one event that changed. A deleted or archived event is not in it: refresh the full query above at least once a week to see what was removed. To keep a full copy up to date from one refresh to the next, use the Google Sheets or Python recipe, which can remember where they stopped.

Switch to your group

Change api-demo.scoutworks.app to api.scoutworks.app, and replace the sandbox key with your own. Your key needs the events:read scope.

Excel saves the key inside the workbook. Anyone with a copy of the file can read it. Keep the workbook private, and create a key with only the scope it needs. If the file is shared by mistake, revoke the key in Scoutworks.

Other lists

Change the address in Web.Contents and the column names in Table.FromRecords:

List	Address	Scope
Members	`https://api-demo.scoutworks.app/api/v1/members`	`members:read`
Sections	`https://api-demo.scoutworks.app/api/v1/sections`	`members:read`
Badges	`https://api-demo.scoutworks.app/api/v1/badges`	`badges:read`
Badge catalogue	`https://api-demo.scoutworks.app/api/v1/badge-catalogue`	`badges:read`
Waiting list	`https://api-demo.scoutworks.app/api/v1/waiting-list`	`waiting-list:read`

The API reference lists every field.

Start here

Recipes for leaders

Recipes by tool

How the API works

Tools

Quick version: the first 200 events

Full version: every event, every page

Only the events that changed this week

Switch to your group

Other lists

​Quick version: the first 200 events

​Full version: every event, every page

​Only the events that changed this week

​Switch to your group

​Other lists

Quick version: the first 200 events

Full version: every event, every page

Only the events that changed this week

Switch to your group

Other lists