Manually Initiate Multiple Data Shuttles with One Click

M Johnston

I have 8 daily(ish) data shuttles that I'd like to orderly initiate with just a single click. Is that possible? Trying to eliminate the possibility of one being missed or clicked out of turn… they cannot be put on a timer of sorts and they have to be manually initiated in the particular order 1-8.

Thx!

Brian_Richardson

It depends - if you're doing Offload Data Shuttles (exports) then no, there isn't a way to trigger those.

If you're doing Upload Data Shuttles though, you can trigger those on Attachment. You would need to attach the files to a Smartsheet to trigger the workflow, perhaps through a form submission or a manual upload.

You can also setup an Offload Data Shuttle to offload data to an attachment, which can trigger an Upload. However the Upload will read the Offloaded attachment, you cannot trigger from one file and read a different one. So it's not really possible to "cascade" the Data Shuttles easily.

Depending on what you're doing, you also might look at Data Mesh, using it with a reduced set of uploaded data to take that data and separate or manipulate it instead of loading 8 pieces.

Sorry for being vague - if you want to post more detail about your data and your workflows, there might be some other suggestions from the community.

M Johnston

Hey Brian - thanks. These are all uploads from Excel to Smartsheet… 3 of them are just to clear files by bringing up blank Excel's. The others populate about 4k lines of Excel detail each that is dispensed to other files through workflows.

Brian_Richardson

Sorry it's really not possible to cascade through a set of Data Shuttles, it's not "triggerable" except by attachment, but it reads the attachments as the data. So if you can find a way to somehow attach the Excel files in sequence, that would give you the sequential runs without having to go into Data Shuttle to run them.

Although I don't know much about your data flow, you might be successful if you revise it a little to remove the reliance on sequential Data Shuttles.

- If you use Replace instead of Update, you will not need to upload blank files to clear sheets

- If you import each Excel into it's own sheet, you will eliminate the need to run sequential uploads

- You could use Data Mesh to take each uploaded sheet and merge them. Use "Copy and Add" as the option for Data Mesh, and it will build a consolidated sheet, adding missing rows as it goes. You can trigger Data Mesh to "Update Immediately", which will fire it off as soon as your Data Shuttles do their uploads, and will fire it multiple times as the various uploads finish, ensuring that you end up with a consistent and comprehensive merged set of data.

- Data Mesh can also source data from one sheet to up to 5 other sheets in one flow. So for example if one of your Data Shuttles is bringing in a key value like a part number, you can distribute that to 5 separate sheets as needed. And there's no limit on multiple Data Mesh workflows working on the same sheet.

- Also not obvious is the reverse, you can Data Mesh from the same sheet to itself.

Hopefully, with all those options, you can find a way to rework your flow. If you don't have Data Mesh as an option, you can likely "poor man" it by using Copy Row automations, it's just not as elegant or easy to do.

M Johnston

Hi Brian - thank you.
When creating workflows from the uploaded detail sheets, we only have the options to move or copy (dispense) to multiple summary files… not replace… which is why the summary files need to be purged. For the large files, I do use the Data Shuttle replace option so they're not ones that I 'blank out'. It will be far easier for the exec to just hit 7 Data Shuttle runs than to attach files. Appreciate the help!

M Johnston

I think my best bet is to go ahead and clear the work files automatically each day early am with a clear cell workflow on each column and scrap half of the shuttles. It is unlikely they will run the uploads more than once per day, more like every-other day.

Thanks again!

M Johnston

Never mind that last one… we can't set an hourly, daily, or any other period to clear cells/columns. Ugh… Would have been nice but back to the drawing board.

Brian_Richardson

Easiest way to schedule a clear is to do a move automation to another sheet and periodically wipe that sheet.

If you are trying to partially clear the sheet (certain columns only) then you can have a data shuttle Offload the data to an attachment on the schedule you want and include a column in your Smartsheet that is blank. Then setup an Upload workflow to trigger on attachment and import the blank column into all the columns you want to clear, leaving the others intact.

If you have Bridge you can also do clears on a detailed schedule exactly how you’d like it.

M Johnston

Yeah, just trying to clear work files / sheets fully… I'd thought of moving to another sheet but with 4K rows each day we might as well continue with the Data Shuttles. I do appreciate the feedback and confirmation.
Thx !

Brian_Richardson

Yes sheet clearing is a PITA. There's a Product Feedback item for delete rows automation that you might want to vote. Lots of ways in Smartsheet to ADD data, but almost no way to DELETE data.

The only other tip I can offer is that, if you can find a way to shift most of your data under a parent row, deleting or moving the parent will delete/move all the children in one step. Unfortunately though there's not a way to have Data Shuttle create those parent/child relationships. But it won't remove them either, so if you can preset your sheet that way, it might save some time. Or not, depending :-)

The fastest and most reliable method I've found is exactly what you're doing, upload a blank sheet with Data Shuttle.

If you have Bridge, I have an article on clearing sheets using Bridge, with an update at the end discussing the parent-child delete method.

https://community.smartsheet.com/discussion/104848/clear-a-sheet-using-bridge

M Johnston

Thanks my new friend… and I can appreciate that they Smartsheet protects the deletion of data.

Brian_Richardson

haha back at you