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.
Hello! Google Sheet isn't for that 🫣