How to use Data Shuttle Expressions

Josue RQ

Hello everybody,

I hope you are well.

Could you please help me understand how to use Expressions in Data Shuttle? I have watched the webinars, and help pages and I can't figure it out.

I don't have a use case specifically but let us say I would like to add an expression that looks at a date in the origin file and then provides a year out of that. I know how to use formulas to accomplish that but I think there is a way to do that through expressions in Data Shuttle.

Appreciate any help you can provide.

Cheers!

Mike TV

@Josue RQ

When creating an expression, Unique Name is the column name you want to put it in. Value is what you want to end up there, which could be a formula.

What I'm not certain of is whether the target sheet has to already have a column of that name or if it will add it if it's the first time you're running the Data Shuttle workflow. You'd have to test that out.

I don't think there's too much else to know about Expressions because there's not much else you can do with it that I'm aware of.

Mike TV

@Josue RQ

When creating an expression, Unique Name is the column name you want to put it in. Value is what you want to end up there, which could be a formula.

What I'm not certain of is whether the target sheet has to already have a column of that name or if it will add it if it's the first time you're running the Data Shuttle workflow. You'd have to test that out.

I don't think there's too much else to know about Expressions because there's not much else you can do with it that I'm aware of.

Mike TV

@Josue RQ

I just read a post from someone that said you have to first create the column on your target sheet before creating an expression to go there. Hope that helps.

Josue RQ

Thank you, Mike! I just figured it out and what you said helped a lot!

For anyone out there with the same question:

1. Create a column where the formula will be mapped to. For example if you want to get TODAY() create a Date Column.
2. Save your sheet.
3. When building the Data Shuttle, map everything and leave the new column unmapped.
4. When you get to Expressions use the Unique Name as an identifier so then you can map the unmapped column to that expression. So in my case, it was the first image below.
5. Then head back to Mapping and in the Source Fields at the bottom, your unique identifier will show up and you can map.
6. Continue to build the automation.
7. Run it, it will work.

IMAGE 1

IMAGE 2

I wish this was more clear in the training and webinars, as it was really intense to figure it out lol

Thank you @Mike TV for the help!

Kayla Q

@Mike TV why is this any better than just writing the function directly in the sheet column?

Matthewrh

@Kayla Q my understanding is that it will process the input expression for each time the datashuttle import process runs, so if you are adding data on a periodic basis by updating attachments via the API for instance whenever the datashuttle runs it will add Today() into the today's date column along with your new data elsewhere.

I personally use it for flagging data sources in a large spreadsheet that I have for gathering alerting data from numerous systems, the report is generated by my internal scripting as a CSV which is attached to my data import sheet which triggers the datashuttle workflow.

Each workflow has an input expression in it that fills in a column in my main working sheet that tells me the source of that data, and which system generated it.

Kayla Q

@Mike TV that makes sense, thanks!