Smartsheet data in Excel via Power Query

Question

The API output from Smartsheet returns rows and columns as separate objects,that are independent of each other. This results in separate records for the columns(A list of field names) and another set of records for the rows(records with a single field of values from various fields)

Is there a way to return a single list of JSON (with rows and columns resulting in a single list of records)?

This is the code I'm using in the Query Editor that returns separate Rows and Columns

= Web.Contents(
    "https://api.smartsheet.com/1.1/sheet/[SHEET_ID]",
    [
        Headers = 
        [
            #"Authorization" = "Bearer YOUR_API_TOKEN"
        ]
    ]
)

On a related note, let me also point out that figuring out how to connect to an authenticated REST API service from PQ was non-trivial. Here is how I got it to work: (1) in PQ editor I hardcoded the auth header + API token: = Web.Contents(“api.smartsheet.com/1.1/sheet/4693183612381060”, [ Headers=[#“Authorization” = “Bearer YOUR_API_TOKEN”]]) (2) when prompted to enter credentials, I chose Anonymous instead of Web API, as someone suggested in the comments here powerpivotblog.nl/… — avioing
Ivan, Can you provide an example of what you would like the output to look like? — Brett

Curt Hagenlocher Curt Hagenlocher · Accepted Answer · 2015-02-08T09:46:04

I used the sample data on their site to come up with this set of transformations:

let
    Source = Json.Document(File.Contents("D:\testdata\foo.json")),
    ColumnIds = List.Transform(Source[columns], each Text.From([id])),
    ColumnNames = List.Transform(Source[columns], each [title]),
    Table = Table.FromList(Source[rows], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1", {"rowNumber", "cells"}, {"rowNumber", "cells"}),
    Mapped = Table.TransformColumns(Expanded, {"cells",
        each Record.Combine(List.Transform(_, each Record.AddField([], Text.From([columnId]), [value])))}),
    Result = Table.ExpandRecordColumn(Mapped, "cells", ColumnIds, ColumnNames)
in
    Result

Smartsheet data in Excel via Power Query

1 Answers