Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Make RESTful API with Google Apps Script and SpreadSheet
/* Route
* All Request with Method Get will be proces here
*/
function doGet(req) {
var action = req.parameter.action;
var db = SpreadsheetApp.openById("YOUR SPREADSHEET ID");
// Don't forget to change your Sheet Name by default is 'Sheet1'
var sheetUsers = db.getSheetByName("Users");
switch(action) {
case "read":
return doRead(req, sheetUsers);
break;
case "insert":
return doInsert(req, sheetUsers);
break;
case "update":
return doUpdate(req, sheetUsers);
break;
case "delete":
return doDelete(req, sheetUsers);
break;
default:
return response().json({
status: false,
message: 'silent!'
});
}
}
/* Read
* request for all Data
*
* @request-parameter | action<string>
* @example-request | ?action=read
*/
function doRead(request, sheetObject)
{
var data = {};
data.records = _readData(sheetObject);
return response().json(data);
}
/* Insert
*
*/
function doInsert(req, sheet) {
var id = req.parameter.id;
var username = req.parameter.username;
var email = req.parameter.email;
// all data your needed
var flag = 1;
var Row = sheet.getLastRow();
for (var i = 1; i <= Row; i++) {
/* getRange(i, 2)
* i | is a row index
* 1 | is a id column index ('id')
*/
var idTemp = sheet.getRange(i, 1).getValue();
if (idTemp == id) {
flag = 0;
var result = "Sorry bratha, id already exist";
}
}
// add new row with recieved parameter from client
if (flag == 1) {
var timestamp = Date.now();
var currentTime = new Date().toLocaleString(); // Full Datetime
var rowData = sheet.appendRow([
id,
username,
email,
timestamp,
currentTime
]);
var result = "Insertion successful";
}
return response().json({
result: result
});
}
/* Update
* request for Update
*
* @request-parameter | id<string>, data<JSON>, action<string>
* @example-request | ?action=update&data={"email":"ryandevstudio@gmail.com", "username":"nyancodeid"}
*/
function doUpdate(req, sheet)
{
var id = req.parameter.id;
var updates = JSON.parse(req.parameter.data);
var lr = sheet.getLastRow();
var headers = _getHeaderRow(sheet);
var updatesHeader = Object.keys(updates);
// Looping for row
for (var row = 1; row <= lr; row++) {
// Looping for available header / column
for (var i = 0; i <= (headers.length - 1); i++) {
var header = headers[i];
// Looping for column need to updated
for (var update in updatesHeader) {
if (updatesHeader[update] == header) {
// Get ID for every row
var rid = sheet.getRange(row, 1).getValue();
if (rid == id) {
// Lets Update
sheet.getRange(row, i + 1).setValue(updates[updatesHeader[update]]);
}
}
}
}
}
// Output
return response().json({
status: true,
message: "Update successfully"
});
}
/* Delete
*
*/
function doDelete(req, sheet) {
var id = req.parameter.id;
var flag = 0;
var Row = sheet.getLastRow();
for (var i = 1; i <= Row; i++) {
var idTemp = sheet.getRange(i, 1).getValue();
if (idTemp == id) {
sheet.deleteRow(i);
var result = "deleted successfully";
flag = 1;
}
}
if (flag == 0) {
return response().json({
status: false,
message: "ID not found"
});
}
return response().json({
status: true,
message: result
});
}
/* Service
*/
function _readData(sheetObject, properties) {
if (typeof properties == "undefined") {
properties = _getHeaderRow(sheetObject);
properties = properties.map(function (p) {
return p.replace(/\s+/g, '_');
});
}
var rows = _getDataRows(sheetObject),
data = [];
for (var r = 0, l = rows.length; r < l; r++) {
var row = rows[r],
record = {};
for (var p in properties) {
record[properties[p]] = row[p];
}
data.push(record);
}
return data;
}
function _getDataRows(sheetObject) {
var sh = sheetObject;
return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}
function _getHeaderRow(sheetObject) {
var sh = sheetObject;
return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
}
function response() {
return {
json: function(data) {
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
}
}
@richardblondet

This comment has been minimized.

Copy link

richardblondet commented Aug 4, 2019

Hello Man, first of all nice work right there, thanks for this. The function getHeaderRow_ is not present. Thanks

@nyancodeid

This comment has been minimized.

Copy link
Owner Author

nyancodeid commented Aug 4, 2019

Sorry, my mistake. you only need to change from getHeaderRow_ to _getHeaderRow

@richardblondet

This comment has been minimized.

Copy link

richardblondet commented Aug 4, 2019

Hi! Thank you very much, and thanks for sharing!

@richardblondet

This comment has been minimized.

Copy link

richardblondet commented Aug 5, 2019

Hi @nyancodeid, after revising this script many times, I've been motivated and inspired to create one, here it is https://gist.github.com/nyancodeid/abc7f2c3ce47eda753dee8a2b63070ab hope you can look it too!
Thank you for your great work

@kekcsi

This comment has been minimized.

Copy link

kekcsi commented Jun 19, 2020

This code is very helpful and inspiring, but calling its API RESTful is an overstatement. RESTful APIs don't use HTTP GET method for everything. But it definitely helps creating CRUD web services in general.

@richardblondet, your link is wrong, should be https://gist.github.com/richardblondet/ce87a397ef669d4d25dd21ea02b9dda1

@nyancodeid

This comment has been minimized.

Copy link
Owner Author

nyancodeid commented Jun 19, 2020

This code is very helpful and inspiring, but calling its API RESTful is an overstatement. RESTful APIs don't use HTTP GET method for everything. But it definitely helps creating CRUD web services in general.

@richardblondet, your link is wrong, should be https://gist.github.com/richardblondet/ce87a397ef669d4d25dd21ea02b9dda1

That's right, RESTful must have an HTTP Method that represents the operation that was performed likes POST, PUT, or DELETE. Old me has understanding about RESTful as CRUD operation, i just realized it after study more about API and how they should.

Thanks to @richardblondet to make it clear documentation and script. It's awesome. Appreciate.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.