google sheet spreadsheet as api rest using google app script or feeds

gistfile1.txt

var sheetId = "15x3asddawIXE";
var sheetName = "20adsasd-4";
var token = 'adasdasd';
var telegramToken = 'asdasdasd';

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);

var sheetLog = book.getSheetByName("log");

function testDoPostFindAll() {
  var requestMock = {
    parameter: {
      token: "50a1asdasd7ac",
      operation: "findAll"
    },
    postData: {
      contents: {query:"$.name == 'batou'"}
    }
  };
  Logger.log(requestMock);
  var result = doPost(requestMock);
  Logger.log(result.getContent());
}

function testDoPostNewMessage() {
  var requestMock = {
    parameter: {
      token: "50asdasdca7ac",
      operation: "newMessage"
    },
    postData: {
      contents: {"update_id":661916498,
"message":{"message_id":78,"from":{"id":297927684,"is_bot":false,"first_name":"Kirito","last_name":"ENL","username":"Kiritoxz"},"chat":{"id":-479640753,"title":"Test","type":"group","all_members_are_administrators":true},"date":1594569903,"text":"/animes today","entities":[{"offset":0,"length":7,"type":"bot_command"}]}}
    }
  };
  Logger.log(requestMock);
  var result = doPost(requestMock);
  Logger.log(result.getContent());
}

function doPost(e) {

  var receivedToken = e.parameter.token;
  var callback = e.parameter.callback;

  if (receivedToken !== token) {
    var response = {
      "status": 401,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }

  var apiOperation = e.parameter.operation;

  var response;

  try {
    switch (apiOperation) {
      case "newMessage":
        response = processIncomingMessage(e.postData.contents);
        break;
      case "findAll":
        response = performQuery(null);
        break;
      case "query":
        //if operation is query
        var postData = JSON.parse(e.postData.contents);
        var query = postData.query;
        response = performQuery(query);
        break;
      default:
        response = operationNotImplementedYetError();
        break;
    }

    if (callback) {
      return createJsonpResponse(callback, response);
    } else {
      addLogToSheet("before response");
      return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);  
      //return ContentService.createTextOutput(); 
    }



  } catch (err) {
    var responseErr = {
      "status": 500,
      "message": "Internal error:" + err
    };
    addLogToSheet("Global:"+err)
    return ContentService.createTextOutput(JSON.stringify(responseErr)).setMimeType(ContentService.MimeType.JSON);
  }

}

function addLogToSheet(payload) {
  sheetLog.appendRow([payload]);
}

function processIncomingMessage(rawBody) {
  
  addLogToSheet("processIncomingMessage");
  addLogToSheet((typeof rawBody) + rawBody);
  
  var newMessage = JSON.parse(rawBody);
  addLogToSheet((typeof newMessage) + newMessage);  
  addLogToSheet("text:"+newMessage.message.text);
  
  // Make a POST request with a JSON payload.
  var data = {
    'chat_id': newMessage.message.chat.id,
    'text': "your command is:"+newMessage.message.text
  };
  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(data)
  };
  UrlFetchApp.fetch('https://api.telegram.org/bot'+telegramToken+'/sendMessage', options);  
  
  return newMessage;
}  

//TODO: implement filter at convertSheet2JsonText function in order to speed the elapsed time
//TODO: detect query parameters and validate if exist
function performQuery(query) {

  var data;
  try {
    data = convertSheet2JsonObject(sheet);
  } catch (err) {
    return internalError(err)
  }

  //if query is null, return entire data
  if (query == null) {
    var successResponse = {
      "status": 200,
      "message": "success",
      "content": data
    };
    return successResponse;
  }

  try {
    var dataFiltered = [];
    data.filter(function($) {
      if (eval(query)) {
        dataFiltered.push($);
      }
    });

    var successResponse = {
      "status": 200,
      "message": "success",
      "content": dataFiltered
    };
    return successResponse;

  } catch (err) {
    return internalError(err)
  }
}

function createJsonpResponse(callback, data) {
  return ContentService.createTextOutput(callback + '(' + JSON.stringify(data) + ')').setMimeType(ContentService.MimeType.JAVASCRIPT);
}

function createJsonResponse(data) {
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}

function internalError(err) {
  var response = {
    "status": 500,
    "message": "Internal error:" + err
  };
  return response;
}

function operationNotImplementedYetError(operation) {
  var response = {
    "status": 400,
    "message": "operation not implemented yet: " + operation
  };
  return response;
}

function convertSheet2JsonObject(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for (var rowIndex = 2; rowIndex <= lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for (var i = 0; i < rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for (var j = 0; j < titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

sheet-to-api-rest-v1.js

var sheetName = "data01";
var sheetId   = "1i3xaxavcxks4SbGv-rsd4SJ-Lk";

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);  

function doGet(request) {
  
  var json = convertSheet2JsonText(sheet);
          
  return ContentService.createTextOutput(JSON.stringify(json)).setMimeType(ContentService.MimeType.JSON);      
}

function convertSheet2JsonText(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

sheet-to-api-rest-v2.js

var sheetName = "data01";
var sheetId   = "1i3xaxavcxks4SbGv-rsd4SJ-Lk";
var token = 'myprecioustoken'

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);  


function testDoPost() {
  var requestMock = {
    parameter: {
      token: "myprecioustoken"
    },
    postData:{
      contents:"{\"queryType\":\"data\",\"query\":\"$.job == 'developer'\"}"
    }
  };
  Logger.log(requestMock);
  var result = doPost(requestMock);
  Logger.log(result.getContent());
}

function doPost(e) { 
  
  var receivedToken = e.parameter.token;
  
  if(receivedToken!==token){
    var response = {
      "status": 401,
      "message" : "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON); 
  }   

  var postData = JSON.parse(e.postData.contents);
  var queryType = postData.queryType;
  var query = postData.query;
  
  var response;
  var successResponseTemplate = {
    "status": 200,
    "message" : "success"
  };   
  
  switch(queryType){
  
    case "data": 
      response = performQuery(query, null);
      break;
    case "api": 
      response = performQuery(query, successResponseTemplate);
      break;      
    default:
      response = queryNotImplementedError();
      break;
  }
  
  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON); 
  
}

//TODO: implement filter at convertSheet2JsonText function in order to speed the elapsed time
//TODO: detect query parameters and validate if exist
function performQuery(query, responseTemplate) {  
  try{

    var data = convertSheet2JsonText(sheet);  
    var dataFiltered = [];
    data.filter(function ($) {
      if(eval(query)){
        dataFiltered.push($);
      }
    });
    
    if(responseTemplate){
      responseTemplate.content = dataFiltered;
      return responseTemplate;
    }else{
      return dataFiltered;
    }    
    
  }catch(err){
   return internalError(err)
  }
}

function internalError(err) {    
  var response = {
    "status": 500,
    "message" : "Internal error:"+err
  };
  return response;  
}


function queryNotImplementedError(request) {    
  var response = {
    "status": 400,
    "message" : "query type not implemented yet"
  };
  return response;  
}

function convertSheet2JsonText(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

sheet-to-api-rest-v3.js

var sheetId   = "1B0-b****NlZo";
var sheetName = "data";
var token = 'myprecioustoken'

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);  


function testDoPost() {
  var requestMock = {
    parameter: {
      token: "myprecioustoken",
      operation: "findAll"
    },
    postData:{
      contents:"{\"query\":\"$.name == 'batou'\"}"
    }
  };
  Logger.log(requestMock);
  var result = doPost(requestMock);
  Logger.log(result.getContent());
}

function doPost(e) { 
  
  var receivedToken = e.parameter.token;
  
  if(receivedToken!==token){
    var response = {
      "status": 401,
      "message" : "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON); 
  }
  
  var apiOperation = e.parameter.operation;
  
  var response;
  
  try{
  switch(apiOperation){
  
    case "findAll": 
      response = performQuery(null);
      break; 
    case "query": 
      //if operation is query
      var postData = JSON.parse(e.postData.contents);
      var query = postData.query;      
      response = performQuery(query);
      break;       
    default:
      response = operationNotImplementedYetError();
      break;
  }
  
  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);  
  }catch(err){
    var responseErr = {
      "status": 401,
      "message" : "Internal error:"+err
    };    
  return ContentService.createTextOutput(JSON.stringify(responseErr)).setMimeType(ContentService.MimeType.JSON);  
  }
  
}

//TODO: implement filter at convertSheet2JsonText function in order to speed the elapsed time
//TODO: detect query parameters and validate if exist
function performQuery(query) {  
  
  var data;
  try{
    data = convertSheet2JsonObject(sheet);  
  }catch(err){
   return internalError(err)
  }
  
  //if query is null, return entire data
  if(query == null){
    var successResponse = {
      "status": 200,
      "message" : "success",
      "content": data
    };     
    return successResponse;
  }
  
  try{
    var dataFiltered = [];
    data.filter(function ($) {
      if(eval(query)){
        dataFiltered.push($);
      }
    });
    
    var successResponse = {
      "status": 200,
      "message" : "success",
      "content": dataFiltered
    };     
    return successResponse;       
    
  }catch(err){
   return internalError(err)
  }
}

function internalError(err) {    
  var response = {
    "status": 500,
    "message" : "Internal error:"+err
  };
  return response;  
}


function operationNotImplementedYetError(operation) {    
  var response = {
    "status": 400,
    "message" : "operation not implemented yet: "+operation
  };
  return response;  
}

function convertSheet2JsonObject(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

sheet-to-api-rest-v4-get-all-post-query.js

var sheetId = "1B***NlZo";
var sheetName = "****";
var token = '****'

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);


function testDoPost() {
  var requestMock = {
    parameter: {
      token: "myprecioustoken",
      operation: "findAll"
    },
    postData: {
      contents: {query:"$.name == 'batou'"}
    }
  };
  Logger.log(requestMock);
  var result = doPost(requestMock);
  Logger.log(result.getContent());
}

function doPost(e) {

  var receivedToken = e.parameter.token;
  var callback = e.parameter.callback;

  if (receivedToken !== token) {
    var response = {
      "status": 401,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }

  var apiOperation = e.parameter.operation;

  var response;

  try {
    switch (apiOperation) {

      case "findAll":
        response = performQuery(null);
        break;
      case "query":
        //if operation is query
        var postData = JSON.parse(e.postData.contents);
        var query = postData.query;
        response = performQuery(query);
        break;
      default:
        response = operationNotImplementedYetError();
        break;
    }

    if (callback) {
      return createJsonpResponse(callback, response);
    } else {
      return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);  
    }



  } catch (err) {
    var responseErr = {
      "status": 500,
      "message": "Internal error:" + err
    };
    return ContentService.createTextOutput(JSON.stringify(responseErr)).setMimeType(ContentService.MimeType.JSON);
  }

}

//TODO: implement filter at convertSheet2JsonText function in order to speed the elapsed time
//TODO: detect query parameters and validate if exist
function performQuery(query) {

  var data;
  try {
    data = convertSheet2JsonObject(sheet);
  } catch (err) {
    return internalError(err)
  }

  //if query is null, return entire data
  if (query == null) {
    var successResponse = {
      "status": 200,
      "message": "success",
      "content": data
    };
    return successResponse;
  }

  try {
    var dataFiltered = [];
    data.filter(function($) {
      if (eval(query)) {
        dataFiltered.push($);
      }
    });

    var successResponse = {
      "status": 200,
      "message": "success",
      "content": dataFiltered
    };
    return successResponse;

  } catch (err) {
    return internalError(err)
  }
}

function createJsonpResponse(callback, data) {
  return ContentService.createTextOutput(callback + '(' + JSON.stringify(data) + ')').setMimeType(ContentService.MimeType.JAVASCRIPT);
}

function createJsonResponse(data) {
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}

function internalError(err) {
  var response = {
    "status": 500,
    "message": "Internal error:" + err
  };
  return response;
}

function operationNotImplementedYetError(operation) {
  var response = {
    "status": 400,
    "message": "operation not implemented yet: " + operation
  };
  return response;
}

function convertSheet2JsonObject(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for (var rowIndex = 2; rowIndex <= lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for (var i = 0; i < rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for (var j = 0; j < titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

sheet-to-api-rest-v5-get-all.js

var sheetId = "1***yk";
var sheetName = "acme";
var token = '7****4'

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);


function testDoGet() {
  var requestMock = {
    parameter: {
      token: token,
      operation: "findAll"
    }
  };
  Logger.log(requestMock);
  var result = doGet(requestMock);
  Logger.log(result.getContent());
}

function doGet(e) {

  var receivedToken = e.parameter.token;
  
  if (!e.parameter.token) {
    var response = {
      "status": 4011,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }  

  if (receivedToken !== token) {
    var response = {
      "status": 401,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }

  var apiOperation = e.parameter.operation;

  var response;

  try {
    switch (apiOperation) {

      case "findAll":
        response = performQuery(null);
        break;
      default:
        response = operationNotImplementedYetError();
        break;
    }

    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);  



  } catch (err) {
    var responseErr = {
      "status": 500,
      "message": "Internal error:" + err
    };
    return ContentService.createTextOutput(JSON.stringify(responseErr)).setMimeType(ContentService.MimeType.JSON);
  }

}

//TODO: implement filter at convertSheet2JsonText function in order to speed the elapsed time
//TODO: detect query parameters and validate if exist
function performQuery(query) {

  var data;
  try {
    data = convertSheetToJsonObject(sheet);
  } catch (err) {
    return internalError(err)
  }

  //if query is null, return entire data
  if (query == null) {
    var successResponse = {
      "status": 200,
      "message": "success",
      "content": data
    };
    return successResponse;
  }

  try {
    var dataFiltered = [];
    data.filter(function($) {
      if (eval(query)) {
        dataFiltered.push($);
      }
    });

    var successResponse = {
      "status": 200,
      "message": "success",
      "content": dataFiltered
    };
    return successResponse;

  } catch (err) {
    return internalError(err)
  }
}

function createJsonResponse(data) {
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}

function internalError(err) {
  var response = {
    "status": 500,
    "message": "Internal error:" + err
  };
  return response;
}

function operationNotImplementedYetError(operation) {
  var response = {
    "status": 400,
    "message": "operation not implemented yet: " + operation
  };
  return response;
}

function convertSheetToJsonObject(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for (var rowIndex = 2; rowIndex <= lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for (var i = 0; i < rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for (var j = 0; j < titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

sheet-to-api-rest-v6-get-all-query.js

//https://docs.google.com/spreadsheets/d/1A76abcderUQ/edit
var sheetId = "1A76abcderUQ";
var sheetName = "1.0.0";
var token = 'changeme'

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);


function testDoGet() {
  var requestMock = {
    parameter: {
      token: token,
      operation: "findAll"
    }
  };
  Logger.log(requestMock);
  var result = doGet(requestMock);
  Logger.log(result.getContent());
}

function testDoGetQuery() {
  var requestMock = {
    parameter: {
      token: token,
      operation: "simpleQuery",
      field1: "aaa",
      field2: "bbb"
    }
  };
  Logger.log(requestMock);
  var result = doGet(requestMock);
  Logger.log(result.getContent());
}

function doGet(e) {

  var receivedToken = e.parameter.token;
  
  if (!e.parameter.token) {
    var response = {
      "status": 4011,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }  

  if (receivedToken !== token) {
    var response = {
      "status": 401,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }

  var apiOperation = e.parameter.operation;

  var response;

  try {
    switch (apiOperation) {

      case "findAll":
        response = performQuery(null);
        break;
      case "simpleQuery":
        response = performQuery(createQuery(e.parameter));
        break;        
      default:
        response = operationNotImplementedYetError();
        break;
    }

    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);  



  } catch (err) {
    var responseErr = {
      "status": 500,
      "message": "Internal error: " + err
    };
    return ContentService.createTextOutput(JSON.stringify(responseErr)).setMimeType(ContentService.MimeType.JSON);
  }

}

function createQuery(parameters) {
  if(typeof parameters.field1 === 'undefined'){
    throw new Error("field1 parameter is required in query operation");
  }
  
  if(typeof parameters.field2 === 'undefined'){
    throw new Error("field2 parameter is required in query operation");
  }
  
  if(typeof parameters.comparator === 'undefined'){
    throw new Error("comparator parameter is required in query operation");
  }
  
  return `$.${parameters.field1} ${parameters.comparator} ${parameters.field2}`
}  

//TODO: implement filter at convertSheet2JsonText function in order to speed the elapsed time
//TODO: detect query parameters and validate if exist
function performQuery(query) {

  var data;
  try {
    data = convertSheetToJsonObject(sheet);
  } catch (err) {
    return internalError(err)
  }

  //if query is null, return entire data
  if (query == null) {
    var successResponse = {
      "status": 200,
      "message": "success",
      "content": data
    };
    return successResponse;
  }

  try {
    var dataFiltered = [];
    data.filter(function($) {
      if (eval(query)) {
        dataFiltered.push($);
      }
    });

    var successResponse = {
      "status": 200,
      "message": "success",
      "content": dataFiltered
    };
    return successResponse;

  } catch (err) {
    return internalError("Query execution failed:"+query+" "+err)
  }
}

function createJsonResponse(data) {
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}

function internalError(err) {
  var response = {
    "status": 500,
    "message": "Internal error:" + err
  };
  return response;
}

function operationNotImplementedYetError(operation) {
  var response = {
    "status": 400,
    "message": "operation not implemented yet: " + operation
  };
  return response;
}

function convertSheetToJsonObject(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for (var rowIndex = 2; rowIndex <= lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for (var i = 0; i < rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for (var j = 0; j < titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

sheet-to-api-rest-v7-comple-query.js

// https://gist.github.com/jrichardsz/870caadc511e9b55448c07845fdbd4ad#file-sheet-to-api-rest-v7-comple-query-js

var sheetId = "17IQW9BcM";
var sheetName = "1.0.0";
var token = 'changeme'

var book = SpreadsheetApp.openById(sheetId);
var sheet = book.getSheetByName(sheetName);


function testDoGet() {
  var requestMock = {
    parameter: {
      token: token,
      operation: "findAll"
    }
  };
  Logger.log(requestMock);
  var result = doGet(requestMock);
  Logger.log(result.getContent());
}

function testDoGetQuery() {
  var requestMock = {
    parameter: {
      token: token,
      operation: "simpleQuery",
      field1: "repositoryName",
      comparator: "==",
      field2: "'ccc'"
    }
  };
  Logger.log(requestMock);
  var result = doGet(requestMock);
  Logger.log(result.getContent());
}

function testDoGetComplexQuery() {
  var requestMock = {
    parameter: {
      token: token,
      operation: "complexQuery",
      query: "$.repositoryName == 'ccc'",
    }
  };
  Logger.log(requestMock);
  var result = doGet(requestMock);
  Logger.log(result.getContent());
}

function testDoGetComplexQueryMultipleExp() {
  var requestMock = {
    parameter: {
      token: token,
      operation: "complexQuery",
      query: "$.repositoryName == 'ccc' && $.branchName == 'ddd'",
    }
  };
  Logger.log(requestMock);
  var result = doGet(requestMock);
  Logger.log(result.getContent());
}

function doGet(e) {

  var receivedToken = e.parameter.token;
  Logger.log("receivedToken:"+receivedToken);
  
  if (!e.parameter.token) {
    var response = {
      "status": 4011,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }  

  Logger.log("(receivedToken !== token):"+(receivedToken !== token));
  if (receivedToken !== token) {
    var response = {
      "status": 401,
      "message": "remote execution is not authorized"
    };
    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
  }

  var apiOperation = e.parameter.operation;

  var response;

  try {
    switch (apiOperation) {

      case "findAll":
        response = performQuery(null);
        break;
      case "simpleQuery":
        response = performQuery(createQuery(e.parameter));
        break;  
      case "complexQuery":
        response = performQuery(e.parameter.query);
        break;                
      default:
        response = operationNotImplementedYetError();
        break;
    }

    return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);  



  } catch (err) {
    var responseErr = {
      "status": 500,
      "message": "Internal error: " + err
    };
    return ContentService.createTextOutput(JSON.stringify(responseErr)).setMimeType(ContentService.MimeType.JSON);
  }

}

function createQuery(parameters) {
  if(typeof parameters.field1 === 'undefined'){
    throw new Error("field1 parameter is required in query operation");
  }
  
  if(typeof parameters.field2 === 'undefined'){
    throw new Error("field2 parameter is required in query operation");
  }
  
  if(typeof parameters.comparator === 'undefined'){
    throw new Error("comparator parameter is required in query operation");
  }
  
  return `$.${parameters.field1} ${parameters.comparator} ${parameters.field2}`
}  

//TODO: implement filter at convertSheet2JsonText function in order to speed the elapsed time
//TODO: detect query parameters and validate if exist
function performQuery(query) {

  Logger.log("query:"+query)

  var data;
  try {
    data = convertSheetToJsonObject(sheet);
  } catch (err) {
    return internalError(err)
  }

  //if query is null, return entire data
  if (query == null) {
    var successResponse = {
      "status": 200,
      "message": "success",
      "content": data
    };
    return successResponse;
  }

  try {
    var dataFiltered = [];
    data.filter(function($) {
      if (eval(query)) {
        dataFiltered.push($);
      }
    });

    var successResponse = {
      "status": 200,
      "message": "success",
      "content": dataFiltered
    };
    return successResponse;

  } catch (err) {
    return internalError("Query execution failed:"+query+" "+err)
  }
}

function createJsonResponse(data) {
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
}

function internalError(err) {
  var response = {
    "status": 500,
    "message": "Internal error:" + err
  };
  return response;
}

function operationNotImplementedYetError(operation) {
  var response = {
    "status": 400,
    "message": "operation not implemented yet: " + operation
  };
  return response;
}

function convertSheetToJsonObject(sheet) {
  // first line(title)
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  // after the second line(data)
  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for (var rowIndex = 2; rowIndex <= lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  // create json
  var jsonArray = [];
  for (var i = 0; i < rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for (var j = 0; j < titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
} 

steps.md

Method 01 : with feeds

https://coderwall.com/p/duapqq/use-a-google-spreadsheet-as-your-json-backend or if it is down https://raw.githubusercontent.com/jrichardsz/static_resources/master/google/Use%20a%20Google%20Spreadsheet%20as%20your%20JSON%20backend.pdf

This methods return a raw json. You will need to parse it

# Method 02 : susing google app script (RECOMMENDED)

steps

• create a gogole sheet with this data:

name	job
ishikawa	architect
batou	developer

• go to tools > script editor and create a new script and save it
• copy paste this script https://gist.github.com/jrichardsz/870caadc511e9b55448c07845fdbd4ad#file-sheet-to-api-rest-v2-js
• Save the script
• Scrip needs authorizations. To prompt them, click on "run" option and select testDoPost function. Accept the requested permissions.
• Click “Publish” in the Google Apps Script Menu
• Select “Deploy as web app”
• Select Anyone, even anonymous in Who has access to the app option.
• An url will promt you. Save it, this is the url of you new rest api!!
• Click on Deploy button
• If you perform modifications, select a new version when for a new deploy

test: Operation query

• http method: post
• url : https://script.google.com/macros/s/your-key/exec?token=myprecioustoken&operation=query
• body json

{"query":"$.name == 'batou'"}

• response

{
  "status": 200,
  "message": "success",
  "content": [
    {
      "name": "batou",
      "job": "developer"
    }
  ]
}

test: Operation findALl

• http method: post
• url : https://script.google.com/macros/s/your-key/exec?token=myprecioustoken&operation=findAll
• body json (empty)
• response

{
  "status": 200,
  "message": "success",
  "content": [
    {
      "name": "jrichardsz",
      "job": "developer"
    },
    {
      "name": "batou",
      "job": "developer"
    },
    {
      "name": "ishikawa",
      "job": "architect"
    }
  ]
}

links

• https://coderwall.com/p/duapqq/use-a-google-spreadsheet-as-your-json-backend
• https://trevorfox.com/2015/03/rest-api-with-google-apps-script/
• https://ctrlq.org/code/20532-json-rest-api-google-script
• https://developers.google.com/apps-script/guides/web