This workflow updates rows in a sheet with the latest UPS shipment status when a new ID is added to the sheet. It also updates all non-delivered shipment statuses once per day
- Systems Used
- Workflow Triggers:
- Smartsheet - When Column Values Are Changed
- Daily Schedule
- Templates Used:
- Bridge Workflow - 'Daily UPS Shipment Updates'
Steps to complete:
- Deploy the template
- Set up the trigger
- Define the schedule
- Start the schedule
Deploy the template
In the Solution Center find the template Daily UPS Shipment Updates in the list and click on it.
Give your workflow a name and click Deploy. Your new workflow is now available in the list of Workflows but first you need to set up a trigger.
Set up the trigger
Navigate to the Integrations page from the left hand side menu. Scroll to find the Smartsheet logo and click on it.
In the pop-up, click Continue in the first tab and Authenticate in the second to authenticate the Bridge/Smartsheet integration, if you haven't already. A new window will open asking you to give Bridge permission to access your Smartsheet account. Click Allow to do so. The window will close on its own in 5 seconds if authentication was successful.
In the third tab of the pop-up expand the Triggers section, and click on Add to triggers.
Enter the ID or unique name of the sheet where you keep your UPS tracking IDs. Select the Event Type When Column Values Are Changed.
This reveals another field where you can enter the name of the column you want should trigger workflows. The column name should be the one where you enter tracking IDs in the sheet. If you are using a sheet as described at the end of this article, type UPS Tracking ID into this field.
In the dropdown select the workflow that contains the name you gave the template and Daily Shipment Status Updates.
Click Save to save the trigger and close the pop up when it has reloaded.
The Daily Shipment Status Updates will now run when you add a new tracking ID to the sheet.
Define the schedule
To also make the workflow update shipment statuses on a daily basis you need to define the schedule this should happen on.
Navigate to Workflows in the left hand side menu and open the 'All' tab at the top of the dashboard that appears. Find the workflow that contains the name you gave the template and Start Daily Schedule and open it.
Click on the module System Tools: Schedule Conversation.
In the Template field, enter the full name of the workflow that contains Daily Shipment Status Updates.
Next, expand the section called Entity List. You should see a row with the field Key #1 already set to SheetID and the Value #1 field empty. In the empty value field, enter the ID of your sheet.
You can find the sheet ID by navigating to your sheet and selecting Properties in the File dropdown.
Finally, you need to set a time you want the schedule to start. This will also determine the time of day your schedule runs once started. This needs to be set in unix time. You can use this free online converter to find the timestamp you need: https://www.epochconverter.com/
Choose the time you want the workflow to run for the first time, get the unix timestamp, and enter it into the Time To Start Conversation field.
Make sure you don't pick a time that's at least a few minutes out as you might otherwise not have completed the setup before the start time passes. If this happens just get a new timestamp for another few minutes out and try again.
Start the schedule
Remember that when you start the schedule it will continue to run once per day. If you want to test it before using it continuously you can change the value in the Interval dropdown to Once. When you're satisfied it works as you expected you can change it back to Daily and start it again.
Now you are ready to kick off the schedule, which will cause the Daily Shipment Status Updates workflow to run once per day. The simplest way to do this is by triggering the Start Daily Schedule workflow manually.
Click on the trigger element at the top of the workflow to open up the panel. Expand the last section called Phrases and enter the value Start. (Make sure you don't have any other workflows in the workspace triggered via phrases using the word 'start'. If you do they will all be triggered so pick some other phrase to use.) Save the workflow.
In the top right corner, click on the speech bubble to open up a chat window. In the text field at the bottom, where it says Talk to Bridge, type in the word 'start' and press enter. When the schedule has been created a message confirming it will appear.
Now, once per day, from the time you entered earlier, the status of all tracking IDs in the sheet (where the status isn't already 'Delivered') will be updated with the latest status.
To cancel the schedule in the future you will need the schedule ID. You can find it in the Run Log by expanding System tools: Schedule Conversation and copying the JSON source of the objectUUID.
The below screenshot shows the sheet that was used when building this example. Most of the columns below are used in the workflow so if you are following this exact example make sure you have the same columns in your sheet.
There is also some sheet automation set up on the sheet to update the last column with the date of the last status update.