Google script sheet API

google_script_sheet_api.js

function createSheetApi(sheetName) {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  function mapValues(values) {
    var data = {};
    for (var i=0, length=headers.length; i<length; i++) {
      data[headers[i]] = values[i];
    }
    return data;
  }
  const methods = {
    find: function(compare) {
      var values = sheet.getDataRange().getValues();
      for (var index=0, length=values.length; index<length; index++) {
        if (compare(mapValues(values[index]))) {
          return index;
        }
      }
      return null;
    },
    get: function(index) {
      var values = sheet.getRange(index, 1, 1, sheet.getLastColumn()).getValues()[0];
      return mapValues(values);
    },
    remove: function(index) {
      sheet.deleteRow(index);
    },
    insert: function(data) {
      var index = sheet.getLastRow()+1;
      methods.update(index, data);
      return index;
    },
    update: function(index, data) {
      var values = sheet.getRange(index, 1, 1, sheet.getLastColumn()).getValues()[0];
      var row = [];
      for (var i=0, length=headers.length; i<length; i++) {
        if (headers[i].length > 0) {
          row.push(data[headers[i]] || values[i]);
        }
      }
      sheet.getRange(index, 1, 1, row.length).setValues([row]);
    }
  };
  return methods;
}