Best Of

This post details how to track all sheet automation notifications emails. This solution is medium complexity and will likely require you to use an AI for some simple coding. Smartsheet allows users to automatically send email notifications from sheets. There are a few significant problems with these automated emails.

1. Emails come from automation@app.smartsheet.com and may get junked by the recipient.
2. There is no tracking of the emails and users cannot see what emails were sent. This makes auditing impossible.

Users can create an audit trail of automated emails and have them sent from an organization email address using Outlook and PowerAutomate Flows.

What is needed

1. Smartsheet sheet automation that sends an email.
2. Outlook Shared Email address
   1. Shared Email addresses are preferred because if staff leaves, the process will still function.
3. PowerAutomate Flow

Smartsheet automation configuration

1. Setup your automation including the "Alert someone" component.
2. Send the Alert to your Outlook Shared Email address.
3. Ensure the email subject is unique and easy for a computer to identify.
   1. PowerAutomate will identify emails for processing based on the subject.
4. Ensure the final recipient email is included in the body of the message and a way that is easy for a computer to find. In my example, the final recipient email is always after the string "Instructor email: "
   1. PowerAutomate will extract the destination email by looking for a string in the email body.

Example automation

Select the Contact column that contains your Outlook Shared Email address. The key areas are highlighted below.

Example email subject
Section Change: {{Course and section}} start date {{Section Start Date}}

Exampled email body
Hello {{Instructor 1 first name}},

I hope you are staying well.

Please note that we have been notified that the section below has been cancelled due to low enrollment. Please accept our apologies for the inconvenience.

Course section: {{Course and section}}
Section name: {{Section Name}}
Section start date: {{Section Start Date}}
Instructor email: {{Instructor 1 email}}
Talent Coordinator: {{TC}}

Kind regards
{{TC}}
Talent Coordinator

Shared Email
This stage will move all the specific automated emails from Smartsheet to an Inbox subfolder for better management and processing by the Power Automate Flow.

• In the SharedEmail Account, create an inbox subfolder for the emails.
• Create Outlook Rule to put specific emails from automation@app.smartsheet.com in an Inbox subfolder.
  • This part of the process requires using Outlook Web and specific navigation.
    • Open Outlook Web with your email account that has access to the Shared Email Account
    • Click on your icon on the top right and click "Open another mailbox" and type your Shared Email Address.

• This will open the shared mailbox where you can edit rules for that mailbox.
• Select Home > Quick Steps

• Select Mail > Rules > Add new rule

• Setup the rule
  • Name
    • Add your rule name
  • Add a condition
    • From
      • automation@app.smartsheet.com
    • Subject includes
      • Your string
      • Ex. "Course Change: "
    • Stop processing more rules can be checked or unchecked based on your needs.

PowerAutomate Flow
The PowerAutomate Flow account needs access to view emails and send emails and move emails in the shared email Account. The flow will:

1. Check each email for
   1. From automation@app.smartsheet.com
   2. Being in a specific inbox subfolder
2. Extract the recipient email from the body of the email.
3. Send and email from the shared inbox
4. Move the sent email to a subfolder of the Sent folder.

Example PowerAutomate Flow

When a new email arrives in a shared mailbox (V2)
Action: Office 365 Outlook > When a new email arrives in a shared mailbox (V2)

This step is the trigger that identifies emails that need to be processed by this flow.

Email body to text
Action: Content Conversion > Html to text

Use the lightning bolt to enter Body variable.

Extract instructor email
Action: Data Operation > Compose

This step extracts the recipient email from the body of the email. In my case I am using the string "Instructor email: " before the email and after "talent coordinator:" so the code can find the email.

Use the Expression button to enter the code. You may need to use an AI to adjust the code to your needs.

Code

replace(

substring(

  body('Email_body_to_text'),

  add(

    indexOf(

      toLower(body('Email_body_to_text')),

      'instructor email: '

    ),

    length('Instructor email: ')

  ),

  sub(

    indexOf(

      toLower(body('Email_body_to_text')),

      'talent coordinator:'

    ),

    add(

      indexOf(

        toLower(body('Email_body_to_text')),

               'instructor email: '

      ),

      length('Instructor email: ')

    )

  )

)

,decodeUriComponent('%0A'),'')

Send an email from a shared mailbox (V2)
Action: Office 365 Outlook > Send an email from a shared mailbox (V2)

This step will send the email.

Below are screenshots showing to use the lightning bolt to get the correct variables.

To

Subject

Body

Delay
Action: Schedule > Delay

This step allows the email to finish sending before trying to move the sent email to a storage folder. If the delay is not used, the sent email is not moved because it does not exist.

Get emails (V3)
Action: Office 365 Outlook < Get emails (V3)

This step finds the email that was just sent by:

• Matching the subject
• looking in the Sent folder

Below are screenshots showing to use the lightning bolt to get the correct variables.

To or CC

Subject Filter

For each
Action: None. This will be automatically added then you add the "Move Email (V2)" action.

This step runs the "Move Email (V2)" for every email found in the Get emails (V3) step.

Move Email (V2)
Action: Office 365 Outlook > Move Email (V2)

This step moves the sent email to a subfolder for better organizing and ease for auditting.
Add this action will automatically create the "For each" loop.

Below are screenshots showing to use the lightning bolt to get the correct variables.

Message Id

When you save and refresh the flow, the Message Id icon will change to the below.

Summary of results

This setup allows you to have Smartsheet notification emails that are:

• Sent from your business email
• Provide an audit trail of automated email history

Have a good day and I hope this is useful.

Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology

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!