SmartSheet Source to Excel Destination

[Anonymous]

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:

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:  

The action does not contain outputs of the columns for the insert data into Excel like Excel does as source of data.
 

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.

[v-bacao-msft]

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:

SmartSheet:

Take a try.

Best Regards,

Barry

[Anonymous · Accepted solution]

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:

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

[ReneAlvarezM]

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]

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]

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?

[Anonymous]

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?

[JeremyPERRIN]

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]

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