Skip to content

Instantly share code, notes, and snippets.

@dimitrispaxinos

dimitrispaxinos/sheetsToApi.js

Last active December 21, 2023 10:08
Show Gist options
  • Save dimitrispaxinos/293815cfc4009a575e393229dd683a96 to your computer and use it in GitHub Desktop.
Save dimitrispaxinos/293815cfc4009a575e393229dd683a96 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment