DataShuttle, DataMesh, Something Different--what's best
There is an excel report we get from another system that needs to be brought into SmartSheet on a weekly cadence. It is a timeline-driven document so we need the updates on dates, status, and new entries (rows) to be brought into SmartSheet. The report has this information for four teams, so we need the information from this exel report to be brought into four sheets (it MUST be sheets, not reports). The excel file has a column where the team name is specified.
There is information in that excel needs to be tweaked before it is ready for the end-users to see (index/match, joining multiple columns, truncating some of the text). The same tweaks are needed for every team.
I'm a bit confused on the best way to do this--do I bring in the excel file via Data Shuttle and run 4 workflows (filtered by team) into identically formatted sheets where the data is tweaked directly in the file? Is there a way to bring in the excel file via data shuttle into a master sheet where the tweaks are made and then use data mesh to bring that data into each team's sheet (and is there a way to make sure this is filtered by team)? Is there another solution I haven't considered yet?
Best Answer
-
Hello @MelissaYE
In my opinion, I prefer to use Data Shuttle.
You can run a workflow to a master file to lookup data. Then you can run another workflow on the sheet that your teams would be referencing to make the updates.
The tab called expressions, in Data Shuttle, is used to write functions and map functions to the columns.
If you are using INDEX/MATCH within Data Shuttle, the cross sheet references need to be set up in the source before you run Data Shuttle. The references are stored within the sheet and not data shuttle, which means you would need to set up the references on each sheet, but you can use the same function to use those references.
I like to write the functions within the sheet first. Make sure they work and all the references are set up. Then copy the function into the expressions of the workflow and map it. Usually works perfect if everything is set up correctly initially.
Answers
-
Hello @MelissaYE
In my opinion, I prefer to use Data Shuttle.
You can run a workflow to a master file to lookup data. Then you can run another workflow on the sheet that your teams would be referencing to make the updates.
The tab called expressions, in Data Shuttle, is used to write functions and map functions to the columns.
If you are using INDEX/MATCH within Data Shuttle, the cross sheet references need to be set up in the source before you run Data Shuttle. The references are stored within the sheet and not data shuttle, which means you would need to set up the references on each sheet, but you can use the same function to use those references.
I like to write the functions within the sheet first. Make sure they work and all the references are set up. Then copy the function into the expressions of the workflow and map it. Usually works perfect if everything is set up correctly initially.
