Skip to content

Instantly share code, notes, and snippets.

@nyancodeid
Last active September 24, 2024 11:56
Show Gist options
  • Save nyancodeid/abc7f2c3ce47eda753dee8a2b63070ab to your computer and use it in GitHub Desktop.
Save nyancodeid/abc7f2c3ce47eda753dee8a2b63070ab to your computer and use it in GitHub Desktop.
Make RESTful API with Google Apps Script and SpreadSheet

Google Script CRUD

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);
}
}
}
@wrightkhlebisol
Copy link

Nice one @nyancodeid 👍🏼

@thuctd
Copy link

thuctd commented Dec 20, 2021

Thanks @nyancodeid !

@FranciscoUribeSM
Copy link

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

@fernandoMota93
Copy link

fernandoMota93 commented Sep 23, 2022

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

@irfansofyana
Copy link

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!

@JillaSridatta
Copy link

image

i am getting this error

@VenomFate-619
Copy link

do share the code where you are calling the doGet functions as well as log the req also .

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment