Table of Contents
- Table of Contents
- Introduction
- What is Google Sheets?
- How to create API using Google Sheets?
- Creating Sample Web Page to show the data
- Conclusion
Introduction
An application program interface (API) is a set of routines, protocols, and tools for building software applications. An API specifies how software components should interact. It can be used to create a new API or extend an existing API. An API can be entirely custom, specific to a component, or it can be designed based on an industry standard to ensure interoperability. An API can be entirely custom, specific to a component, or it can be designed based on an industry standard to ensure interoperability.
What is Google Sheets?
Google Sheets is a spreadsheet program included as part of a free, web-based software office suite offered by Google within its Google Drive service. It allows collaborative editing of spreadsheets online and offline, and the ability to store spreadsheets in the cloud. It is available as a web application, mobile app, and desktop app.
How to create API using Google Sheets?
Step 1
Open Google Sheets and create a new spreadsheet.
Step 2
Add the data you want to use in your API.
Step 3
Click on the share button and share the spreadsheet with anyone with the link. *(Make sure you have selected the option to allow anyone with the link to view the spreadsheet)
Step 4
Now click on the help button and search for AppScript and click on it to open the AppScript editor.This will open a new tab in your browser.
Step 5
This written code is the API for your spreadsheet. You can change the code to suit your needs. You can also add more functions to the code to make it more useful.For this tutorial, we will use the code given below.
function doGet(req){
var doc=SpreadsheetApp.getActiveSpreadsheet();
var sheet=doc.getSheetByName('Sheet1');
var values = sheet.getDataRange().getValues();
var output=[];
for(var i=0;i<values.length;i++){
var row={};
row['Name']=values[i][0];
row['Location']=values[i][1];
output.push(row);
}
return ContentService.createTextOutput(JSON.stringify({data: output})).setMimeType(ContentService.MimeType.JSON);
}
Step 6
Now click on Deploy and select New Deployment.
Step 7
Now Select on the settings button .
Step 8
Now click on the Web App button and choose "Who has access to the app" as Anyone and click on Deploy.
Step 9
It will ask you to authorize the app. Click on Review Permissions and click on Allow.
(It will say the website is not verified. Don't worry about that. It is safe to allow the app just click on Advanced and then click on Go to App)
Step 10
Now you will get a URL. Copy the URL and paste it in your browser. You will get the data in JSON format.
Hurray🎉! You have created your own API using Google Sheets.
Creating Sample Web Page to show the data
- Create a new file and name it index.html.
- Add the following code to the file.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
</body>
<script>
const api="YOUR_API_URL";
fetch(api)
.then(response => response.json())
.then(characters => showCharacters(characters.data));
showCharacters = characters => {
document.write("<table class='tab'><tr class='tab'><th class='tab'><h2>Name</h2></th><th class='tab'><h2>USN</h2></th><th>");
characters.forEach(character => {
if(character.Name!="Name"){
document.write("<tr style='color:black;font-weight: bold;'><td>" + character.Name + "</td><td class='tab'>" + character.Location + "</td><td>");
}
});
}
</script>
</html>
- Be sure to replace YOUR_API_URL with the URL you got from the previous step.
- Now open the file in your browser and you will get the data in a table format.
- You can also use this API in your Android app or any other app.
Conclusion
- In this tutorial, we learned how to create an API using Google Sheets.
- We also learned how to use the API in a web page.
- For reference, you can check out the GitHub repository for this tutorial.
If you have any doubts or suggestions, feel free to comment below.
Latest comments (22)
Very good job and an interesting approach for non-developers or to create a prototype very fast.
However, I think that you may mention in your title "static API" and adding at least one of these tags to be more accurante : #sheet #googleworkspaces #google #appscript
Regards
😅Yeah sorry for it
No problem, it's also for your visibility 😉
But it will be a static api...
Then you can also write some json and host it... That's also an api then!
but you can do live writing and reading using some appscript in the google console so even if it is static in some you can update add delete very easily and it's very easy to contain also
Okay, I was unaware of that we can update the content of google sheets using some script.
Yup 👍
This is great! Thanks for sharing.
I might try integrating this into my github pages somehow 😋
Yeah you can if you do please send me the link love to see your projects. 🎉
WOW! This is actually so cool! Actually quite useful for a mock API even.
Yeah i even created website so everyone can track the placement information 😅. Varshithvhegde.github.io/placements
Step 4: Should be "Apps Script". I found the option under the Extensions menu.
Yes there are multiple ways to get there and this also ne of them .Thanks for noting it
That's quiet original and may definitely fit with some use cases -- e.g. interfacing with Google forms' results. Caution with the term API which might be excessive though. Thanks for sharing
I dont know about google forms but you can use it as server for your contact form i have made it you can check it here
Ok now this comment made my day🥺. Happy to know that this helped.
A complementary reading :)
medium.com/@eric_koleda/why-you-sh...
Wow I thought to cover the disadvantages in my next post. And I think for small applications or if you are competing in a hackathon and you need faster way to generate api i think this would be great.
Hello! Google Sheet isn't for that 🫣
Indeed, but to quote myself when I used Google Sheets as a CMS:
Doesn't mean it's a pretty sight though 😂
😂Great
It feels like illegal 😂