Best Of

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

This is the simplest formula I can find to add Months to Date. This considers sum of months exceeding 12, converting it to January and adding 1 to the year.

Where: Term column is the number of months to add, Date is the starting date.

=DATE(
  YEAR([Date]@row) + INT((MONTH([Date]@row) + [Term]@row - 1) / 12), 
  MOD(MONTH([Date]@row) + [Term]@row - 1, 12) + 1, 
  DAY([Date]@row)
)

Posting it out here for my future use. I surely will forget this when I need it.

Hello everyone!

I spent WAY to long looking for this answer so I thought I would share it with the Community in hopes of helping someone else.

Problem: Adding attachments to rows/sheet is not possible through the standard SmartSheet Connector

Solution: This can only be done by creating a Custom Connector. It CANNOT be done through the HTTP request. As of writing this, here is the relevant information. On the

General tab

Scheme: "HTTPS"

Host: "api.smartsheet.com"

Base: "/2.0/"

Security

Authentication Method: API Key

Parameter Location: Header

The rest of the info is up to you.

Definition

Url: https://api.smartsheet.com/2.0/sheets/{sheetId}

Headers: Content-Type and Content-Disposition

Body: This must be edited in the swagger editor after creating Action. The body should look like this

" - name: file

      in: body

      required: false

      schema:

        type: string

        format: binary

"

Be sure to format it consistent with the rest of the Yaml.

The rest of the info is up to you.

Finally Update the connector. You should now be able to use this custom connector in your flows.

Your Atachtion should look like the example below. Key items, you will need to know the Content-type. If you are using ShareFile, you can get it directly from the "Get File Content" step. Just be sure to drill down from the body, ie. @{body('Get_file_content')?['$content-type']}. I used the Get File Metadata step to get the file name. You will need the file extension but not the folder path. Finally, the File MUST be in Binary. If your file is in Base64, use the following: @{base64ToBinary(body('Get_file_content')?['$content'])}

I hope this helps!