By Ryan Aunur Rassyid
Simply create RESTful API with Google Script and store it to Google SpreadSheet like a Pro.
/* 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); | |
} | |
} | |
} |
Hello! Does anyone know how to publish the API with a url that doesn't change when making changes? Now every time I implement it changes the URL. It would be ideal to use your own domain to make requests like this: mydomain.com/read
Hello! Does anyone know how to publish the API with a url that doesn't change when making changes? Now every time I implement it changes the URL. It would be ideal to use your own domain to make requests like this: mydomain.com/read
Update the implantation. Go to: Implant->manage->new version. The link will be the same.
I'm using the PT-BR version, may the names vary due your region
Hello everyone! thanks, @nyancodeid for some insight and references on this code. I'm trying to create my own version and improve some things. If anyone interested, feel free to check this out: https://github.com/irfansofyana/sheets-api!
do share the code where you are calling the doGet
functions as well as log the req also .
Thanks @nyancodeid !