Create a simple API backend with Google App Script and a Spreadsheet

README.md

Google App Script CRUD

Inspired by this gist.

Getting Started

1. Create a new App Script project.
2. Paste the content of the file google-app-script-crud.gs in the default Code.gs file.
3. Create a new Spreadsheet.
4. Copy the Spreadsheet ID found in the URL into the variable SHEET_ID located in line 1 of your file.

Sheet Structure Tips

• Every Sheet should have an id column as the recommended structure. Think of it as a relational database.
• There is no auto-increments in the script logic for the id column, but can be easy send in the payload just getting the total length of the table + 1.

API

Read

Query params:

@parameter-required action=read
@parameter-required table=<SHEET_NAME>
@parameter-optional id=<COLUMN_ID>

When providing the optional id, it will fetch that record in key-value format.

Example:

Request GET https://<yourappscripturl>?action=read&table=employees

Response:

{"success":true,"data":[{"id":1,"name":"Carls","email":"[email protected]","account":000000000,"row":2},{"id":2,"name":"Alf","email":"[email protected]","account":000000000,"row":3},{"id":3,"name":"Rich","email":"[email protected]","account":000000000,"row":4},{"id":4,"name":"Salem!","email":"[email protected]","account":000000000,"row":5}]}

Insert

Query params:

@parameter-required action=insert
@parameter-required table=<SHEET_NAME>
@parameter-required data=JSON

Example:

Request GET https://<yourappscripturl>?action=insert&table=employees&data={"id":5,"name":"John Doe","email":"[email protected]","account":1111}

Response:

{"success":true,"data":{"id":5,"name":"John Doe","email":"[email protected]","account":1111}

Update

Query params:

@parameter-required action=update
@parameter-required table=<SHEET_NAME>
@parameter-required id=ID
@parameter-required data=JSON

To update you only need to provide with the key-value JSON of what's going to change.

Example:

Request GET https://<yourappscripturl>?action=update&table=employees&id=5&data={"name":"Johnnathan"}

Response:

{"success":true,"data":{"id":5,"name":"Johnnathan","email":"[email protected]","account":1111}

Delete

Query params:

@parameter-required action=delete
@parameter-required table=<SHEET_NAME>
@parameter-required id=ID

Example:

Request GET https://<yourappscripturl>?action=delete&table=employees&id=5

Response:

{"success":true,"data":{"id":5,"name":"Johnnathan","email":"[email protected]","account":1111}

Author

• Richard Blondet - RichardBlondet

License

This project is licensed under the MIT License - see the LICENSE file for details.

google-app-script-crud.gs

var SHEET_ID = "YOU SPREADSHEET ID";

/* 
 * GET Requests
 */
function doGet( req ) {
   var action    = req.parameter.action;
   var table_req = req.parameter.table;

   var db    = SpreadsheetApp.openById( SHEET_ID );
   var table = db.getSheetByName( table_req );

   switch(action) {
       case "read":
           return Read( req, table );
           break;
       case "insert":
           return Insert( req, table );
           break;
       case "update":
           return Update( req, table );
           break;
       case "delete":
           return Delete( req, table );
           break;
       default:
           break;
   }
}

/* Read
 * request for all tables
 *
 * @parameter action=read
 * @parameter table=<TABLE_NAME>
 * @parameter id=<COLUMN_ID>
 *
 * @example-request | ?action=read&table=<TABLE_NAME>
 * @example-request-single-row | ?action=read&table=<TABLE_NAME>&id=<ROW_NUMBER>
 */
function Read( request, table ) {
  var request_id = Number( request.parameter.id );
  
  return response().json({
    success: true,
    data: _read( table, request_id )
  });

}

/* Insert
 * dynamic for all data
 *
 * @parameter action=insert
 * @parameter table=<TABLE_NAME>
 * @parameter data=JSON
 *  
 * @example-request | ?action=insert&table=<TABLE_NAME>&data={"name":"John Doe"}
 */
function Insert( request, table ) {
  var errors = [];
  
  var last_col     = table.getLastColumn();
  var first_row    = table.getRange(1, 1, 1, last_col).getValues();
  var headers      = first_row.shift();
  var data         = JSON.parse( request.parameter.data );
  var new_row;
  var result = {};  

  try {
    new_row = prepareRow( data, headers );
    table.appendRow( new_row );
    
    result.success = true;
    result.data = data;
    
  } catch ( error ) {
    result.success = false;
    result.data = { error: error.messsage };
  }
  
  return response().json( result );
}

/* Update
 * dynamic for all tables
 *
 * @parameter action=update
 * @parameter table=<TABLE_NAME>
 * @parameter id=<COLUMN_ID>
 * @parameter data=JSON
 * 
 * @example-request | ?action=update&table=<TABLE_NAME>&id=<ID>&data={"col_to_update": "value" }
 */
function Update( request, table ) {
  var last_col      = table.getLastColumn();
  var first_row     = table.getRange(1, 1, 1, last_col).getValues();
  var headers       = first_row.shift();
  
  var request_id    = Number( request.parameter.id );
  var current_data  = _read( table, request_id );
  var data          = JSON.parse( request.parameter.data );
  
  var result = {};
  
  try {
    var current_row   = current_data.row;
    for( var object_key in data ) {
      var current_col = headers.indexOf( object_key ) + 1;
      table.getRange( current_row, current_col ).setValue( data[ object_key ]); // update iteratively
      current_data[ object_key ] = data[ object_key ]; // update for response;
    }
    result.successs = true;
    result.data = current_data;
  } catch ( error ) {
    result.success = false;
    result.data = { error: error.messsage };
  }
  
  return response().json( result );
}

/* Delete
 * dynamic for all tables
 *
 * @parameter action=delete
 * @parameter table=<TABLE_NAME>
 * @parameter id=<COLUMN_ID>
 * 
 * @example-request | ?action=update&table=<TABLE_NAME>&id=<ID>
 */
function Delete( request, table ) {
  var request_id    = Number( request.parameter.id );
  var current_data  = _read( table, request_id );
  
  // delete
  table.deleteRow( current_data.row );
  
  return response().json({
    success: true,
    data: current_data
  });
}

/**
 * Build the response content type 
 * back to the user
 */
function response() {
   return {
      json: function(data) {
         return ContentService
            .createTextOutput(JSON.stringify(data))
            .setMimeType(ContentService.MimeType.JSON);
      }
   }
}

/**
* Read from sheet and return map key-value
* javascript object
*/
function _read( sheet, id ) {
  var data         = sheet.getDataRange().getValues();
  var header       = data.shift();
  
  // Find All
  var result = data.map(function( row, indx ) {
    var reduced = header.reduce( function(accumulator, currentValue, currentIndex) {
      accumulator[ currentValue ] = row[ currentIndex ];
      return accumulator;
    }, {});

    reduced.row = indx + 2;
    return reduced;
    
  });
  
  // Filter if id is provided
  if( id ) {
    var filtered = result.filter( function( record ) {
      if ( record.id === id ) {
        return true;
      } else {
        return false;
      }
    });
    
    return filtered.shift();
  } 
  
  return result;
  
}

/*
 * Prepare row with correct order to insert into
 * sheet.
 * 
 * @throws Error
 */
function prepareRow( object_to_sort, array_with_order ) {
  var sorted_array   = [];
  
  for( var i=0; i<array_with_order.length; i++ ) {
    var value = object_to_sort[ array_with_order[ i ]];
    
    if( typeof value === 'undefined' ) {
      throw new Error( "The attribute/column <" + array_with_order[i] + "> is missing." );
    } else {
      sorted_array[i] = value;      
    }
  }

  return sorted_array;
}

@richardblondet thank you very much for your time and efforts for this clear and clean back-end. Looking at the Delete function, would it be possible to create an Archive function that will take the deleted entry and move it to another Table in the same Sheet ? (instead of inserting it first in the second table then delete it from the first)

Hello, if anyone wants to archive a row, lets say from table data to table archives, just fetch() the insert API to table archives then fetch() the delete API from table data
For more information, this link is about making multiples API calls

Hey @LebCit thanks for your help there! That's a good way to achieve that, thanks for sharing!

Thank you very much for this. Is there any way to secure the API? So that only certain people can access the API?

@HasanMukit, Kanshi Tanaike made a guide for redeploying web apps without changing Web Apps' URL. In the guide their is a very detailed comparison of different combination of app deployment, including deployment settings to organize access to Apps Script project to different Users.

Hello! @Sterh20 thank you so much for providing those resources, I find them extremely useful! Thanks for sharing! @HasanMukit let us know how it goes securing your app. The guide that @Sterh20 shared covers a great amount of use cases and examples. Sending my kind regards!