cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
johngabbradley
Level: Powered On

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
johngabbradley
Level: Powered On

Re: SmartSheet Source to Excel Destination

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.

 

 

6 REPLIES 6
Community Support Team
Community Support Team

Re: SmartSheet Source to Excel Destination

Hi @johngabbradley,

 

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.
johngabbradley
Level: Powered On

Re: SmartSheet Source to Excel Destination

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.

 

 

ReneAlvarezM
Level: Powered On

Re: SmartSheet Source to Excel Destination

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.

ReneAlvarezM
Level: Powered On

Re: SmartSheet Source to Excel Destination

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

trand86
Level: Powered On

Re: SmartSheet Source to Excel Destination

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?
lk581
Level: Powered On

Re: SmartSheet Source to Excel Destination

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

 

but question for @johngabbradley  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?
 

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 120 members 4,577 guests
Please welcome our newest community members: