PushToWebEndpoint

PushToWebEndpoint.gs

/**
 * This Google Apps Script provides functionality to:
 * 1. Query a Google Sheet via an HTTP GET request.
 * 2. Push data from the sheet to an external endpoint using a menu-triggered action.
 * 
 * Features:
 * - Reads data from a specific sheet and processes specified columns.
 * - Allows adding a custom menu to trigger the data push.
 * - Logs progress and timing information for debugging.
 */

// Define constants for configuration
const HEADER_ROWS_TO_IGNORE = 1; // Number of header rows to skip (e.g., title row)
const SHEET_NAME = "Tabellenblatt1"; // The name of the sheet to read from
const RETURN_COLUMNS = ["Search", "Content"]; // Columns to include in the output (case-insensitive)
const PUSH_ENDPOINT = "https://mpd964be78bb044681ab.free.beeceptor.com"; // URL for the external API to push data

/**
 * Adds a custom menu to the Google Sheets UI when the spreadsheet is opened.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("JobNinja Actions")
    .addItem("Push Data to JobNinja Database", "pushDataToEndpoint")
    .addToUi();
}

/**
 * Pushes data from the specified sheet to the defined external endpoint.
 * Logs progress and performance metrics throughout the process.
 */
function pushDataToEndpoint() {
  const logStart = new Date(); // Record start time
  console.log("Push data process started at: " + logStart.toISOString());

  // Get the sheet by name
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  if (!sheet) {
    SpreadsheetApp.getUi().alert(`Sheet '${SHEET_NAME}' not found.`);
    console.log("Sheet not found. Process aborted.");
    return;
  }

  console.log("Sheet found. Loading data...");
  const data = sheet.getDataRange().getValues(); // Load all data from the sheet
  console.log(`Data loaded. Number of rows: ${data.length}`);

  const headers = data[0].map(header => header.toString().trim().toLowerCase()); // Extract headers
  console.log("Headers: " + JSON.stringify(headers));

  // Validate if all RETURN_COLUMNS are present in the headers
  const lowerReturnColumns = RETURN_COLUMNS.map(col => col.toLowerCase());
  const missingColumns = lowerReturnColumns.filter(col => !headers.includes(col));
  if (missingColumns.length > 0) {
    SpreadsheetApp.getUi().alert(
      `Missing columns in the sheet: ${missingColumns.join(", ")}. Please verify column names.`
    );
    console.log(`Missing columns: ${missingColumns.join(", ")}. Process aborted.`);
    return;
  }

  console.log("All required columns found. Extracting data...");
  const extractedData = [];
  for (let i = HEADER_ROWS_TO_IGNORE; i < data.length; i++) {
    const rowObject = {};

    // Map values for the specified columns
    headers.forEach((header, idx) => {
      if (lowerReturnColumns.includes(header)) {
        rowObject[header] = data[i][idx];
      }
    });

    extractedData.push(rowObject);

    // Log progress every 100 rows
    if (i % 100 === 0) {
      const currentTime = new Date();
      console.log(
        `Processed ${i - HEADER_ROWS_TO_IGNORE + 1} rows so far... Duration: ${currentTime - logStart}ms`
      );
    }
  }

  console.log(`Data extraction completed. Total rows extracted: ${extractedData.length}`);

  // Send the extracted data to the external endpoint
  let responseCode = "unknown";
  try {
    console.log("Sending data to endpoint...");
    const options = {
      method: "post",
      contentType: "application/json",
      payload: JSON.stringify(extractedData),
    };

    const response = UrlFetchApp.fetch(PUSH_ENDPOINT, options);
    responseCode = response.getResponseCode();
    console.log("Data successfully pushed. Response code: " + responseCode);
  } catch (error) {
    console.error("Error while pushing data: " + error.message);
    SpreadsheetApp.getUi().alert(`Failed to push data: ${error.message}`);
  }

  const logEnd = new Date(); // Record end time
  console.log("Push data process completed at: " + logEnd.toISOString());
  console.log("Total time taken: " + (logEnd - logStart) + "ms");

  // Display summary alert
  SpreadsheetApp.getUi().alert(
    `Data push completed.\nTotal rows pushed: ${extractedData.length}\nResponse code: ${responseCode}\nTotal time taken: ${logEnd - logStart}ms.`
  );
}

/**
 * Test function to simulate data push locally in the script editor.
 * Useful for debugging without running through the menu.
 */
function testPushData() {
  pushDataToEndpoint();
}