When a new row is created in smartsheet get the data from that new row and use it in Power Automate

Alex Herrity · 02/02/21

I have a Smartsheet sheet which is populated using a Smartsheet form. When the form is submitted it creates a new row in the Smartsheet and inserts the relevant form data into the corresponding Smartsheet columns.

I now want to use the data from that row in a flow in Power Automate. I am using the "When a new row is created" trigger but I am struggling to then access/use the data in the columns of that new row.

For example one of the values is User Department Name - I want to take the value from that column for the newly inserted row and use it in Power Automate email or updating a sharepoint list etc... etc...

Any ideas?!

Genevieve P. · 02/04/21

Hi @Alex Herrity

It sounds like this has something to do with the functionality of the Power Automate workflow. I checked the Microsoft Community and found this older post, here: Smartsheet - When new row is added. One of the solutions suggested on this post is to use the expression builder instead.

There's another post here: Add a row from Smartsheet to excel.

If neither of these have resolved your specific issue, I would recommend posting in the Microsoft Power Automate Community for more help, with screen captures if possible.

Cheers!

Genevieve

Genevieve P. · 02/04/21

Hi @Alex Herrity

It sounds like this has something to do with the functionality of the Power Automate workflow. I checked the Microsoft Community and found this older post, here: Smartsheet - When new row is added. One of the solutions suggested on this post is to use the expression builder instead.

There's another post here: Add a row from Smartsheet to excel.

If neither of these have resolved your specific issue, I would recommend posting in the Microsoft Power Automate Community for more help, with screen captures if possible.

Cheers!

Genevieve

Laurel Stokely · 03/17/22

I struggled a lot with this until I realized there is an easy formula for just extracting the data from the last row. After your trigger, you do have to use the 'Get a sheet data (dynamic schema)' action which gives you the data from the entire sheet.

After that, do a compose action and write the following formula:

last(outputs('Get_a_sheet_data_(dynamic_schema)')?['body/value']

This gives you only the relevant information in the form of an object. I like to name this "Last Row Only"

I then like to generate an array from this object by using another compose action with a simple formula:

createArray(outputs('Last_Row_Only'))

which you can then use if you want to generate an HTML table

brianw · 03/21/22

@Laurel Stokely this is what I am looking for, but when I insert the compose operation and paste in the last(outputs('Get_a_sheet_data_(dynamic_schema)')?['body/value'] I get a notification the expression is invalid.

What am I doing wrong?

Unknown · 03/23/22

This content has been removed.

Laura CB · 06/27/22

first expression is missing an end parens, should be:

last(outputs('Get_a_sheet_data_(dynamic_schema)'))?['body/value']

and watch out for smart apostrophes

leeroy · 06/29/22

First "Compose" = last(outputs('Get_a_sheet_data_(dynamic_schema)')?['body/value'])

second "Compose 2" = createArray(outputs('Compose'))

KelvinC · 07/13/22

This solution worked but I had to make a few tweaks..

the last() function expects an array, so i needed to change the object returned from 'Get_a_sheet_data_(dynamic_schema)' into an array 1st.
then i ran last() on that result.
Then i use "Parse JSON" to parse that row into objects

end result looks something like this

*Get Last Row of Sheet data function = last(outputs('Compose-convertSheetDataToArray'))*

I can then use the results from the parse as objects.

I still don't like the fact that we need to load the whole smartsheet to do this, but it seems to work. Hopefully, smartsheet can work on exposing these columns/values as objects when a new row is created.

mbsamuel6 · 04/19/23

Need some help on this one. I am trying to accomplish something similar where I am trying to simply send out an email that contains the data entered from a Smartsheet form.

So someone submits a form in Smartsheet and powerautomate takes the data from the form and sends it in an email.

When I use the smartsheet dynamic content in the "send email" action via powerautomate, powerautomate automatically places that send email action. That creates a loop so powerautomate will send multiple emails.

To avoid that I tried doing what you guys did in the thread, but I can't find a way to get the dynamic content from the smartsheet form (row data) into the emails.

Here's what I have so far:

r1.JPG

r2.JPG

@KelvinC

@leeroy

KelvinC · 04/20/23

hi @mbsamuel6 ,

Its been a while since i worked on this but maybe this will help. I couldn't tell if you created a schema for your "Compose - Get Last Row of Sheet Data" output, but I needed to do that in order to see the properties in any steps following the Parse one. Basically all of your columns in the row need to be explicitly typed in your schema for PA to use.

FYI - if you have a column that can be of type string, integer or null make sure you put all three as the options for type or you'll get an error when the flow runs into that value.

mbsamuel6 · 04/20/23

@KelvinC thank you so much for the response.

What you're saying makes sense and it solved my issue.

One thing I noticed I did wrong was my code in JSON was wrong as you mentioned.

So I simply looked at my outputs created by the Compose action "Get Last Row of Sheet Data" and copied that into the JSON and then created a Sample code. Now all my values from the JSON are appearing in the email.

Just in case anyone needs to see the code for the compose actions:

1) Convert Sheet Data into Array- "value" (Smartsheet dynamic content named value)

2) Get Last Row of Sheet Data - last(outputs('Get_a_sheet_data_(dynamic_schema)')?['body/value'])

oflores · 05/04/23

So recentely came across this forum and followed the step to create a flow in power automate. However, in my testing I found that the Compose-Convertsheetdatatoanarray is irrelevant. I get the same results in the end but I am skipping the portion that grab all the smartsheet data and just grabs the last record (see the picture). Am I wrong in thinking this?

Jaime M. · 07/07/23

Does anyone have a solution for getting data from the new row added, which may not always be the last row, if multiple rows are added at a time? Each row has a unique identifier.

mbsamuel6 · 07/10/23

@Jaime M.

I had the same issue. I found one work around. When a new form is submitted, the data from that form is entered in the top (first) row by default.

You can change this to make it appear in the bottom row in the form settings. That should help.

So now that compose2 action that looks at the last row will focus on the data from new forms submitted.

Let me know if that makes sense

Jaime M. · 07/10/23

Hey!

Yes, that makes sense. The only problem is when multiple requests hit the sheet at the same time, and power automate only looks at the last row instead of the 5 new rows, it sends 5 emails based on the same row of data.

I need a way to identify the specific new row it should be referencing (by unique row ID?) rather than the "last" row, since we will have a lot of requests being processed simultaneously.

RSteele · 08/12/23

@Jaime M.

I am having the same problem. I get multiple form entries at the same time. Ive thought of trying to delay the row being added to the sheet, but that isnt possible. hopwfully we find a solution soon!

When a new row is created in smartsheet get the data from that new row and use it in Power Automate

Best Answer

Answers

Categories