Gainsight/Scripts/GAS_GS/Williams-Sonoma-Property-Scraper/extractPersonToNewSheet.js

/***************
 * CONFIG
 ***************/
const CONFIG = {
  SPREADSHEET_ID: "17II0V8EsTL-9nHq2HgqhHcay-t-hhOaY5wNEzXHEsAM",
  SOURCE_SHEET_NAME: "WSMinMCA.csv",       // tab name inside the spreadsheet
  EMAIL_COLUMN_INDEX_1BASED: 3,            // Column C = 3 (1-based)
  SENDER: "nrasmussen@gainsight.com",
  SUBJECT: "DATA REQUEST",
  PROCESSED_LABEL: "DATA_REQUEST_PROCESSED",
  TEMP_SHEET_PREFIX: "DATA_REQUEST_",      // new tab prefix
  MAX_THREADS_PER_RUN: 10                  // safety throttle
};

/**
 * Run once to create the time-driven trigger.
 * (There is no true "email received" trigger for Gmail in Apps Script.)
 */
function setupTrigger() {
  // Optional: delete existing triggers for this function to avoid duplicates
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === "processDataRequests")
    .forEach(t => ScriptApp.deleteTrigger(t));

  // Create a trigger every 5 minutes (adjust as desired)
  ScriptApp.newTrigger("processDataRequests")
    .timeBased()
    .everyMinutes(5)
    .create();
}

/**
 * Main worker: finds unread "DATA REQUEST" messages from aubrey@ws.com,
 * extracts target email from body, filters rows, creates new tab, CSVs it,
 * replies with attachment, and labels thread as processed.
 */
function processDataRequests() {
  // const label = GmailApp.getUserLabelByName(CONFIG.PROCESSED_LABEL) || GmailApp.createLabel(CONFIG.PROCESSED_LABEL);

  // // Search unread matching threads that are not already labeled processed
  // const query = [
  //   `from:${CONFIG.SENDER}`,
  //   `subject:"${CONFIG.SUBJECT}"`,
  //   "is:unread",
  //   `-label:${CONFIG.PROCESSED_LABEL}`
  // ].join(" ");

  const threads = GmailApp.search(query, 0, CONFIG.MAX_THREADS_PER_RUN);
  Logger.log(threads);
  // if (!threads.length) return;

  const ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  const sourceSheet = ss.getSheetByName(CONFIG.SOURCE_SHEET_NAME);
  if (!sourceSheet) throw new Error(`Sheet tab not found: ${CONFIG.SOURCE_SHEET_NAME}`);

  // Read all data once for efficiency
  const sourceValues = sourceSheet.getDataRange().getValues();
  if (sourceValues.length < 1) throw new Error(`No data found in ${CONFIG.SOURCE_SHEET_NAME}`);

  const header = sourceValues[0];
  const emailColIdx0 = CONFIG.EMAIL_COLUMN_INDEX_1BASED - 1;

  threads.forEach(thread => {
    const messages = thread.getMessages();

    // Process only unread messages in the thread (usually just the newest)
    messages.forEach(message => {
      if (!message.isUnread()) return;

      try {
        const plainBody = message.getPlainBody() || "";
        const targetEmail = extractFirstNonSenderEmail_(plainBody, CONFIG.SENDER);

        if (!targetEmail) {
          message.reply(
            `I received your DATA REQUEST, but I couldn't find a target email address in the message body.\n\n` +
            `Please include an email like "someone@company.com" in the body and resend.`
          );
          message.markRead();
          return;
        }

        const targetLower = targetEmail.toLowerCase();

        // Filter rows where Column C exactly matches the extracted email (case-insensitive)
        const matches = sourceValues
          .slice(1) // skip header
          .filter(row => String(row[emailColIdx0] || "").trim().toLowerCase() === targetLower);

        // Create a new tab with results
        const newSheetName = makeSafeSheetName_(
          `${CONFIG.TEMP_SHEET_PREFIX}${targetLower}_${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd_HHmmss")}`
        );

        const resultSheet = ss.insertSheet(newSheetName);
        resultSheet.getRange(1, 1, 1, header.length).setValues([header]);

        if (matches.length) {
          resultSheet.getRange(2, 1, matches.length, header.length).setValues(matches);
        }

        // Convert result tab to CSV
        const csv = valuesToCsv_(resultSheet.getDataRange().getValues());
        const blob = Utilities.newBlob(csv, "text/csv", `${targetLower}.csv`);

        // Reply with CSV attached (keeps threading)
        const body =
          `Attached is the CSV export for ${targetEmail}.\n\n` +
          `Rows matched (excluding header): ${matches.length}\n` +
          `Source tab: ${CONFIG.SOURCE_SHEET_NAME}\n` +
          `Result tab: ${newSheetName}\n`;

        message.reply(body, { attachments: [blob] });

        // Mark processed
        message.markRead();
        thread.addLabel(label);

        // Optional cleanup: uncomment if you do NOT want to keep the result tab
        // ss.deleteSheet(resultSheet);

      } catch (err) {
        // Fail gracefully: reply with error so the sender knows it didn't work
        message.reply(
          `I attempted to process this DATA REQUEST but hit an error:\n\n${err && err.stack ? err.stack : err}`
        );
        message.markRead();
      }
    });
  });
}

function modifiedProcessDataRequests() {
  const ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  const sourceSheet = ss.getSheetByName(CONFIG.SOURCE_SHEET_NAME);
  if (!sourceSheet) throw new Error(`Sheet tab not found: ${CONFIG.SOURCE_SHEET_NAME}`);
  const targetEmail = "khodge2@wsgc.com";
  const targetLower = targetEmail.toLowerCase();

  // Read all data once for efficiency
  const sourceValues = sourceSheet.getDataRange().getValues();
  if (sourceValues.length < 1) throw new Error(`No data found in ${CONFIG.SOURCE_SHEET_NAME}`);

  const header = sourceValues[0];
  Logger.log(header);
  const emailColIdx0 = CONFIG.EMAIL_COLUMN_INDEX_1BASED - 1;
  Logger.log(emailColIdx0);

  // Filter rows where Column C exactly matches the extracted email (case-insensitive)
  const matches = sourceValues
    .slice(1) // skip header
    .filter(row => String(row[emailColIdx0] || "").trim().toLowerCase() === targetLower);

  // Create a new tab with results
  const newSheetName = makeSafeSheetName_(
    `${CONFIG.TEMP_SHEET_PREFIX}${targetLower}_${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd_HHmmss")}`
  );

  const resultSheet = ss.insertSheet(newSheetName);
  resultSheet.getRange(1, 1, 1, header.length).setValues([header]);

  if (matches.length) {
    resultSheet.getRange(2, 1, matches.length, header.length).setValues(matches);
  }

  // Convert result tab to CSV
  const csv = valuesToCsv_(resultSheet.getDataRange().getValues());
  const blob = Utilities.newBlob(csv, "text/csv", `${targetLower}.csv`);

  // Reply with CSV attached (keeps threading)
  const body =
    `Attached is the CSV export for ${targetEmail}.\n\n` +
    `Rows matched (excluding header): ${matches.length}\n` +
    `Source tab: ${CONFIG.SOURCE_SHEET_NAME}\n` +
    `Result tab: ${newSheetName}\n`;

  Logger.log(body);

  // Optional cleanup: uncomment if you do NOT want to keep the result tab
  // ss.deleteSheet(resultSheet);
}

/****************
 * HELPERS
 ****************/

/**
 * Extracts the first email address found in the text that is NOT the sender email.
 */
function extractFirstNonSenderEmail_(text, senderEmail) {
  const re = /[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/ig;
  const found = text.match(re) || [];
  const senderLower = (senderEmail || "").toLowerCase();
  const first = found
    .map(e => e.trim())
    .find(e => e.toLowerCase() !== senderLower);
  return first || null;
}

/**
 * Converts a 2D array of values to RFC-ish CSV:
 * - Wrap fields in quotes if they contain comma, quote, or newline
 * - Double internal quotes
 */
function valuesToCsv_(values) {
  return values.map(row => row.map(cell => {
    let s = cell === null || cell === undefined ? "" : String(cell);
    // Normalize line endings inside cells
    s = s.replace(/\r\n/g, "\n").replace(/\r/g, "\n");
    const needsQuotes = /[",\n]/.test(s);
    if (needsQuotes) s = `"${s.replace(/"/g, '""')}"`;
    return s;
  }).join(",")).join("\r\n");
}

/**
 * Google Sheets tab names have limits; keep it safe.
 */
function makeSafeSheetName_(name) {
  // Remove characters Sheets doesn't like: [ ] : * ? / \
  let safe = name.replace(/[\[\]:*?/\\]/g, " ");
  safe = safe.replace(/\s+/g, " ").trim();
  // Max length 100
  if (safe.length > 100) safe = safe.slice(0, 100);
  return safe || "DATA_REQUEST_RESULT";
}