Do I Need an Umbrella Today? Google Sheets and JavaScript to the Rescue!

OK, Google Sheets! Send me an email if I need an umbrella today.

Send an email using Google Sheets? Yes, that’s right! Sheets can do a lot more than add numbers and calculate your mortgage payments. In our last article, we learned how to consume JSON web data using Google Sheets and we built templates to display and sort weather forecast data. Today, we’ll build on what we covered last time and learn how to send email using Google Sheets based on the JSON data we retrieve.

Let’s get practical and build something useful while we’re at it. Have you ever found yourself outside somewhere and it starts to rain and you realize you forgot your umbrella? This has certainly happened to me! We have been experiencing drought conditions in Southern California for the last few years, and so I don’t always think about bringing an umbrella since rain is somewhat infrequent. (Thankfully, I’m hearing rain outside my window right now and we are receiving a lot more rain this year which is great!) Let’s use Google Sheets to improve our situational awareness by retrieving the weather forecast for the day and sending us an email before we leave in the morning if we should bring an umbrella. Let’s get started!

Article Contents

Forecast Weather API Overview

We will retrieve weather forecast data using the same Web API that we utilized in our last article. The data will be delivered in JSON format and provide us with the weather forecast for the next five days. As a review, the forecast API accepts two parameters:

• loc: location (example: San Diego, CA)
• deg: degree type (F or C) Default is F.

Example: Return a JSON object with five day weather forecast information (deg F)

https://thisdavej.azurewebsites.net/api/weather/forecast?loc=San Diego,CA&deg=F

This will return a JSON object with the following structure:

[
  {
    "low": "49",
    "high": "62",
    "skycodeday": "27",
    "skytextday": "Cloudy",
    "date": "2017-01-21",
    "day": "Saturday",
    "shortday": "Sat",
    "precip": "0",
    "degType": "F"
  },
  {
    "low": "56",
    "high": "59",
    "skycodeday": "11",
    "skytextday": "Rain",
    "date": "2017-01-22",
    "day": "Sunday",
    "shortday": "Sun",
    "precip": "100",
    "degType": "F"
  },
  {
    "low": "49",
    "high": "58",
    "skycodeday": "11",
    "skytextday": "Rain Showers",
    "date": "2017-01-23",
    "day": "Monday",
    "shortday": "Mon",
    "precip": "100",
    "degType": "F"
  },
  {
    "low": "48",
    "high": "57",
    "skycodeday": "9",
    "skytextday": "Light Rain",
    "date": "2017-01-24",
    "day": "Tuesday",
    "shortday": "Tue",
    "precip": "80",
    "degType": "F"
  },
  {
    "low": "48",
    "high": "60",
    "skycodeday": "32",
    "skytextday": "Sunny",
    "date": "2017-01-25",
    "day": "Wednesday",
    "shortday": "Wed",
    "precip": "60",
    "degType": "F"
  }
]

Such useful information! Let’s harness our JavaScript skills in conjunction with Google Sheets to discern if rain is likely today.

Create custom function to determine if rain likely

We’ll first create a custom function in Google Sheets to determine if rain is probable today. Open Google Sheets and create a new spreadsheet.

Next, click on Tools from the menu and select Script editor. Google Sheets utilizes JavaScript for developing functions so let’s create some JavaScript code to request data from our forecast weather API and determine if rain is anticipated.

function padZero(number) {
    return number < 10 ? '0' + number : number.toString();
}

We first create a helper function to pad numbers with zero, if needed. For example, the JSON data returned includes dates like 2017-01-25 and this function ensures that the 1 for January is padded with a zero and rendered as 01 so we can effectively query the JSON data. Next, we create the main function:

function forecast_today_object(city, threshold) {
    var url = 'https://thisdavej.azurewebsites.net/api/weather/forecast?loc=' + encodeURI(city);

    var response = UrlFetchApp.fetch(url);
    var obj = JSON.parse(response.getContentText());

    // check forecast for today's date
    var date = new Date();
    var day = padZero(date.getDate());
    var month = padZero(date.getMonth() + 1);
    var year = date.getFullYear();
    var now = year + '-' + month + '-' + day;

    var todayObj = obj.filter(function(o) { return o.date === now; });

    if (todayObj.length > 0) {
        var today = todayObj[0];
        today.precip = parseFloat(today.precip);
        today.rainLikely = today.precip >= threshold;
        return today;
    } else {
        return { error: 'No forecast data found for today' };
    }
}

Our forecast_today_object function uses the built-in UrlFetchApp.fetch method to fetch the data from our weather service. After fetching the raw JSON text, we parse it, and query the array of objects returned to find the forecast weather object for today. We append an additional field to our today object called today.rainLikely and set this to true if the chance of rain is greater than the threshold provided in the function parameter.

As a next step, let’s create a Sheets function to stringify our today JSON object so it can be displayed in a spreadsheet. This function will not ultimately be required for us to send an email if rain is likely; however, it will serve as a useful diagnostics tool for troubleshooting. Go ahead and add this function too:

/**
 * Provide weather forecast object for today including a 'rainLikely' field value based on 'threshold' parameter
 *
 * @param {"city"} city
 *        City (e.g. San Diego, CA)
 * @param {"threshold"} threshold
 *        percent threshold - rain likely if chance of precipitation is greater than or equal to threshold
 * @customfunction
 */
function FORECAST_TODAY(city, threshold) {
    var today = forecast_today_object(city, threshold);
    return JSON.stringify(today, null, 2)
}

This function converts the today JSON object returned into a string and adds 2 spaces of indenting for readability purposes. We could simply call the forecast_today_object function directly in Google Sheets; however, it will not output any results in the cell unless it is converted to a string as we are doing in this FORECAST_TODAY function.

We declare our function name in upper case which is the standard convention for Google Sheets functions. Finally, you will notice that we include a JSDOC style comment above our function. Sheets uses the JSDOC comment to provide a tooltip on function usage when editing a cell and using the function.

Go ahead and press Ctrl+S to save your function. Let’s start using it! Add data to your Sheet as follows:

	A	B	C
1	City	Threshold Percent	Rain likely today?
2	San Diego, CA	20	=FORECAST_TODAY(A2,B2)

You should see the results in the following format:

	A	B	C
1	City	Threshold Percent	Today’s Forecast
2	San Diego, CA	20	{ “low”: “56”, “high”: “59”, “skycodeday”: “11”, “skytextday”: “Rain”, “date”: “2017-01-22”, “day”: “Sunday”, “shortday”: “Sun”, “precip”: 100, “degType”: “F”, “rainLikely”: true }

Yes! We are successfully retrieving JSON data related to today’s weather forecast. The results dramatically increase the height of our row which is not super elegant; nevertheless, we can clearly see that we are successfully retrieving data for the San Diego weather forecast. You will also see that a rainLikely field is included as the last field with a value of true, giving us the assurance that our rain threshold logic is working correctly.

As a test, change the forecast city in cell A2. You should see a new weather forecast appear.

Create custom function to send email if rain is likely today

Great work! We are now ready to create a function that will send an email if rain is likely. Add the following code:

function CheckForecastForRain() {
    var recipient = "[email protected]"
    var city = 'San Diego, CA';
    var threshold = 20;

    var today = forecast_today_object(city, threshold);
    if (today.error === undefined) {
        if (today.rainLikely) {
            MailApp.sendEmail({
                to: recipient,
                subject: "It may rain today (" + today.precip + "% chance) so bring an umbrella",
                body: "Weather forecast:" + today.shortday + " " + today.low + "-" + today.high + "°" + today.degType + " " +
                    today.skytextday + " ~ Rain: " + today.precip + "%"
            });
        }
    }
}

For this function, we do not include any parameters, but instead include all items that might vary at the top of our function. In the next section, we will be creating a “project trigger” to invoke our function once a day, and we will not be able to pass parameters in that context.

In the body of the function, we check and confirm that today.error === undefined. The reason for this is that the forecast_today_object function returns an object with an error field if any errors occur. We thus confirm that today.error === undefined to ascertain that the function was invoked successfully.

We use the built-in Google MailApp.sendEmail method to send an email message. You will notice that there is no “from” parameter included in our email message. Google Sheets will send the email under the aegis of the Gmail account you used to log into Google Sheets.

Let’s try our function to see if it works! First, be sure to modify the recipient variable in the function so that you don’t send me an email message—unless you are seeking to keep me in the loop on the weather in your area. 😉 Also, for testing purposes, you may need to change the threshold to a negative number if no rain is forecasted in your area over the next five days, to confirm the email functionality is working properly.

To invoke your function, follow the two steps in the screenshot below to select the CheckForecastForRain function (step 1) and run it (step 2).

You should receive an email message if you set the threshold to an appropriate value. Congratulations – we almost have complete victory!

Do you want to send text messages too? Keep in mind that we can also utilize email to send SMS (text) messages. For example, to send a text message to 610-555-1234, you can simply send an email message to [email protected], assuming the cell phone number is a T-Mobile subscriber. See this page for a list of SMS email addresses used by many carriers.

Create a time-driven trigger to invoke function on a daily basis

As our final step, we will configure Google Sheets (Google Apps scripts, to be more precise) to invoke our function once a day in the morning to notify us if rain is in the forecast. This will enable us to remember that umbrella before we walk out the door. 🙂 On the page containing your Google Sheets scripts, carry out the following steps:

• From the menu, select “Resources” | “Current project’s triggers”.
• Select “Add a new trigger”.
• Select the following:
• function to run: CheckForecastForRain
• Time-driven event
• Day timer
• 7am to 8am (or whatever time you want to invoke the function to check the forecast for you)

Here’s a screenshot depicting these configuration options:

As explained here, time-driven triggers used by Google Apps scripts (like the one we just created above) will run sometime within the hour you configured and run at that time consistently in the future.

Finally, your function will run in the cloud and there is no need to stay logged into Google Sheets for your function to faithfully execute once a day.

Conclusion

Congratulations! You will now be prompted to remember your umbrella when it is needed, and hopefully you learned something in the process!

We have merely scratched the surface of all that Google Sheets can do with the help of JavaScript and JSON. Take your newfound knowledge and go create some other cool inventions today!

Follow @thisDaveJ (Dave Johnson) on Twitter to stay up to date with the latest tutorials and tech articles.

Additional articles

Consuming JSON Web Data using Google Sheets
Consuming Web API JSON Data Using curl and jq
Creating Node.js Microservices with Ease Using Stdlib
Consuming Node.js Microservices Created with Stdlib