You can now easily turn your Google Sheet into a public API for free.
We created this guide and video tutorial because we needed a simple way to store data for use with Google Tag Manager (GTM).
Before this, we had two options: either set up a server and pay a developer to save the data, which is costly, or use a service like Zapier, which can get expensive when saving thousands of rows.
This guide uses a simple one-column Google Sheet as an example, but the code can be easily modified to support multiple columns. If you're not a programmer, don't worry; the steps are straightforward and involve copy-pasting code.
What is an API? For those who are new to this, API stands for Application Programming Interface. Very simply, you can use a URL in the browser or an code to read from your google sheet or write to it.
Steps to Create Your Google Sheet API
1. Create Google Sheet
- Create a Column: Create a new Google Sheet and add a header to column A, like "Users."
- Add Data: Add data below the header. For instance, write "Bob" in cell A2 and "Jason" in cell A3.
- Name the Sheet: By default, it’s probably named "Sheet1." If you change this, remember to update the API request path later on.
2. Create Apps Script
- Access Apps Script: In your Google Sheet, go to the top menu and click on "Extensions" -> "Apps Script."
- Edit Code.gs: In the script editor, replace the existing code with the content from this repository's Code.gs. Make sure to edit row 70 where it says "Users" to match the header of your column A.
- Deploy: After editing, set a project title and click on "Deploy" -> "New Deployment."
3. Set Permissions and Publish
- Deployment Type: Make sure to deploy as a "Web app."
-
Copy Web App URL: After deploying, you'll get a Web App URL like
https://script.google.com/macros/s/xxxxxxxxxxxxxx/exec. This is your API endpoint.
4. Test Your API
-
Reading the Sheet: Add
?path=Sheet1&action=readto the end of your Web App URL. -
Writing to the Sheet: Add
?path=Sheet1&action=write&Users=[name]to the end of your Web App URL to add a new row. For example, to add a user named "Jimmy," you would append?path=Sheet1&action=write&Users=Jimmy.
5. Start Using It
Congratulations! You've just turned your Google Sheet into a simple but powerful public API. Enjoy using it for your projects.
Additional Notes
- Duplicate Checks: The script will check for duplicates before appending a new row. If a duplicate is found, it will not add the new entry.
- Access and Limitations: Make sure to read about sharing settings in Google Sheets and familiarize yourself with Google Sheets and Apps Scripts guidelines, quotas, and limitations.
- Disclaimer: All code and instructions are provided "as is." By using this guide, you agree that you are solely responsible for your actions.
GitHub URL
For the full code and updates, visit our GitHub repository.
