Make API with Google Sheets by using table as database

You can use your Google Sheet as a database and make API calls with it. The process is pretty simple and does not require any external third party service.

Steps:

Go to the Google sheet where your data it. Go to Extension -> AppsScript. Paste the below code.

function doGet(e){

// Change Spread Sheet url

var ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/yourspreadsheet/edit");

// Sheet Name, Chnage Sheet1 to your sheet name in Spread Sheet.

var sheet = ss.getSheetByName(“Sheet1”);

return getResult(sheet);

}

function getResult(sheet){

var jo = {};

var dataArray = [];

// collecting data from 2nd Row , 1st column to last row and last column

var rows = sheet.getRange(2,1,sheet.getLastRow()-1, sheet.getLastColumn()).getValues();

for(var i = 0, l= rows.length; i<l ; i++){

var dataRow = rows[i];

var record = {};

record[‘id’] = dataRow[0];

record[‘name’] = dataRow[1];

dataArray.push(record);

}

jo.user = dataArray;

var result = JSON.stringify(jo);

return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);

}

Run the code. It should probably run fine without errors. I have tested it.

Click on Deploy, then New Deployment (you have to deploy as Web app). Fill in the details wherever it asks, it’s quite straightforward.

Deployment here means that your code is saved by Google as a web service, and whenever you call the URL, that code will be run. The code first opens your spreadsheet, reads all the data, and simply returns it as JSON object, in the forms of a REST API.

Related posts:

Coming out of the great depression and heading almost immediately into WWII was not easy on the world. This left a mass amount of people looking to take the most economical route to go about their…

yet another Christmas my heart is on litmus blinded by the lights I see only darkness stooping under sins I am crushed by the cross shackles of guilt dragging my feet bleeding from the brain ego…

Few business owners have both the time and skill with words needed to do their company blog justice. Business blogs need to look polished and represent the brand well, yet learning to write…