Controlling record visibility in reports using Smartsheet Groups

Neil Egsgard

This solution allows you to combine the "Current User" filter and Smartsheet Group membership to filter records based on who is a member of a Smartsheet Group.

Solution overview

1. Python
   1. Create scheduled task to run daily Python script that pulls all Smartsheet groups and group members and saves as CSV on SharePoint site.
2. DataShuttle
   1. Schedule DataShuttle to load "Group-Member" CSV into "Groups" DataTable
3. DataTable
   1. Create DataTable Connection to load "Groups" DataTable data into "Group-Members" sheet.
4. Group-Members sheet
   1. The "Group-Members" sheet will contain a record for each Group-Member combination. There will be one record for every member of every group.
5. DataMesh - Group names to Group Summary sheet
   1. Create DataMesh to transfer Group names from "Group-Members" sheet to "Group Summary" sheet.
6. Group Summary sheet
   1. The "Group Summary" sheet will contain a list of every group and all members. There will be one record per Group.
   2. On "Group Summary" sheet use a Join Collect formula to gather all the group member emails into the "Group members - Text" field.
      1. =IF([# of members]@row <= 30, JOIN(COLLECT({Smartsheet Group List - Member email}, {Smartsheet Group List - Group name}, [Smartsheet group name]@row ), ";"), "Too many members to display")
   3. The Group members will appear as emails separated by ";"
7. DataMesh - Convert member emails into Contacts
   1. Create a DataMesh to transfer the text group members into a Contact column called "Group members" on the same "Group summary" sheet. The text emails will be converted into Contacts.

At this point you will now have a automatically updating list of groups with their members as Contacts in Smartsheet. You can you DataMesh or cros ssheet references to pull the Group members onto other sheets using the Group name.

Tools required

• Computer to run scheduled Python query to pull Groups and Group members and save it to a SharePoint location
• SharePoint location to save the Group-Member list
• Smartsheet API access
• DataShuttle
• DataTable
• DataMesh

I hope this is useful to you.

Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology