You know your data is out there, in that powerful API, but you just can’t figure out how to get it into Google Sheets or your workflow. Between confusing documentation, authentication headaches, and code examples that seem too complex, connecting to an API with Google Apps Script can feel intimidating.
The good news? It’s much easier than it looks once you know the right steps. In this guide, I’ll show you exactly how to access any API with Google Apps Script, step by step, so you can pull in live data, automate updates, and finally make your scripts work for you.
At Cloud Sultans, our mission is to help small and large businesses to better collaborate and achieve high productivity with their teams by maximizing the power of Google Workspace (formerly GSuite).
APIs (Application Programming Interfaces) open endless possibilities for automating tasks, pulling real-time data, and enhancing workflows inside Google Workspace. With Google Apps Script, you can connect your spreadsheets, documents, and other Workspace tools to external services in just a few lines of code. In this article, inspired by a tutorial from saperis, we’ll explore how to access an API with Google Apps Script by building a simple project that retrieves live weather information directly inside Google Sheets.
Why Use APIs with Google Apps Script?
Before diving into the how-to, it’s important to understand why APIs and Google Apps Script are such a powerful combination. APIs are essentially doors into external services — whether it’s weather data, financial updates, or project management platforms. Google Apps Script, being cloud-based and deeply integrated with Google Workspace, makes it incredibly easy to fetch, parse, and display that data right where you need it.
For example, rather than checking a separate weather app, you can type a city name in a Google Sheet and instantly get the live weather pulled in via API. This saves time, reduces manual data entry, and brings external insights right into your business workflows.
Step 1: Creating a Project in the Script Editor
The first step is to prepare your environment. From within Google Sheets, navigate to Extensions > Apps Script. This opens the Script Editor, which acts like your development environment in the cloud.
Although you could leave your project untitled, it’s always best practice to name it something meaningful, such as “Weather API Project.” This way, if you come back later or build multiple projects, you’ll know exactly what each one does. Good naming conventions are an underrated but crucial part of managing automation workflows.
Step 2: Adding a Custom Menu for Users
One of the user-friendly aspects of Apps Script is the ability to create custom menus. Instead of requiring users to run scripts manually, you can add a menu option directly inside Google Sheets.
By writing an onOpen() function, you tell Google Sheets to automatically create a menu every time the spreadsheet is opened. For example, a “Weather” menu with an item called Display Weather makes it simple for users. When clicked, it triggers the script to fetch weather data. This ensures that even non-technical team members can benefit from the API connection without touching the code.
Step 3: Setting Up the API Key and URL
Now that the interface is ready, the next step is authentication. Most APIs, including the one used here (from WeatherAPI.com), require an API key. Think of it like a password that lets your script access the service securely.
After signing up for a free account on WeatherAPI, you’ll receive a unique key. In the script, this key is stored as a constant, ensuring it can be reused easily. Next, the base URL for the API is combined with your key and the query (in this case, the city name) to create the request. Without this step, your script wouldn’t know where to send its request or how to authenticate itself.
Step 4: Pulling Data from Google Sheets
To make the process dynamic, the script doesn’t hard-code the city name. Instead, it pulls the location directly from the sheet. For example, if users type a city name into cell B1, the script reads that value and sends it as the query to the API.
This makes the project flexible: you can check Berlin’s weather one minute and London’s the next, simply by changing the value in the sheet. This ability to connect user input to real-time external data is where the magic of APIs really shines.
Step 5: Making the API Request
With the city name and API key in place, it’s time to send the actual request. Google Apps Script provides the UrlFetchApp service, which allows scripts to communicate with external APIs.
When the request is sent, the API responds with a JSON object — a structured format that contains details like temperature, weather condition, and even icons representing the forecast. While the raw JSON may look confusing, Apps Script makes it easy to parse this data into usable variables with JSON.parse().
Step 6: Extracting Useful Information
The response from WeatherAPI includes a wealth of data, but you usually don’t need everything. In this example, the script focuses on extracting:
- Temperature in Celsius
- Temperature in Fahrenheit
- A description of the current condition (e.g., “Sunny” or “Cloudy”)
- An icon URL representing the condition
By targeting only the necessary fields, you keep the script efficient and the output user-friendly. This practice also ensures that the data displayed in Google Sheets is relevant and easy to interpret.
Step 7: Writing Data Back to the Sheet
Now comes the rewarding part — putting the fetched weather data directly into the spreadsheet. Apps Script requires a two-dimensional array to set values, so the script first organizes the weather information into this format.
The values are then written into specific cells, such as A4:C4, so that users see neatly arranged data: Celsius, Fahrenheit, and condition side by side. This turns the sheet into a live dashboard rather than just a static table.
Step 8: Displaying Weather Icons
Numbers and text are helpful, but visuals make the experience more engaging. The API also provides a URL for a small weather icon (like a sun or cloud symbol). To display this, the script uses the built-in Google Sheets IMAGE() formula, inserting the icon into cell D4.
This step highlights how Apps Script can blend automation with native Google Sheets functionality, creating outputs that are not just practical but also visually appealing. For team dashboards or client reports, these touches make a big difference.
Step 9: Handling Permissions
When running the script for the first time, Google Sheets will ask for authorization. Since the script is trying to access external data and write into your spreadsheet, it needs your explicit permission. This is a one-time setup — once granted, the script can run smoothly without repeated prompts.
This step also reassures users that Google keeps security at the forefront. Even though you’re connecting to an API, you’re in full control of what the script can and cannot do.
Step 10: Testing and Using Your Weather Script
Once everything is set up, it’s time to test. By typing a city like Berlin or Barcelona into cell B1 and then selecting Display Weather from the custom menu, the script fetches and displays real-time data within seconds.
This seamless integration shows just how powerful Apps Script can be when paired with APIs. Instead of juggling multiple apps or copying data manually, everything flows directly into your Google Workspace environment, ready to use for decision-making, reporting, or collaboration.
Final Thoughts on Accessing APIs with Apps Script
Accessing APIs with Google Apps Script unlocks tremendous opportunities. From weather updates and stock prices to CRM integrations and automation with third-party tools, the same principles apply: request data via API, parse the response, and use Apps Script to display or act on it inside Google Workspace.
This project with WeatherAPI is just the beginning. With creativity and a little coding, you can design solutions that transform how your team works every day.
Any questions, comments, or reactions about our article, we’re happy to hear that in the comment section below. We always love diving into healthy discussions. If you also feel that you haven’t been using Google Workspace at its best, reach us at (Cloud Sultans : https://cloudsultans.com/en/blog-post).
We offer free consultation or system audit to find you the best possible solution.
