Applies to

Bridge by Smartsheet

Start a blank workflow for 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.

PLANS

  • Bridge by Smartsheet

You'll be using the US Weather Service to get forecasts since you don't need an account to access their weather data.

To get the forecast you must make two API calls to the US Weather Service:

  • One to locate the grip point a geographical location is in.
  • A second one to get the forecast for that identified grid. 

You don't need to be comfortable with API calls to follow the steps in this guide as it provides everything you need to complete the workflow.

These are the steps to build the workflow:

  1. Create the workflow
  2. Create the sheet
  3. Authenticate with Smartsheet and set up the trigger
  4. Set up Get Row
  5. Set up the junction
  6. Set up the first API Call
  7. Set up the second API Call
  8. Set up Update Row

Step 1: Create the workflow

  1. In Bridge, select + from the Workflows dashboard.
  2. Give your workflow a name and select Create New. This takes you to the Designer.

    All workflows start with a trigger and a state.

Brandfolder Image
New workflow for the API call to the US Weather Service

Step 2: 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:

Column nameColumn type
DateDate
TodayCheckbox
LongitudeText
LatitudeText
Weather ForecastText

 

Brandfolder Image
Create the sheet for the API call to the US Weather Service

 

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. Select Enter to save the formula in that cell. This checks the checkbox if the data set in the Date column is today's date, which you can then use to trigger your workflow in Bridge.
  3. Right-click the cell with the formula and select Convert to Column Formula. This ensures that all new rows added to the sheet are using that formula in the Today column.
  4. Save the sheet.
  5. Before going back to the Designer in Bridge, copy the sheet ID. You can find the sheet ID by going to File > Properties.

Step 3: Set up the trigger

In the Designer, select 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, select Integrations Page under the automatically expanded section Integrations.

Brandfolder Image
Set up the trigger for the API call

This opens 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.

Brandfolder Image
Triggers tab

Expand the Triggers section and select 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 drop-down 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. In the drop-down Select Workflow list, choose the workflow you created earlier and select Save.

    You can tell 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. 
  • If you're not the sheet owner, check your sheet permissions to ensure you still have access.
  • If there are other triggers set up already, there may be an issue with a different trigger. Review the other triggers to ensure that there wasn't a permissions change on one of them, or that the sheet referenced in the trigger still exists.

Step 4: Set up Get Row

You've now set up the trigger to run the workflow when you make a change 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 must retrieve some of this initial information: 

  1. Start by 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 you 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's empty, so select Refresh. This updates the panel; one run should appear with the status Completed.
  4. To reveal the initial data sent to Bridge from Smartsheet, click on the run, expand the row Trigger, and then the row Event
  5. Hover over rowId in the trigger data and select the more icon to reveal a drop-down list. 
  6. In the drop-down, 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 always gets the row that changed instead of always looking at the same row. This is important as the row changing will probably be different between each run of the workflow. 

Brandfolder Image
Set up Get Row

Update the state and add a module

  1. Click on the gray state and change the Name field to Get Row.

    It's good practice to name the states according to what the modules below them do to make it easier to read your workflows.

  2. 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 gray state. A panel to set up the module opens automatically. 
  4. In the Row ID field, 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 sheet ID by expanding the trigger data. Copy the reference to the sheet ID like you did for the row ID earlier, by selecting the more icon.
  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 Save at the top of the page to save the work you've completed.

    Brandfolder Image
    Update the state and add a module

Step 5: Set up the junction

Now 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 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 select 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 drop-down, 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 Today's 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.

 

Add the data reference to the junction

  1. Select the junction Today's 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've created so far.

    Brandfolder Image
    Add data reference to the junction

Step 6: Set up the first API call

Set up a profile for your API calls

To retrieve weather forecast data, you first need to create a profile for your API calls to the US Weather Service. Profiles are a way to save key information for the system APIs you want to connect your workflows to.

  1. In your Bridge account, navigate to the Integrations tab.
  2. Select the Call API utility.
  3. Select Continue > Add to Profiles.

    Brandfolder Image
    Set up a profile for you API calls

     

  4. Name your profile. Each profile name must be unique. For example: US Weather.
  5. In the Base URL field, enter https://api.weather.gov.
  6. Expand the Headers drop-down:
    • In the Key #1 field, enter User-Agent.
    • In the Value #1 field, enter your company name and email separated by a comma.

      This is a requirement of the US Weather Service so thay can contact you if they see unexpected activity or behavior in their logs.

  7. Save the profile.
  8. Return to your workflow.

 

Set up the Make API Call module in your workflow

The first API call identifies the grid point of a location entered in the sheet.

  1. Find the Make API Call module using the search field or by expanding Utilities > Utility Funcions > Make API Call.
  2. Drag it into the workflow under the true state.
  3. In the Make API Call module, select the profile you just created from the drop-down menu.
  4. In the Endpoint field, include the following points: points/{{states.Get Row.smartsheet.get_row.row.cells.Longitude.value}},{{states.Get Row.smartsheet.get_row.row.cells.Latitude.value}}
  5. Set the Method to GET.
  6. Save the workflow.

    Brandfolder Image
    Set up the first API call

Data references for the grid call

The URL above is created for you since understanding how API 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.

Coordinates

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

Required step

You need coordinates to enter them 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.


Step 7: Set up the second API Call

The second API call retrieves the forecast from the US Weather Service based on the previously located grid points. To set this up, you must add a new state and another Make API 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 Make API Call module under the new state.
  3. In the Make API Call module, select the profile you just created from the drop-down.
  4. In the Endpoint field, include the following: gridpoints/{{states.true.call_api.make_api_call.response.properties.gridId}}/{{states.true.call_api.make_api_call.response.properties.gridX}},{{states.true.call_api.make_api_call.response.properties.gridY}}/forecast
  5. Set the Method to GET.
  6. Save the workflow.

    Brandfolder Image
    Set up the second API call

Data references for the forecast call

If you want to get the data references used in the endpoint 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've created the new row and saved the sheet, find the last run in the Run Log and expand the last step: Call API: Make API 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.


Step 8: 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. 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 API 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 ID 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 Make API 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 want to use the reference to the detailedForecast. Copy the data reference. 
  4. Open up the Update Row module again and expand the Cells section. 
  5. Paste the reference into the Value # field. The pasted value should read: {{states.Get Forecast.call_api.make_api_call.response.properties.periods.0.detailedForecast}} 
  6. In the Key #1 field, enter the column name Weather Forecast.
  7. Save the workflow.

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.call_api.make_api_call.response.properties.periods.0.detailedForecast||Unable to fetch weather data}}
This ensures that the module updates the row with the text after the symbols '||' if there's no data in the detailedForecast.

 

Brandfolder Image
Add detailed forecast data reference

Completed workflow

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

Was this article helpful?
YesNo