Make RESTful API with Google Apps Script and SpreadSheet

README.md

Google Script CRUD

By Ryan Aunur Rassyid

Simply create RESTful API with Google Script and store it to Google SpreadSheet like a Pro.

google-script-crud.gs

/* 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);
      }
   }
}

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

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

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

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

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.

and what would be the url to make the requests? how would it be generated?

man frequently i am getting this error
ReferenceError: response is not defined (line 19, file "Code")

help 🙏🙏plz!!!

man frequently i am getting this error
ReferenceError: response is not defined (line 19, file "Code")

help 🙏🙏plz!!!

Did you copy response function?

Yes, but without that function also error is occurring
`function response() {
return {
json: function(data) {
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
}
}
function doGet(req) {
var action = req.parameter.action;

var db = SpreadsheetApp.getActiveSpreadsheet();

// Don't forget to change your Sheet Name by default is 'Sheet1'
// var sheetUsers = db.getSheetByName("NationalParks").activate();
var sheetUsers = db.getSheets()[0];

switch(action) {
case "read":
console.log(action)
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!'
});
}
}
function doRead(req,first) {
// var ss = SpreadsheetApp.getActiveSpreadsheet();
//var first = ss.getSheets()[0];
let data=[];
console.log(first.getLastColumn())
for(var row=2;row<=first.getLastRow()-1;row++)
{
data.push({"city":first.getRange(row,1).getValue(),"state":first.getRange(row,2).getValue(),
"location":first.getRange(row,4).getValue(),
"description":first.getRange(row,10).getValue(),
"Recreation Visitors (2013)":first.getRange(row,7).getValue()},
)
}

return ContentService.createTextOutput(JSON.stringify(data))

}
`
And also on line no 19 there nothing

Do not visit the "Learn From ..." website ... It redirected me to a malicious website that with a bunch of pop-up's and an alert sound with a computer voice trying to get visitors to think their system has been hacked!

Please remove the link ... This does not effect the code in this gist ... Thanks!

Do not visit the "Learn From ..." website ... It redirected me to a malicious website that with a bunch of pop-up's and an alert sound with a computer voice trying to get visitors to think their system has been hacked!

Please remove the link ... This does not effect the code in this gist ... Thanks!

Thank you so much for telling me

Thanks for writing this out, this is very helpful!

Nice one @nyancodeid 👍🏼

Thanks @nyancodeid !

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!

i am getting this error

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