Convert Google sheets to API using app script

Have you ever thought of creating API's using google sheets? For smaller projects (e.g. portfolio) it doesn't make any sense to create a brand new project, use a database and host it to heroku.

instead you can create a google sheet, add some data and convert it to API using google app script. wondering how? see this step by step guide.

Steps to follow

Step 1 (Creating Google sheet):

Create a Google sheet & add some sample data according to your use case. I am working on a portfolio app so here is my sample data for google sheet.

Step 2 (Creating google app script project) :

After adding sample data click Extensions from top menu bar and select Apps Script.

Step 3 (Adding the code) :

Paste the following code in editor.

function doGet(req) {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let personalDetails = spreadsheet.getSheetByName('personal-details'); //sheet name is personal-details
  let personalDetailsValues = personalDetails.getDataRange().getValues(); //reading the sheet data(2D array of sheet data)

  /*
    personalDetailsValues output
    [ [ 'First Name', 'Nikhil' ],
    [ 'Last Name', 'Bhatt' ],
    [ 'Email', 'nikhilbhatt931@gmail.com' ] ]
  */

  let personalData = {}; // output hash
  for (let i = 0; i < personalDetailsValues.length; i++) {
    personalData[`${personalDetailsValues[i][0]}`] = personalDetailsValues[i][1];  //Getting the data in desired format
  }

  return ContentService
          .createTextOutput(JSON.stringify({personalData: personalData}))
          .setMimeType(ContentService.MimeType.json); //returning the data
}

In above code we are doing following.

1. Getting active spreadsheet.
2. Getting the desired sheet.
3. Reading the sheet data in form of 2d array.
4. Converting the data to desired format.
5. Returning the data in JSON format

Step 4 (Deployment) :

Click on Deploy and then new deployment.

Click on Settings button.

Fill in the basic details and change who has access to anyone, then click on deploy.

Step 5 (API) :

1. Copy the API
2. Use this as an independent API or add it in your project.

You can even make desired API endpoints and filter the data according to your use case. will demonstrate that in my next blog. till then stay tuned.