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

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`
Badges	`https://api-demo.scoutworks.app/api/v1/badges`	`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

How the API works

Tools

Quick version: the first 200 events

Full version: every event, every page

Switch to your group

Other lists

​Quick version: the first 200 events

​Full version: every event, every page

​Switch to your group

​Other lists

Quick version: the first 200 events

Full version: every event, every page

Switch to your group

Other lists