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. Image description

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) Image description

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);
}

Image description

Step 6

Now click on Deploy and select New Deployment.

Step 7

Now Select on the settings button . Image description

Step 8

Now click on the Web App button and choose “Who has access to the app” as Anyone and click on Deploy. Image description

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.

Image description

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.