Start a Blank Workflow for US Weather Service

This guide will take you through the steps to create a simplified version of the workflow that is available in the template Get the Forecast with US Weather Service.

The workflow triggers when a checkbox is checked in a sheet in Smartsheet. It uses coordinates from that row to get the latest weather forecast from the US Weather Service and then populates the same row in Smartsheet with that data. 

You will be using the US Weather Service to get forecasts since you do not need an account to access their weather data. 

This workflow does not require access to any systems other than Bridge and Smartsheet which is why it contains the workflow element HTTP Call Utility Function. 

To get the forecast you will make two HTTP Calls to the US Weather Service: one to locate the grid point a geographical location is in, and a second to get the forecast for that identified grid. You do not need to be comfortable with HTTP Calls to follow the steps in this guide as it will provide everything you need to complete the workflow. 

These are the steps to build the workflow:

• Create the workflow
• Create the sheet
• Authenticate with Smartsheet and set up the trigger
• Set up Get Row
• Set up the junction
• Set up the first HTTP Call
• Set up the second HTTP Call
• Set up Update Row

Create the workflow

In Bridge, click on the + from the Workflows dashboard. Give your workflow a name and select Create New. This will take you to the Designer.

NOTE: All workflows start with a trigger and a state.

Create the sheet

Before setting up the trigger in Bridge, you need a sheet in Smartsheet that has at least five columns. 

Set up your sheet to have the following column names and types:

• Date (Column Type: Date)
• Today (Column Type: Checkbox)
• Longitude (Column Type: Text)
• Latitude (Column Type: Text)
• Weather Forecast (Column Type: Text)

The Weather Forecast column can either be your Primary Column or a regular Text column. 

1. In the first cell of the Today column, paste the following formula:

=IF(Date@row = TODAY(), true, false)

2. Drag-fill to copy the formula into at least the second row to ensure subsequent rows automatically inherit the formula.
   This will check the checkbox if the date set in the Date column is today's date, which you can then use to trigger your workflow in Bridge.
3. Before going back to the designer in Bridge, copy the Sheet ID. You can find the sheet ID by selecting File > Properties.

Set up the trigger

In the designer, click on the Trigger element at the top of the workflow. This opens a panel on the right hand side of the designer where you can set up the type of trigger you want to use. Since the workflow should trigger by an event in Smartsheet, the type of trigger used is an Integration Trigger.

In the panel, click on the link that says Integrations Page under the automatically expanded section Integrations.

This will open a new tab with the Integrations page. 

1. Find Smartsheet and click on the logo. 
2. Select Continue in the first tab of the new window. 
3. Select Authenticate in the second tab.
4. This brings up another window asking if you want to allow Bridge Access to your Smartsheet account. Select Allow.

If the authentication was successful, the window changes to the Triggers tab.

Expand the Triggers section and click on the row with the plus icon (+):

1. In the Sheet ID field, paste the sheet ID you copied when you created the sheet. If you know the sheet name is unique you can also type in the name.
2. In the Event Type dropdown list, select When Column Values Are Changed.
3. In the field that is added when you select the Event Type, type in the column name Today.
4. Finally, in the dropdown Select Workflow list, choose the workflow you created earlier and select Save.
   NOTE: You will know the trigger was set up successfully because the pop-up will reload and collapse the Triggers section.
5. Close the tab and go back to the designer.

Errors when saving the trigger

If you receive an error message when saving a trigger, review the following items:

• Make sure you spelled the column name exactly as you did in the sheet. 
• If you typed in the sheet name instead of using the sheet ID, check that you spelled this exactly the same, as well. 
• If you are not the sheet owner, check your sheet permissions to ensure you still have access.

Set Up Get Row

You have now set up the trigger to run the workflow when a change is made in the Today column. Even though nothing else is specified in the workflow, this action in the sheet will now send some basic information to Bridge. For the first step of your workflow, you will need to retrieve some of this initial information: 

1. Start by simply setting the date cell on the first row to today's date. This should automatically check the box in the Today column because of the formula entered earlier. 
2. Save the sheet and go back to the designer in Bridge.
3. Open the Run Log by clicking on the arrows on the right hand side of the designer. At first it will be empty so click on Refresh. This will update the panel and one run should appear with the status Completed.
4. To reveal the initial data sent to Bridge from Smartsheet, click on the run and expand the row Trigger and then the row Event. 
5. Hover over rowId in the trigger data and click on the three dots to reveal a dropdown list. 
6. In the dropdown, select Copy Data Reference. This copies a reference to the row ID that changed to your clipboard. 

By using the reference, rather than the JSON source, the workflow will always get the row that changed instead of always looking at the same row. This is important as the row changing will mostly likely always be different between each run of the workflow.

Update the state and add a module:

1. Click on the grey state and change the Name field to Get Row.
   TIP: It is good practice to name the states according to what the modules below them do to make it easier to read your workflows.
    
2. Next, find the module Get Row in the left hand menu.
   Use the search field or find it by expanding Integrations > Smartsheet > Get Row.
3. Drag the module into the workflow, under the grey state. A panel to set up the module opens automatically. 
4. In the field called Row ID, paste in the reference you copied from the rowId in the Run Log. The pasted value should read {{runtime.event.rowId}}.
5. To get the sheet ID, open the Run Log again and find the sheetID by expanding the Trigger data. Copy the reference to the sheet ID like you did for the row ID earlier, by clicking on the three dots.
6. Click on the Get Row module again and paste in the reference in the Sheet field. The pasted value should read {{runtime.sheetID}}.
7. Select the Save icon at the top of the page to save the work you’ve just completed.

Set up the junction

Next, you need to determine if the change to the checkbox in the sheet was because the box was checked or unchecked. An unchecked box should be ignored, however if the box changes to checked, it means the date set in the Date column is today's date and you want to collect the weather forecast.

To do so, you will need to reference the cell with the checkbox. 

1. Go back to the sheet and change the date in the row you added earlier to uncheck the box, then save the sheet.
2. In Bridge, open the Run Log again and click on All Runs at the top left of the panel. Refresh the list to see a new completed run at the top of the list. 
3. Open this new run and expand the step Smartsheet: Get Row to reveal the row data.
4. Under row > cells > Today you can see that the value is false.
   This tells you that the checkbox in the Today column, in the row that changed, was unchecked. 
5. Using the dropdown from the three dots, copy the data reference of this value.

Add a Match conditional junction:

The next step is to add a junction to check if the value matches the text values true (checked) or false (unchecked). 

1. In the search field, type in Match or find the junction by expanding Utilities > Conditional Junctions > Match. 
2. Drag the junction in under the Get Row module and name it Todays Date.

This junction consists of three components: the junction itself and two placeholder states. You can find more states to drag into the workflow at the top of the left hand menu under Pinned Items > New State.

Add states to the junction:

1. Drag a new state into the success placeholder and name it true. 
2. Drag another new state into the failure placeholder and name it false.
3. In the setup panel that opens for the false state, find the Answer field and type in the word stop.

This will cause the workflow to stop if it goes down this path, so don't do the same in the true state.

Add the data reference to the junction:

1. Click on the junction Todays Date to open the setup panel. 
2. In the field First Value, paste in the reference you copied earlier.
   The pasted value should be {{states.Get Row.smartsheet.get_row.row.cells.Today.value}}
3. In the field Second Value, type in the word true. 
4. Save what you have created so far.

Set up the first HTTP Call module

The first HTTP Call identifies the grid point of a location entered in the sheet. 

1. Find the module HTTP Call using the search field or by expanding Utilities > Utility Functions > HTTP Call.
2. Drag it into the workflow under the true state.
3. In the URL field, paste in the following:
   https://api.weather.gov/points/{{states.Get Row.smartsheet.get_row.row.cells.Longitude.value}},{{states.Get Row.smartsheet.get_row.row.cells.Latitude.value}}
    
4. Expand the Headers section and the field Key #1 type in User-Agent: 
5. In the field Value #1, type in your company name and email separated by a comma.
   
   NOTE: This is a requirement of the US Weather Service so they can contact you if they see unexpected activity or behaviour in their logs.
6. Finally, check the Response Handler box and save the workflow.

Data References for the grid call

The URL above is created for you, since understanding how HTTP requests work isn't required for this guide. This URL references the Longitude and Latitude values on the row that triggered the workflow.

If you want to get these yourself, you need to trigger the workflow again. This time you need to make sure the checkbox gets checked. Add information into a new row rather than using the first one and make sure to set the date to today's date so that the workflow goes down the left branch of the junction.

You will need coordinates to be entered into the Longitude and Latitude fields as well. For example, you can use 47.6174 and -122.2001 to get the weather for Smartsheet's Bellevue Office.

Coordinates

The US Weather Service requires a maximum of 4 decimal places for the coordinates.

You can adjust the workflow to round values down to the required decimal places like in the template Get the Forecast with US Weather Service.

Set up the second HTTP Call

The second HTTP call is to retrieve the forecast from the US Weather Service based on the previously located grid points. To set this up, you will need to add a new state and another HTTP Call module to the workflow. 

1. Drag and drop a new state from the pinned items at the top of the left hand menu and name it Get Forecast. 
2. Then drag in another HTTP Call module under the new state.
3. In the URL field, paste in the following:
   
   https://api.weather.gov/gridpoints/{{states.true.utilities.httpcall.properties.gridId}}/{{states.true.utilities.httpcall.properties.gridX}},{{states.true.utilities.httpcall.properties.gridY}}/forecast
    
4. Expand the Headers section and in the Key #1 field, type in User-Agent: 
5. In the Value #1 field, type in your company name and email separated by a comma.
6. Finally, check the Response Handler box.
7. Save the workflow.

Data References for the forecast call

If you want to get the data references used in the URL above yourself, trigger the workflow again. Make sure the row changed or added has today's date so the checkbox changes to being checked.

When you have created the new row and saved the sheet, find the last run in the Run Log and expand the last step, Utility Functions: HTTP Call. Under Output > properties, locate the fields gridId, gridX and gridY. You can then use these references in the URL for the call to the weather service.

Set up Update Row

Now that you can get weather data with the workflow, the last step is to update the sheet with the forecast.

Add the Sheet and Row IDs

1. First, trigger the workflow again. Make sure the row changed or added has today's date so the checkbox changes to being checked.
2. Add another state under the last HTTP call module and name it Update Row. 
3. Search for the Update Row module or find it by expanding Integrations > Smartsheet > Update Row.
4. From the Run Log, expand the Trigger of the last run and locate the Sheet ID and Row ID values. 
5. Use the data references to set the fields Sheet and Row ID for the Update Row module.

Add the detailed forecast data reference

1. Go back to the Run Log and expand the last step (the last of the two HTTP Call modules). You can find a range of information in the data returned from the US Weather Service, including weather forecasts for seven days. 
2. Find the current weather under Output > properties > periods > 0.
3. For this workflow, you will want to use the reference to the detailedForecast. Copy the data reference. 
4. Open up the Update Row module again and expand the section called Cells. 
5. Paste the reference into the Value # field. The pasted value should read:
   {{states.Get Forecast.utilities.httpcall.properties.periods.0.detailedForecast}}
    
6. In the Key #1 field, enter the column name Weather Forecast.

If you want to make sure the workflow doesn't fail if the US Weather Service is unable to return information you can adjust the data reference in the Value # field to:

{{states.Get Forecast.utilities.httpcall.properties.periods.0.detailedForecast||Unable to fetch weather data}}

This ensures that if there is no data in the 'detailedForecast' the module updates the row with the text after the symbols '||'.

Completed workflow

Your workflow will run anytime you add a new row (either going down the left or the right branch of the junction), as well as anytime today's date is the date set on an existing row. The Weather Forecast column in your sheet will automatically populate if the workflow goes down the left branch of the junction or if the Today box is checked.