sheetsToApi.js

const AUTHENTICATION_ENABLED_SETTING = 'Authentication Enabled';
const AUTHENTICATION_TOKEN_SETTING = 'Authentication Token';


/**
 * Constants for boolean settings values.
 */
const YES = 'Yes';
const NO = 'No';

/**
 * The Settings class provides a way to manage script parameters/settings 
 * directly within a Google Sheet, making it accessible for non-technical users.
 */
class Settings {
    
    /**
     * Constructor initializes the settings sheet and map.
     * @param {string} [sheetName="Settings"] - Name of the sheet where settings are stored.
     */
    constructor(sheetName = "Settings") {
        this.sheetName = sheetName;
        this.spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        this.settingsSheet = this.spreadsheet.getSheetByName(sheetName);
        this.settingsMap = this.initSettingsMap();
    }

    /**
     * Initializes the settings sheet if it doesn't exist.
     */
    init() {
        if (!this.settingsSheet) {
            this.settingsSheet = this.spreadsheet.insertSheet(this.sheetName);
            this.settingsSheet.appendRow(['Setting', 'Value']);
            this.settingsSheet.getRange('1:1').setFontWeight('bold');
        }
    }

    /**
     * Initializes the settings map from the sheet data.
     * @returns {Map} - A map of settings.
     */
    initSettingsMap() {
        if (!this.settingsSheet) return new Map();

        const data = this.settingsSheet.getDataRange().getValues();
        const map = new Map();

        for (const [key, value] of data) {
            map.set(key, value);
        }

        return map;
    }

    /**
     * Sets or updates a setting in the sheet.
     * @param {string} settingName - Name of the setting.
     * @param {string} settingValue - Value of the setting.
     */
    setSetting(settingName, settingValue) {
        const rowIndex = [...this.settingsMap.keys()].indexOf(settingName) + 1;

        if (rowIndex > 0) {
            this.settingsSheet.getRange(rowIndex, 2).setValue(settingValue);
        } else {
            this.settingsSheet.appendRow([settingName, settingValue]);
        }

        this.settingsMap.set(settingName, settingValue);
    }

    /**
     * Retrieves a setting's value from the map.
     * @param {string} settingName - Name of the setting.
     * @returns {string|null} - Value of the setting or null if not found.
     */
    getSetting(settingName) {
        return this.settingsMap.get(settingName) || null;
    }

    /**
     * Retrieves a boolean setting's value.
     * @param {string} settingName - Name of the setting.
     * @returns {boolean|null} - True if 'Yes', False if 'No', or null if neither.
     */
    getBooleanSetting(settingName) {
        const settingValue = this.getSetting(settingName);

        if (settingValue === YES) return true;
        if (settingValue === NO) return false;

        Logger.log(`Setting value is not ${YES} or ${NO}: ${settingName}`);
        return null;
    }

    /**
     * Sets a setting in the script properties.
     * @param {string} settingName - Name of the setting.
     * @param {string} settingValue - Value of the setting.
     */
    setSettingInScriptProperties(settingName, settingValue) {
        PropertiesService.getScriptProperties().setProperty(settingName, settingValue);
    }

    /**
     * Retrieves a setting from the script properties.
     * @param {string} settingName - Name of the setting.
     * @returns {string} - Value of the setting.
     */
    getSettingFromScriptProperties(settingName) {
        return PropertiesService.getScriptProperties().getProperty(settingName);
    }
}


// Create new Settings Instance
const settings = new Settings('Settings');



function onOpen() {
  settings.init();
}

/**
 * Handles HTTP GET requests.
 * @param {Object} e - The event object containing request parameters.
 * @returns {ContentService.TextOutput} - The response containing filtered data or an error message.
 */
function doGet(e) {
  var sheet = null;

  var authenticationEnabled = settings.getBooleanSetting(AUTHENTICATION_ENABLED_SETTING);

  // Authentication
  var apiToken = settings.getSetting(AUTHENTICATION_TOKEN_SETTING);
  var requestToken = e.parameter.token;

  var sheetName = e.parameter.sheet;

  // Check if sheet name is provided, otherwise use active sheet
  if (sheetName != '' && sheetName != undefined) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  }
  else {
    sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  }

  console.log("Sheet Name: " + sheetName);

  // Get all data from the sheet
  var data = sheet.getDataRange().getValues();
  var jsonData = convertRangeToJson(data);

  console.log("JSON Data: " + JSON.stringify(jsonData));


  // Check if authentication is enabled and token is valid
  if (authenticationEnabled && apiToken != requestToken) {
    return ContentService.createTextOutput(JSON.stringify({ 'error': 'Invalid token' }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // Get all params except token and sheet
  var searchParams = e.parameter;
  delete searchParams.token;
  delete searchParams.sheet;

  // Filter data based on search parameters
  var filteredData = jsonData.filter(function (record) {
    for (var param in searchParams) {

      console.log("Param: " + param);

      var searchParam = searchParams[param];

      // convert searchParams[param] to lower case if string
      // if (typeof searchParam === 'string') {
      //   searchParam = searchParam.toLowerCase();
      // }

      if (record[param] !== searchParam) {
        return false;
      }
    }
    return true;
  });

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

}


/**
 * Converts a range to a JSON object array.
 * @param {*} data 
 * @returns 
 */
function convertRangeToJson(data) {
  var jsonArray = [];
  var headers = data[0];

  // Remove spaces from headers
  var lowerCaseHeaders = headers.map(function (header) {
    var header = header.replace(/\s+/g, '');
    return header.toLowerCase();
  });

  for (var i = 1, length = data.length; i < length; i++) {
    var row = data[i];
    var record = {};

    for (var j = 0; j < row.length; j++) {
      // Convert to lowercase
      record[lowerCaseHeaders[j]] = row[j];
      console
    }
    jsonArray.push(record);
  }
  return jsonArray;
}


function convertRangeToJson2(data) {
  var jsonArray = [];
  var headers = data[0];

  for (var i = 1, length = data.length; i < length; i++) {
    var row = data[i];
    var record = {};

    for (var j = 0; j < row.length; j++) {
      record[headers[j]] = row[j];
    }
    jsonArray.push(record);
  }
  return jsonArray;
}