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

8 REPLIES 8
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

 

 

CCarrill
Regular Visitor

Also looking for something similar. Has anyone been able to get this setup to work fully? 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,656)