Automatically Refresh Weather Data in a Spreadsheet

Bidding by weather is a classic example of the power of AdWords Scripts but the code from Google can be daunting to implement for scripting newbies. Recently my company Optmyzr launched the ability to connect external data to a Rule Engine, making it possible to bid by weather without writing any code.

A solution like this uses a Google Sheet that is automatically updated with current weather conditions. While adding the API calls to fetch the weather is straightforward, getting it to automatically refresh this data is difficult. This post covers a solution for updating external data on a spreadsheet automatically.

First, grab a copy of this Google Spreadsheet that contains a function courtesy of Mogsdad on StackOverflow that handles updating the weather. It does this by removing and then adding back all functions that deal with importing external data.

This spreadsheet also contains the API connection strings that fetch the temperature and conditions for a zip code.

To be able to use the weather API, be sure to put in your own API key on the settings sheet. You can request one from OpenWeatherMap.orghttps://openweathermap.org/appid#get.

To automatically update the weather on a schedule, set a time-based trigger for the function RefreshImports by going to the "Tools" menu in the spreadsheet and selecting "Script Editor" and then clicking the fields shown in the screenshot below:

You'll also need to update the code at line 17 to reference the ID of your own copy of the spreadsheet. For example, grab this part of the URL:

And put that into line 17 of the script that's associated with the spreadsheet here:

That's it. Now you have a spreadsheet that automatically refreshes with current weather data and you can use it for other AdWords automations.