SearchWebEndpoint

SearchWebEndpoint.gs.gs

/**
 * This Google Apps Script provides a web app interface for querying data from a Google Sheet.
 * It enables HTTP GET requests to search for a specific value in a specified column.
 * The script returns the matching row as a JSON object and includes debug information such as:
 * - Time taken for search and retrieval
 * - Deployment version
 * - Last edited time of the sheet
 */

// Define constants for configuration
const SEARCH_COLUMN_NAME = "Search"; // The column name to search (e.g., "Name")
const HEADER_ROWS_TO_IGNORE = 1; // Number of header rows to ignore in the search
const SHEET_NAME = "Tabellenblatt1"; // Name of the Google Sheet to query
const RETURN_COLUMNS = ["Search", "Content"]; // List of columns to include in the response (case-insensitive)

/**
 * Checks whether debug mode is enabled via script properties.
 * @return {boolean} True if debug mode is enabled, false otherwise.
 */
function isDebugMode() {
  return PropertiesService.getScriptProperties().getProperty("DEBUG_MODE") === "true";
}

/**
 * Retrieves the last edited time of the Google Sheet.
 * @param {Sheet} sheet - The Google Sheet object.
 * @param {Array} debugLogs - The debug log array to append timing information.
 * @return {string} The ISO string representation of the last edited time, or "unknown" if unavailable.
 */
function getLastEditedTime(sheet, debugLogs) {
  const start = new Date();
  try {
    const file = DriveApp.getFileById(sheet.getParent().getId());
    const lastEdited = file.getLastUpdated();
    debugLogs.push(`getLastEditedTime Time: ${new Date() - start}ms`);
    return lastEdited ? lastEdited.toISOString() : "unknown";
  } catch (e) {
    debugLogs.push(`getLastEditedTime Time: ${new Date() - start}ms`);
    return "unknown";
  }
}

/**
 * Handles HTTP GET requests to query the Google Sheet.
 * @param {Object} e - The event object containing query parameters.
 * @return {ContentService.TextOutput} The JSON response containing the matching row and debug information.
 */
function doGet(e) {
  const debugLogs = []; // Collect debug information
  try {
    const searchStart = new Date();

    // Extract parameters from the request
    const query = e.parameter.query;

    if (!query) {
      return createResponse({ error: 'Missing query parameter. Please provide a value to search.' }, debugLogs);
    }

    debugLogs.push(`Query parameter: ${query}`);

    // Open the spreadsheet and select the sheet by name
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
    if (!sheet) {
      return createResponse({ error: `Sheet '${SHEET_NAME}' not found. Please check the sheet name.` }, debugLogs);
    }

    const data = sheet.getDataRange().getValues(); // Get all data from the sheet

    // Find the column index based on the header row (assuming headers are in the first row)
    const headers = data[0].map(h => h.toString().trim().toLowerCase());
    debugLogs.push(`Columns Headers: ${headers}`);
    const columnIndex = headers.indexOf(SEARCH_COLUMN_NAME.trim().toLowerCase());
    debugLogs.push(`SEARCH_COLUMN_NAME: ${SEARCH_COLUMN_NAME}, Column Index: ${columnIndex}`);

    if (columnIndex === -1) {
      return createResponse({
        error: `Column '${SEARCH_COLUMN_NAME}' for SEARCH_COLUMN_NAME not found in the headers. Available headers are: [${headers.join(", ")}].`
      }, debugLogs);
    }

    // Convert RETURN_COLUMNS to lowercase for case-insensitive comparison
    const lowerReturnColumns = RETURN_COLUMNS.map(col => col.toLowerCase());

    // Check if all RETURN_COLUMNS match headers case-insensitively
    const unmatchedColumns = lowerReturnColumns.filter(col => !headers.includes(col));
    if (unmatchedColumns.length > 0) {
      debugLogs.push(`Warning: Some columns in RETURN_COLUMNS do not match available headers. RETURN_COLUMNS (lowercase): [${lowerReturnColumns.join(", ")}], Available headers: [${headers.join(", ")}].`);
    }

    // Search for the query in the specified column (case-insensitive, trimmed)
    const lowerQuery = query.trim().toLowerCase();
    for (let i = HEADER_ROWS_TO_IGNORE; i < data.length; i++) {
      const cellValue = data[i][columnIndex].toString().trim().toLowerCase();
      if (cellValue === lowerQuery) {
        debugLogs.push(`Match found in Row ${i}`);
        const rowObject = {};

        headers.forEach((header, idx) => {
          if (RETURN_COLUMNS.length === 0 || lowerReturnColumns.includes(header.toLowerCase())) {
            rowObject[header] = data[i][idx];
          }
        });

        debugLogs.push(`Search and Retrieval Time: ${new Date() - searchStart}ms`);
        debugLogs.push(`Row Object: ${JSON.stringify(rowObject)}`);
        return createResponse(rowObject, debugLogs, sheet);
      }
    }

    // No match found
    debugLogs.push(`Search and Retrieval Time: ${new Date() - searchStart}ms`);
    debugLogs.push(`No match found for query: '${query}'`);
    return createResponse({ error: `No match found for the query: '${query}'.` }, debugLogs, sheet);

  } catch (error) {
    debugLogs.push(`Error: ${error.message}`);
    return createResponse({ error: `An unexpected error occurred: ${error.message}` }, debugLogs);
  }
}

/**
 * Creates a JSON response to return to the client.
 * @param {Object} result - The main result object to include in the response.
 * @param {Array} debugLogs - The debug logs to include if debug mode is enabled.
 * @param {Sheet} [sheet=null] - The Google Sheet object for retrieving metadata (e.g., last edited time).
 * @return {ContentService.TextOutput} The JSON response.
 */
function createResponse(result, debugLogs, sheet = null) {
  if (sheet) {
    const lastEditedStart = new Date();
    result.lastEditedTime = getLastEditedTime(sheet, debugLogs);
    debugLogs.push(`getLastEditedTime Total Time: ${new Date() - lastEditedStart}ms`);
  }

  if (isDebugMode()) {
    result.debug = debugLogs;
  }

  return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
}

/**
 * Test function to simulate an HTTP GET request locally in the script editor.
 */
function testDoGet() {
  const mockEvent = {
    parameter: {
      query: "/stadt/berlin" // Replace with the mock query value you want to test
    }
  };

  const response = doGet(mockEvent);
  Logger.log(response.getContent());
}