Generate Account User List - API, Power Query, Power BI, Dataflow

WiseMan

Sharing the solution so you won't have to navigate the challenge I just conquered!

Need: Generate a list of the Smartsheet users WITHOUT having to download the report from the admin center.

Solution: Power Query

Assumptions: You're familiar with Power Query, Power BI, Dataflows, and how the PI works.

Solve:

1. Create a new Smartsheet data source connection in Power Query, select the Smartsheet.query function.
2. Create a new blank query, go into advanced editor, use the following script:

let

  // Define the maximum number of records per page

  pageSize = 100,

  // Fetch the initial source to determine total count and total pages

  initialSource = #"Smartsheet Query"("users", "page=1", []),

  totalCount = initialSource[totalCount],

  totalPages = initialSource[totalPages],

  // Fields to check for null values and replace with appropriate values

  fieldsToCheck = {"sheetCount", "name", "email", "firstName", "lastName", "admin", "licensedSheetCreator", "groupAdmin", "resourceViewer"},

  // Function to apply null-replacement across specified fields in a record

  transformRecord = (record) =>

    let

      existingFields = List.Intersect({Record.FieldNames(record), fieldsToCheck}),

      updatedFields = List.Transform(existingFields, each

        if _ = "sheetCount" then

          {_, if Record.Field(record, _) = null then 0 else Record.Field(record, _)}

        else

          {_, if Record.Field(record, _) = null then null else Record.Field(record, _)}

      )

    in

      Record.ReorderFields(Record.Combine({record, Record.FromList(List.Transform(List.Difference(fieldsToCheck, existingFields), each if _ = "sheetCount" then 0 else null), List.Difference(fieldsToCheck, existingFields))}), fieldsToCheck),

  // Define a function to fetch data for a given page and apply the transformation

  fetchPage = (pageNum as number) as table =>

    let

      delay = Function.InvokeAfter(()=> null, #duration(0,0,0,1)), // Add a delay of 1 second

      source = #"Smartsheet Query"("users", "page=" & Text.From(pageNum), []),

      data = source[data],

      transformedData = List.Transform(data, each transformRecord(_))

    in

      Table.FromRecords(transformedData), // Convert the list of records into a table

  // Fetch all pages and combine into a single table

  allData = List.Accumulate({1..totalPages}, Table.FromRecords({}), (state, current) => Table.Combine({state, fetchPage(current)}))

in

  allData