DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 966,904 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for Easy way to Create your own API for FREE
Varshith V Hegde
Varshith V Hegde

Posted on • Updated on

Easy way to Create your own API for FREE

Table of Contents

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);
}
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode
  • 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.

Top comments (24)

Collapse
 
thomasbnt profile image
Thomas Bnt

Hello! Google Sheet isn't for that 🫣

Collapse
 
kaspera profile image
Kasper Andreassen

Indeed, but to quote myself when I used Google Sheets as a CMS:

anything that can fit into a spreadsheet eventually will end up in a spreadsheet.

Doesn't mean it's a pretty sight though πŸ˜‚

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

πŸ˜‚Great

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

It feels like illegal πŸ˜‚

Collapse
 
vinceamstoutz profile image
Vincent Amstoutz • Edited on

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

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

πŸ˜…Yeah sorry for it

Collapse
 
vinceamstoutz profile image
Vincent Amstoutz

No problem, it's also for your visibility πŸ˜‰

Collapse
 
hacknetayush profile image
HackNetAyush

But it will be a static api...
Then you can also write some json and host it... That's also an api then!

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

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

Collapse
 
hacknetayush profile image
HackNetAyush

Okay, I was unaware of that we can update the content of google sheets using some script.

Thread Thread
 
varshithvhegde profile image
Varshith V Hegde Author

Yup πŸ‘

Collapse
 
georgecronje1 profile image
georgecronje1

WOW! This is actually so cool! Actually quite useful for a mock API even.

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author • Edited on

Yeah i even created website so everyone can track the placement information πŸ˜…. Varshithvhegde.github.io/placements

Collapse
 
alvesjessica profile image
Jessica Alves

A complementary reading :)
medium.com/@eric_koleda/why-you-sh...

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

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.

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

Ok now this comment made my dayπŸ₯Ί. Happy to know that this helped.

Collapse
 
mellen profile image
Matt Ellen

This is great! Thanks for sharing.

I might try integrating this into my github pages somehow πŸ˜‹

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

Yeah you can if you do please send me the link love to see your projects. πŸŽ‰

Collapse
 
digitalcolony profile image
Michael Allen Smith

Step 4: Should be "Apps Script". I found the option under the Extensions menu.

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

Yes there are multiple ways to get there and this also ne of them .Thanks for noting it

Collapse
 
tleperou profile image
Thomas LepΓ©rou

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

Collapse
 
varshithvhegde profile image
Varshith V Hegde Author

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

Visualizing Promises and Async/Await 🀯

async await

☝️ Check out this all-time classic DEV post