--- Introduce Yourself
--- Answer the Question of the Month
--- Connect with your Peers
Generate Account User List - API, Power Query, Power BI, Dataflow
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:
- Create a new Smartsheet data source connection in Power Query, select the Smartsheet.query function.
- 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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives