Data Uploader - full text not loaded/unique identifier column

Martin Suchy

Hi team, can you please advise the best way how to handle below cases?

1) Smartsheet is not capturing the full text via Data uploader, it is in a format of: 28/01/2021 02:01:52 and it takes only 28/01/2021. The source sheet is stored on google drive. I know Smartsheet is not supporting time formats, but the column is set to "text" so I expected this would capture the full text. I'd like to use this as "unique identifier column" for "merge" option which is not working now.

2) Is there any way how to use formula for "unique identifier column" of "merge" option? Eg I'd like to use combination of two columns for that but I don't see any way to have this compared via data uploader. I cannot add the same into the data source, it can only be done in smartsheet, thus searching for any option how to have this covered by data uploader as well - to run concatenate in the background of data uploader to identify rows which to be uploaded into smartsheet.

Thank you

Ben Goldblatt

Hi @Martin Suchy,

For the first part of your post, I ran some tests with a Google Drive as my source location and pulled data from a Google sheet into Smartsheet via Data Uploader. With 28/01/2021 02:01:52 entered in a cell on the source file, with and without Data Validation set to recognize the value as text or a date value in Google, the initial value that pulled over for me matched in Smartsheet.

I tested with my "Start Date" as a Date column in Smartsheet and as a Text/Number column. In both instances, the date and timestamp were pulled into the cell. I did find though that if the target column is set to be a Date column, double-clicking on the cell and clicking somewhere else in the sheet caused the cell value to only display the date portion. I have a feeling this is expected behavior but you may want to reach out to the Support Team to see if they can verify this.

For the second part of your post, you should be able to concatenate values from multiple columns into one using an Expression. I tested this with a goal of populating cell values that join together my "WBS Number" value with my "Task Title". I inserted a new column into my sheet named "WBS Number + Task Title", saved the sheet, then created a brand new workflow to include this column in the "Mapping" section. Instead of mapping the column directly, I chose "</> New expression" and entered the formula =JOIN([WBS Number]@row:[Task Title]@row, " - ").

This then appeared in the "Expressions" phase of the workflow, at the bottom under the "Schedule".

I ran the new workflow and the formula results came in as expected.

I then hid my "WBS Number" and "Task Title" columns, saved the sheet, added a new row to my Google sheet, and ran my Data Uploader workflow again and this still worked to capture the results of my input expression (formula). The individual columns still mapped, even though they were hidden, since this is what the workflow was told to do.

More information on Expressions in Data Uploader workflows can be found in the Sync Smartsheet and External Systems With Data Uploader Help article from the Learning Center.

I hope this helps!

Thanks,

Ben