How to access an API with Google Apps Script

Summary

In this tutorial, you'll learn how to use Google Apps Script to create a custom menu in Google Sheets that retrieves real-time weather data from an API. By entering a city name, users can get the current weather conditions, including temperature in Celsius and Fahrenheit, as well as an icon representing the weather. The script leverages the `UrlFetchApp` to fetch data from the WeatherAPI, and then writes the data directly into the sheet. The guide is ideal for anyone looking to automate weather data retrieval in a Google Sheet.

Takeaways

• 😀 The video demonstrates how to access an API with Google Apps Script to fetch real-time weather data.
• 😀 Users can type the name of any city in Google Sheets, and the script will retrieve the current weather data from an API.
• 😀 The script utilizes Google Sheets and Google Apps Script's 'on open' function to create a custom menu for users to trigger the weather data fetch.
• 😀 An API key from weatherapi.com is required to make requests to the weather API. A free version is available with up to a million calls per month.
• 😀 The script uses the URL Fetch App service to make API calls and retrieve data in JSON format.
• 😀 The fetched data is parsed using the JSON service in Google Apps Script to handle the response and extract weather details.
• 😀 The weather data (such as temperature in Celsius, temperature in Fahrenheit, and weather conditions) is written into specific cells in the Google Sheet.
• 😀 A second two-dimensional array is used to store and organize the weather data before writing it back into the sheet.
• 😀 An image representing the weather condition is also added to the sheet by fetching an icon URL from the API response and using a Google Sheets formula.
• 😀 Once the script is set up, users can run it by selecting the 'Display Weather' option from the custom menu in the Google Sheet.
• 😀 The video encourages viewers to like, comment, and subscribe to the channel for more tutorials and guides on working with APIs and Google Sheets.

Q & A

What is the purpose of the Google Apps Script in this video?

The purpose of the Google Apps Script in this video is to fetch real-time weather data from an external API (WeatherAPI) and display it in a Google Sheets file. Users can input a city name, and the script will retrieve the current weather conditions, including temperature and a weather icon, and display the data in the sheet.

How does the 'onOpen' function work in this script?

The 'onOpen' function is a simple trigger that runs automatically when the user opens the Google Sheets document. It adds a custom menu to the Sheets interface, which allows the user to select the 'Display Weather' option to fetch weather data.

What is the significance of the API key in this script?

The API key is used to authenticate the user’s request to the WeatherAPI service. It is a unique identifier that ensures the request is authorized and allows the user to access the data provided by the API. In the script, a placeholder API key is used, but users need to generate their own key from the WeatherAPI website.

Where does the user input the city name to fetch weather data?

The user inputs the city name in cell B1 of the Google Sheets document. The script reads this value and sends it as part of the API request to fetch weather data for that specific city.

How does the script handle the API response data?

The script receives the API response as a JSON object, which contains various weather details such as the current temperature, condition, and an icon URL. The script parses the response using the JSON service in Google Apps Script to extract the relevant data.

What method is used to write the fetched weather data into the Google Sheets?

The script uses the `setValues()` method to write the fetched weather data (such as temperature and weather condition) into a two-dimensional array and then into cells in the sheet, starting from cell A4 to C4.

Why is a two-dimensional array used in the script?

A two-dimensional array is used because the `setValues()` method in Google Apps Script requires data to be in this format. The array structure ensures that the weather data (temperature, condition) is correctly placed in individual cells across multiple columns in the sheet.

How does the script display the weather icon in the sheet?

The script retrieves the weather icon URL from the API response and uses the `setFormula()` method to insert an image into a specific cell (D4) in the sheet. The `=IMAGE()` formula is used to display the weather icon from the URL.

What happens the first time the user runs the script?

When the user runs the script for the first time, they are prompted to grant permission to the script to access their Google Sheets. After granting permission, the script executes and fetches the weather data for the specified city.

How can users get their own API key to use in this script?

Users can obtain their own API key by signing up for a free account at [weatherapi.com](https://www.weatherapi.com/). Once registered, they will receive an API key that they can insert into the script to make successful API requests.