Connecting to a WeatherAPI in Google Sheets via Google Apps Script

Connecting to a WeatherAPI in Google Sheets can provide valuable data for digital businesses, and with the help of Google Apps Script, this process can be automated. The benefits of automation are numerous, including increased efficiency, reduced errors, and the ability to access real-time data without the need for manual updates. By integrating weather data into their Google Sheets, businesses can make informed decisions about marketing strategies, supply chain management, and mormor

For example, an e-commerce store could use weather data to adjust their advertising campaigns based on local weather conditions, or a logistics company could use the information to plan routes and deliveries. This article will guide you through the process of connecting to a WeatherAPI in Google Sheets using Google Apps Script and explore the potential benefits for businesses.

Packages Needed

• API from www.weatherAPI.com
• Google Sheets
• Google Apps Script

Guide

1. Head over to www.sheet.new, and create a new spreadsheet. Tap on Extensions and open up Apps Script.

2. Create a new dynamic menu with the onOpen function.

   `function onOpen() {`
          let ui = SpreadsheetApp.getUi();
          ui.createMenu('Weather')
          `.addItem('Display Weather', 'getWeather')`
       `.addToUi();`
   `}`
   

3. Head over to www.weatherapi.com and create a new account. Grab your API key from your dashboard. The free plan gets you up 5,000,000 calls per month. Connect to the API via:
   

   function getWeather() {`
              `const API_KEY = xxxxx;`
              `let url = 'https://api.weatherapi.com/v1/current.json?key=';`
   
              `let sheet = SpreadsheetApp.getActiveSheet();`
              `let location = sheet.getRange('B1').getValue();`
   }
   

4. Parse the response as JSON
   

   let request = url = API_KEY + '&q=' + location;
      let response = UrlFetchApp.fetch(request);`
      let data = JSON.parse(response.getContentText());
   

5. Push the important parameters into an array. For us, those parameters are: Current Temp (in Celsius and Fahreneit), as well as a condition in text.
   

   let weatherData = [];
   weatherData.push[data.current.temp_c];
   weatherData.push[data.current.temp_f]; weatherData.push[data.current.condition.text];

6 Create a Two-dimensional array. The setValues method works only with 2D arrays.

let weather = [];
      weather.push[weatherData];`
      let icon = data.current.condition.icon;`
      let targetRange = sheet.getRange['A4:C4'];
      targetRange.setValues(weather);
      let iconRange = sheet.getRange('D4');  
    iconRange.setFormula('=IMAGE("$(icon)")');

Conclusion

Integrating weather data into Google Sheets using Google Apps Script can provide valuable insights and benefits for digital businesses. By automating the process of retrieving real-time weather data, businesses can save time and reduce errors while making informed decisions based on the latest information.

Whether it's adjusting marketing strategies, optimizing supply chain management, or planning logistics operations, weather data can be a critical factor. With the help of this guide, connecting to a WeatherAPI in Google Sheets can be a straightforward process, allowing businesses to focus on utilizing the data to improve their operations and drive growth.