cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

SmartSheet Source to Excel Destination

Hello,

I am working on a flow that triggers when a SmartSheet is updated.  I want to pull data from the sheet when a change is made.  Here is the Flow as of now:FlowImage.PNG

 

I want to use SmartSheet as a source to pull data into a SQL destination but just experimenting with Excel right now to get the mechanics working.

The SmartSheet "Get a Sheet" action however does not provide the column names in the Dynamic Content as it does with many other actions.  I am hoping that I can use the Workflow Definition Language to create the column references to be used in the destination.  

As I have been researching, I saw the outputs of the "Get a Sheet" action was "Sheet HTML" as shown below:  Get a Sheet Outputs.PNGGet a Sheet Outputs 1.PNG

The action does not contain outputs of the columns for the insert data into Excel like Excel does as source of data.
Excel Example Outputs.PNGExcel Example Outputs 1.PNG 

Can I use the Workflow Definition Language to pull this out of the Sheet HTML output to create a framework to pull the data?  I have seen some examples of using Initialize Variables and then using WDL expressions to populate the variables that were created but I haven't found a methodology that I can get to work.  The WDL language still feels very foreign to me.  How can I write expressions to get the columns to use in a later action?  I have been unable to find a solution querying Google, unfortunately.  I hope some one has solved this or seen something similar.  This seems like it should be documented, but if it is I haven't found it yet.

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

I was able to figure out a way to get this to work with the following solution.  Putting out there in case it helps any one else.

 

Here is the shape of the flow:
image.png

 

image.png

 

image.png

By using the Switch function I can assign the values to a variable which I can use to  then push to my Destination.

 

 

View solution in original post

7 REPLIES 7
v-bacao-msft
Community Support
Community Support

Hi @Anonymous,

 

You could get all columns related information by using Get columns of a sheet.

Use Apply_to_each to get the title of the column, then you could use an array to store the name of these columns.

In the subsequent action configuration, you could use these columns by traversing the array.

Image reference:

50.PNG

 

SmartSheet:

52.PNG

51.PNG

Take a try.

 

Best Regards,

Barry

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I was able to figure out a way to get this to work with the following solution.  Putting out there in case it helps any one else.

 

Here is the shape of the flow:
image.png

 

image.png

 

image.png

By using the Switch function I can assign the values to a variable which I can use to  then push to my Destination.

 

 

View solution in original post

Can you elaborate a bit more? I'm trying to follow your solution but I do not see where are you reading from SmartSheet. Also, that HTTP action stands for... what?

 

Thanks.

I was able to replicate this flow, however, I'm struggling with how to get the actual values for a row. Can you help me with that?

 

Thanks

Hey did you manage to get any cell data from the update row trigger? I’m also trying to do this. I can separate out column data into variables and selecting but really need the actual cell data to push into presentation. Any luck?

for those looking for help on the Http step = https://smartsheet-platform.github.io/api-docs/

 

but question for @Anonymous  After the HTTP step, i only have "body" as a value to choose - not "rows" - and i'm getting an error :

ExpressionEvaluationFailed. The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('HTTP')' is of type 'Object'. The result must be a valid array.

 
Is there a setting in the http request i missed?  could you possibly post a screen shot of that element?
 

Hi, sorry to re open this discussion. But i tried to apply your template. And i got the output.

But how could i copy all the content column to the excel file ?

Could you show me  sample ? Thank you very much

My goal is to copy the full content of a smartsheet to excel O365 thank you very much your help

 

 

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Users online (74,991)