How to Call APIs Directly From Google Sheets
Google sheets is a fabulous yet highly underrated tool. Previously I wrote an article on converting your google sheet into a web application. I currently use that technique to get live order data from a webhook into a sheet.
You can manage an entire e-commerce site using just google sheets.
In this tutorial, I will show you how to do that by making HTTP requests on Google Sheets.
The Problem
We are running an eCommerce site built on WooCommerce with a large inventory, and the product price keeps changing daily based on demand and supply. Updating each item on WooCommerce takes a lot of work, and the inventory data was already being maintained on Google Sheets.
The Solution
Initially, I thought of building an application with a table that syncs between WooCommerce and the warehouse inventory. But then I realized Google Sheets could do this!
Introducing: UrlFetchApp
This library allows us to make HTTP requests from a google sheet. We will write some functions on Apps Script (Macros for Google Sheets).
First, we need a trigger — An event that will trigger the function.
For this example, I will use onEdit(e)
onEdit(e)It runs when a user changes a value in a spreadsheet.
This is the table I will be using:
I want the fields — quantity, sale price and regular price to change on the website if a change is made on the sheet. The ID is the same ID of the product used by WooCommerce.
Here is the Apps Script code:
The function is easy to understand. The constants contain the column numbers and details of the sheet.
The onEdit() function will be triggered whenever any cell is edited.
The event object is passed as a parameter. It has all the attributes and functions we need.
First, we will figure out where the edit was made. I used e.source.getActiveSheet() and getActiveSheet() to find the sheet's name.
In my case, it's "inventory".
Then I used thee.rangeto find which row and column were edited. That helps us select the data going into the HTTP request's body.
To get the item id, I used sheet.getRange(range.getRow(), item_id_column)
This selects the row where the edit was made and the item_id_column. Once I figure out which column was edited, I use e.value to get the new value of that cell and add that to the request body.
Finally, I store the options of the API call in the variable options and call the API using UrlFetchApp.fetch() with the endpoint.
It's that simple! This solution has saved hours in my company, and the staff didn't have to be retrained.
There is the risk of a clumsy employee pressing a keyboard key by mistake and changing the cell (it has happened). You can also add a button on Google Sheets to trigger the function call instead of using onEdit(). I will show you how in another tutorial.
Here I used a simple trigger. You can even create complex ones chained with other functions and formulas.
Limitations
It is worth noting that UrlFetch() calls are limited to 20,000 per day for a regular user and 100,000 a day for a workspace user.
There are also limits to the body size, which is 50 MB.
Both are more than sufficient for many use cases.
Read more about the limitations here:
https://developers.google.com/apps-script/guides/services/quotas
As a system architect, I always try to find more straightforward solutions to problems.
I love developing solutions using no more than existing tools :)
If you have ideas on how this can be useful, please share them in the comments below.
Level Up Coding
Thanks for being a part of our community! Before you go:
- 👏 Clap for the story and follow the author 👉
- 📰 View more content in the Level Up Coding publication
- 🔔 Follow us: Twitter | LinkedIn | Newsletter
🚀👉 Join the Level Up talent collective and find an amazing job
