Get the forecast with US Weather Service

You can use this template to gather jobs to be done at a geographical location on a specific date in a sheet. The workflow will update the sheet with coordinates for the address specified and then collect the weather forecast for that day, on the day of the job. This template includes three Bridge workflows.

• Systems Used:
  • Smartsheet
  • Google Geocode
• Workflow Trigger:
  • Smartsheet - When Column Values Are Changed
• Templates Used:
  • Bridge Workflow - Weather Forecast - US Weather Service - Todays Date
  • Bridge Workflow - Weather Forecast - US Weather Service - Get Coordinates
  • Bridge Workflow - Weather Forecast - US Weather Service - Get Forecast

Create a sheet

You will need to have a sheet created in Smartsheet with relevant columns before working with this template. 

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

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

   You will also need to set up a formula in the Today column to check a box if the Date column for that row is today’s date.

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

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

3. =IF(Date@row = TODAY(), true, false)
    
4. Right-click the cell to open the context menu and select Convert to Column Formula. This applies the formula to all cells in the column. (Learn more about column formulas.) 

Deploy the template

In the Solution Center in Bridge, find the template Weather Forecast - US Weather Service in the list and click on it.

Give your workflow a name and click Deploy. This template contains three workflows and they are now available in the list of workflows. 

Click on Weather Forecast - US Weather Service - Get Forecast to open it.

Authenticate Google GeoCode

1. Click on the trigger element at the start of the workflow to reveal the trigger panel on the right side of the designer.
2. Under the section Integration Triggers, click on the link to the Integrations Page.
3. Find the icon for Google Geocode and click on it. 
4. In the pop-up click Continue to open the Settings tab. 
5. In the field paste in the API key from your Google Cloud Platform account. 
6. Click Save.

Authenticate Smartsheet and set up the triggers

You will be setting up two triggers: one starts the workflow Weather Forecast - US Weather Service - Get Coordinates when a new address is entered so the sheet can be updated with coordinates for the location, and one starts the workflow Weather Forecast - US Weather Service - Todays Date when the scheduled date is today's date so the forecast can be fetched.

1. Find the Smartsheet integration and click on the logo.
    
2. Once authenticated, click on the Triggers tab and expand the section Triggers.

Trigger One:

1. Click on Add to Triggers.
    
2. In the Sheet ID field, enter the unique ID of the sheet or, if you know the sheet name is unique, simply type in the name.
   
   NOTE: You can find the sheet ID by navigating to your sheet and selecting File > Properties.
    
3. Next, select the event type When Column Values Are Changed.
    
4. This reveals another field, Column Name or ID. Type in the column name Address.
    
5. In the Workflow dropdown, select the workflow Weather Forecast - US Weather Service - Get Coordinates.
    
6. Click Save.

Trigger Two:

1. Click on Add to Triggers again.
    
2. Enter the same sheet ID or unique sheet name.
    
3. Select the same Event Type, When Column Values Are Changed, but this time type in the column name Today in the Column Name or ID field.
    
4. In the workflow dropdown, select the workflow Weather Forecast - US Weather Service - Todays Date.
    
5. Click Save and close the pop-up.
    
6. Close the integrations tab and go back to the designer.

Set Contact Info to US Weather Service

The US Weather Service doesn't require authentication to use, but they ask that you include contact information in the HTTP Call.

1. To do this, click on the first of the HTTP Call modules to open the setup panel and expand the Headers section.
    
2. The field Key #1 is already set. In the Value #1 field, enter your company name and email address separated by a comma.
   
   e.g. MBF Corp., john.doe@mbf.com
    
3. Repeat the same steps for the second HTTP Call module and save your workflow.

How the workflows work

Both workflows can result in returning a forecast to your sheet; which one depends on if a job was added for a future date, if a job was added for today's date, or if today is now the date of a previously scheduled job.

Option One: Get Coordinates triggering Get Forecast

When a job is added, the Get Coordinates workflow will run to update the Longitude and Latitude of the address entered. The workflow will stop there if the date entered is in the future. 

If the date entered is today's date, then the Get Forecast workflow will run to get the weather forecast for today.

Option Two: Todays Date triggering Get Forecast

If a job that was added previously with a future date now has today’s date, a checkbox will become checked on your sheet (due to the formula you created earlier) and trigger the workflow called Todays Date. This workflow will first get the row data and then call the Get Forecast workflow to update the row with the weather forecast for the day.

If a new job is scheduled on the same day it’s entered, both the Get Coordinates workflow and the Todays Date workflow will trigger, resulting in the Get Forecast workflow to be triggered twice. 

However, Todays Date requires the Latitude, so if that field is currently blank this workflow will stop as it won’t have all of the data needed. The Get Coordinates workflow will first update the row with the right coordinates and then the forecast will be collected, as outlined in Option One above.