Excel & Power Query

Power Query connects Excel directly to the LightPane API. Click Refresh All and your spreadsheet updates with live cloud resource data. No macros, no add-ins, no coding required.

Requirements:

• Excel 2016 or later (Windows or Mac), or Excel for Microsoft 365
• A LightPane API key (csl_ak_ prefix) — create one here

Step 1: Create a Config sheet

Set up a configuration table that all queries read from.

1. Open a new Excel workbook
2. Rename the first sheet to Config
3. In cells A1:B4, enter:

	A	B
1	Setting	Value
2	API URL	https://api.lightpane.cloud/services/discover
3	Bearer Token	csl_ak_YOUR_API_KEY_HERE
4	Region	eu-west-2

1. Select A1:B4 and insert a table (Insert > Table or ++ctrl+t++)
2. Name the table Config (in the Table Design tab)

All queries reference this named table. To change region or replace an expired token, update the Config sheet and refresh.

Step 2: Add a service query

Each query is self-contained. Paste it into a blank query and it loads as its own sheet.

1. Go to Data > Get Data > From Other Sources > Blank Query
2. Click Advanced Editor (in the Home tab)
3. Delete everything in the editor
4. Paste the query below
5. Click Done
6. Rename the query (e.g. "EC2_Instances") in the Query Settings panel
7. Click Close & Load

EC2 Instances

EC2_Instances.pq

let
    Config = Excel.CurrentWorkbook(){[Name="Config"]}[Content],
    ApiUrl = Config{0}[Value],
    Token = Config{1}[Value],
    Region = Config{2}[Value],

    FullUrl = ApiUrl
        & "?service_type=ec2"
        & "&attributes=name,instance_id,instance_type,state,private_ip,launch_time"
        & "&region=" & Uri.EscapeDataString(Region)
        & "&format=json",

    Response = Web.Contents(FullUrl, [
        Headers = [
            #"Authorization" = "Bearer " & Token
        ],
        ManualStatusHandling = {400, 401, 403, 429, 500}
    ]),

    JsonResponse = Json.Document(Response),
    Success = try JsonResponse[success] otherwise false,
    Resources = if Success then JsonResponse[resources] else error JsonResponse[error],
    Columns = if Success then JsonResponse[columns] else {},
    ResourceTable = Table.FromRecords(Resources, null, MissingField.UseNull),
    ColumnRenames = List.Transform(
        List.Select(Columns, each _[id] <> "tags"),
        each {_[id], _[label]}
    ),
    RenamedTable = List.Accumulate(
        ColumnRenames, ResourceTable,
        (tbl, pair) =>
            if Table.HasColumns(tbl, {pair{0}})
            then Table.RenameColumns(tbl, {{pair{0}, pair{1}}})
            else tbl
    ),
    WithoutTags = if Table.HasColumns(RenamedTable, {"tags"})
                  then Table.RemoveColumns(RenamedTable, {"tags"})
                  else RenamedTable
in
    WithoutTags

S3 Buckets

Use the same pattern. Change service_type=ec2 to service_type=s3 and update the attributes parameter:

& "?service_type=s3"
& "&attributes=bucket_name,creation_date,region"

Other services

Replace the service_type and attributes for any supported service:

Service	service_type	Example attributes
EC2 Instances	ec2	name,instance_id,instance_type,state,private_ip
S3 Buckets	s3	bucket_name,creation_date,region
VPCs	vpcs	name,vpc_id,cidr_block,state
Security Groups	security_groups	name,group_id,group_name,vpc_id
Lambda Functions	lambda_functions	name,runtime,memory,timeout,last_modified
RDS Instances	rds_instances	name,engine,db_instance_class,status

See the Service Catalogue for the complete list.

Step 3: Refresh data

• Refresh all sheets: ++ctrl+alt+f5++ (Windows) or ++cmd+option+f5++ (Mac)
• Refresh one sheet: Right-click the table and select Refresh

First-time prompts

Privacy levels

Excel may prompt about data privacy levels on the first refresh. Select Ignore Privacy Levels or set the data source to Organizational.

Credentials

If Excel asks about credentials for the web request, select Anonymous. The bearer token is already included in the request header.

Change configuration

Task	Action
Switch AWS region	Update cell B4 on the Config sheet, then Refresh All
Replace an expired token	Update cell B3 on the Config sheet, then Refresh All
Add a new service	Create a new blank query with the service's service_type and attributes

Troubleshooting

"An on-premises data gateway is required to connect"

The query combines two data sources (the Config table and the web API). Power Query's privacy firewall blocks this by default.

MacWindows

1. Open the Power Query Editor
2. Click Options (Home tab)
3. Select Project options
4. Check Allow combining data from multiple sources
5. Click OK and retry

1. Go to File > Options > Query Options > Privacy
2. Select Ignore the Privacy Levels
3. Click OK and retry

"Invalid or expired access token"

The bearer token in the Config sheet is incorrect or expired. Check that:

• The token was copied correctly (no extra spaces or line breaks)
• The token has not expired — check its status in Access Keys on your account

Empty table with no errors

The API returned zero resources. This is normal if the region has no resources of that type.

Auto-refresh

Right-click a query in the Queries & Connections panel, select Properties, and enable Refresh every N minutes. Be mindful of your key's rate_limit_rpm if you set short intervals across many queries.