Data Shuttle: Target sheet is empty but all rows are "unaffected" after running workflow

Janae G.

Hello,

I am new to Data Shuttle and creating my first Upload Data workflow. My target sheet does not have any data in it: everything is blank (it is a new sheet). When I run the workflow however, the run details say that there are 77 rows unaffected (as in there are 77 rows where data already exists and is consistent with the source). There are supposed to be 69 rows, plus Smartsheet had been counting 8 blank rows at the bottom for some reason. No rows were added or updated.

Why is this happening? Does there need to be data already in the target sheet for the workflow to add anything? Why does it say there are 77 rows unaffected when the target sheet doesn't have any data?

Kerry St. Thomas

This is largely conjecture on my part, and I haven't found any documentation from Smartsheet about this. But… it's based on me seeing a similar thing happen with my own stuff. If you're trying to set up a Data Shuttle using the "Merge data onto the target based on a key column value"… <blank> value in the key column could be causing the issue. Blank exists both in Smartsheet and in your source sheet… so apparently you confused Data Shuttle and broke the internet. 😀

The solution that I've used is to set up the Data Shuttle to "Replace all Target sheet rows with data from the input file" and then run it. Then reconfigure the Data Shuttle to "Merge…" now that your key column contains actual values. To test it, delete some (BUT NOT ALL) of the rows from your target sheet, delete all the blank rows in your source sheet (even though there's probably NOT anything in those rows this is a "just in case"), and then rerun your workflow.

Good luck!

Janae G.

Thanks for your help! I'll keep that in mind when I have blank rows or any issues regarding them. Changing it to Replace worked, however after deleting a few rows and the blank rows and changing it back to add and update, none of the removed rows were added. I am wondering if it has to do with my key column? I don't have a true primary column in my source data, so I tried creating one by creating an expression in the workflow to create a row ID column (that way it saves the step of creating a row ID in the source file, which is in Excel). It is basically a concatenate/join function that creates a compound primary key from three columns.

Will the data shuttle recognize the created expression as the unique identifier? Or does Smartsheet not let you use an expression as the unique identifier for adding/updating rows?

Humashankar

Great Insight