
      <h2>Introduction</h2>
      <p>Every business receives daily operational emails: customer quote requests, inbound website leads, payment receipts, and vendor invoices. Manually copying contact names, phone numbers, and pricing figures into Google Sheets consumes hundreds of team hours annually. With Google Apps Script, you can construct an autonomous, serverless automation pipeline that extracts, parses, and logs email data on a scheduled clock trigger.</p>

      <h2>Architectural Overview</h2>
      <p>Our automation workflow follows four programmatic stages:</p>
      <ol>
        <li>Search Gmail inbox for unread messages matching a specific search operator (e.g., <code>label:leads is:unread</code>).</li>
        <li>Iterate through threads and extract metadata (Date, Sender, Subject, Body text).</li>
        <li>Parse specific fields using regular expressions (Regex) or line splits.</li>
        <li>Append structured records into Google Sheets and apply a processed label in Gmail to prevent duplicate logging.</li>
      </ol>

      <h2>The Production Apps Script Code</h2>
      <p>Open your Google Sheet, click <strong>Extensions &gt; Apps Script</strong>, and paste the following tested script:</p>
      <pre><code>function parseInboundLeadsToSheets() {
  const SHEET_NAME = "Inbound_Leads";
  const GMAIL_QUERY = 'label:inbound-lead is:unread';
  const PROCESSED_LABEL_NAME = "Automated_Processed";
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEET_NAME);
  
  // Create sheet with headers if not present
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
    sheet.appendRow(["Timestamp", "Sender Name", "Sender Email", "Phone", "Requested Service", "Message Snippet", "Thread ID"]);
    sheet.setFrozenRows(1);
    sheet.getRange("A1:G1").setFontWeight("bold").setBackground("#f1f5f9");
  }
  
  // Ensure processed label exists
  let processedLabel = GmailApp.getUserLabelByName(PROCESSED_LABEL_NAME);
  if (!processedLabel) {
    processedLabel = GmailApp.createLabel(PROCESSED_LABEL_NAME);
  }
  
  const threads = GmailApp.search(GMAIL_QUERY, 0, 25);
  if (threads.length === 0) {
    Logger.log("No new unread leads found.");
    return;
  }
  
  const rowsToAdd = [];
  
  for (let i = 0; i < threads.length; i++) {
    const messages = threads[i].getMessages();
    const latestMessage = messages[messages.length - 1];
    
    if (!latestMessage.isUnread()) continue;
    
    const rawSender = latestMessage.getFrom();
    const date = latestMessage.getDate();
    const body = latestMessage.getPlainBody();
    const threadId = threads[i].getId();
    
    // Parse sender name and email address
    let senderName = rawSender;
    let senderEmail = rawSender;
    const emailMatch = rawSender.match(/<([^>]+)>/);
    if (emailMatch) {
      senderEmail = emailMatch[1];
      senderName = rawSender.replace(/<[^>]+>/, "").trim();
    }
    
    // Extract Phone Number via Regex
    const phoneMatch = body.match(/(?:phone|tel|mobile)[:s]*([+0-9s-()]{7,20})/i);
    const phone = phoneMatch ? phoneMatch[1].trim() : "Not Provided";
    
    // Extract Service Requested
    const serviceMatch = body.match(/(?:service|interest|product)[:s]*([^
]+)/i);
    const service = serviceMatch ? serviceMatch[1].trim() : "General Inquiry";
    
    const snippet = body.substring(0, 150).replace(/[\r\n]+/g, " ");
    
    rowsToAdd.push([date, senderName, senderEmail, phone, service, snippet, threadId]);
    
    // Mark as read and label
    latestMessage.markRead();
    threads[i].addLabel(processedLabel);
  }
  
  if (rowsToAdd.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rowsToAdd.length, rowsToAdd[0].length).setValues(rowsToAdd);
    Logger.log("Successfully appended " + rowsToAdd.length + " new leads.");
  }
}</code></pre>

      <h2>Step 2: Automating with Time-Driven Triggers</h2>
      <p>To run this function autonomously without opening Google Sheets:</p>
      <ol>
        <li>In the Apps Script editor, click the <strong>Triggers</strong> icon (clock symbol) on the left sidebar.</li>
        <li>Click <strong>+ Add Trigger</strong>.</li>
        <li>Choose <code>parseInboundLeadsToSheets</code> as the target function.</li>
        <li>Select event source: <strong>Time-driven</strong>.</li>
        <li>Select type: <strong>Minutes timer</strong> &gt; <strong>Every 10 minutes</strong>.</li>
        <li>Click <strong>Save</strong> and authorize requested Gmail and Sheets permissions.</li>
      </ol>

      <h2>Best Practices & Quotas</h2>
      <ul>
        <li><strong>Batch Append:</strong> Notice how our script collects rows in an array (<code>rowsToAdd</code>) and calls <code>setValues()</code> once. Never call <code>appendRow()</code> inside a loop, as each network round-trip slows execution and burns API quotas.</li>
        <li><strong>Daily Quotas:</strong> Standard <code>@gmail.com</code> accounts have a limit of 20,000 read operations per day, whereas Google Workspace accounts have 50,000. Filtering via <code>is:unread</code> ensures minimal quota consumption.</li>
      </ul>
    

Step-by-step tutorial on building an automated Google Apps Script pipeline that extracts inbound customer inquiries and invoice attachments from Gmail directly into Google Sheets.

Automating Gmail to Google Sheets: Build an Email Lead & Invoice Extractor with Apps Script


      <h2>Introduction</h2>
      <p>Billing operations in small-to-medium businesses frequently rely on manual invoice generation: copying customer details into a template, exporting a PDF, and attaching it to a Gmail draft. By leveraging Google Sheets as a database, Google Docs as an invoice template, and Google Apps Script as the orchestrator, you can fully automate customized PDF invoice creation and email delivery with a single click.</p>

      <h2>The Architecture</h2>
      <p>This automated invoicing engine operates across three integrated Google services:</p>
      <ul>
        <li><strong>Google Sheet:</strong> Houses transaction records (Client Name, Email, Invoice Number, Line Items, Amount, Status).</li>
        <li><strong>Google Doc Template:</strong> Formatted invoice document containing merge tags like <code>{{ClientName}}</code>, <code>{{InvoiceNumber}}</code>, and <code>{{TotalDue}}</code>.</li>
        <li><strong>Google Drive &amp; Gmail:</strong> Automatically converts the merged document into a locked PDF, stores it in an organized Drive folder, and dispatches it via Gmail.</li>
      </ul>

      <h2>Step 1: Create Your Google Doc Invoice Template</h2>
      <p>Create a clean Google Doc with your company logo, bank details, and merge fields formatted exactly like this:</p>
      <ul>
        <li><code>{{InvoiceNumber}}</code></li>
        <li><code>{{InvoiceDate}}</code></li>
        <li><code>{{ClientName}}</code></li>
        <li><code>{{ClientEmail}}</code></li>
        <li><code>{{ServiceDescription}}</code></li>
        <li><code>{{TotalAmount}}</code></li>
      </ul>
      <p>Note the Document ID from the URL bar (e.g., <code>docs.google.com/document/d/&lt;TEMPLATE_ID&gt;/edit</code>).</p>

      <h2>Step 2: The Core Apps Script Code</h2>
      <p>Open your Google Sheet, navigate to <strong>Extensions &gt; Apps Script</strong>, and implement the script:</p>
      <pre><code>function generateAndEmailInvoices() {
  const TEMPLATE_DOC_ID = "YOUR_GOOGLE_DOC_TEMPLATE_ID_HERE";
  const DESTINATION_FOLDER_ID = "YOUR_GOOGLE_DRIVE_FOLDER_ID_HERE";
  const SHEET_NAME = "Invoices";
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  const folder = DriveApp.getFolderById(DESTINATION_FOLDER_ID);
  
  // Headers: [Invoice#, Client Name, Email, Service, Total, Status, PDF Link]
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const invoiceNum = row[0];
    const clientName = row[1];
    const clientEmail = row[2];
    const serviceDesc = row[3];
    const totalAmount = row[4];
    const status = row[5];
    
    // Skip already generated invoices
    if (status === "Sent" || status === "Paid") continue;
    
    // 1. Make a copy of the template
    const templateFile = DriveApp.getFileById(TEMPLATE_DOC_ID);
    const copyFile = templateFile.makeCopy("Invoice_" + invoiceNum + "_" + clientName, folder);
    const copyDoc = DocumentApp.openById(copyFile.getId());
    const body = copyDoc.getBody();
    
    // 2. Replace merge tags
    body.replaceText("{{InvoiceNumber}}", invoiceNum);
    body.replaceText("{{InvoiceDate}}", Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"));
    body.replaceText("{{ClientName}}", clientName);
    body.replaceText("{{ClientEmail}}", clientEmail);
    body.replaceText("{{ServiceDescription}}", serviceDesc);
    body.replaceText("{{TotalAmount}}", "$" + Number(totalAmount).toFixed(2));
    
    copyDoc.saveAndClose();
    
    // 3. Export as PDF
    const pdfBlob = copyFile.getAs("application/pdf").setName("Invoice_" + invoiceNum + ".pdf");
    const pdfFile = folder.createFile(pdfBlob);
    
    // 4. Delete the temporary Google Doc copy
    copyFile.setTrashed(true);
    
    // 5. Send Email via GmailApp
    const emailSubject = "Invoice #" + invoiceNum + " from Your Company";
    const emailBody = "Dear " + clientName + ",\n\nPlease find attached invoice #" + 
      invoiceNum + " for " + serviceDesc + ".\nTotal Due: $" + Number(totalAmount).toFixed(2) + 
      "\n\nThank you for your business!\nYour Company Team";
      
    GmailApp.sendEmail(clientEmail, emailSubject, emailBody, {
      attachments: [pdfBlob],
      name: "Accounts Receivable"
    });
    
    // 6. Update sheet row with status and file link
    sheet.getRange(i + 1, 6).setValue("Sent");
    sheet.getRange(i + 1, 7).setValue(pdfFile.getUrl());
  }
}</code></pre>

      <h2>Step 3: Creating a Custom Menu Button in Sheets</h2>
      <p>Add an <code>onOpen()</code> trigger to let accounting staff run the batch process directly from the spreadsheet top navigation:</p>
      <pre><code>function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("⚡ Invoicing")
    .addItem("Generate & Send Invoices", "generateAndEmailInvoices")
    .addToUi();
}</code></pre>

      <h2>Enterprise Tips</h2>
      <ul>
        <li><strong>Draft Mode:</strong> During initial testing, replace <code>GmailApp.sendEmail()</code> with <code>GmailApp.createDraft()</code> to review emails before they reach real client inboxes.</li>
        <li><strong>PDF Table Formatting:</strong> For invoices with multiple line items, use <code>body.getTables()</code> in Apps Script to dynamically append rows to your invoice table.</li>
      </ul>
    

Learn how to generate customized PDF invoices from Google Sheets data, save them to Google Drive, and automatically email them to clients with Google Apps Script.

How to Auto-Generate & Email PDF Invoices from Google Sheets Using Apps Script


      <h2>Introduction</h2>
      <p>Commercial workflow approval platforms often cost thousands of dollars per month in enterprise SaaS licensing fees. Yet, almost every business already possesses the exact infrastructure required to build seamless approval engines: Google Forms for data collection, Google Sheets as the relational database, Gmail for one-click manager sign-offs, and Google Apps Script for automated state management.</p>

      <h2>Workflow Logic</h2>
      <ol>
        <li>An employee submits an expense reimbursement or time-off request via Google Forms.</li>
        <li>Form submission triggers an Apps Script <code>onFormSubmit</code> event.</li>
        <li>The script generates a secure, unique approval token and emails the designated manager with interactive [Approve] and [Reject] HTML action buttons.</li>
        <li>Clicking a button hits an Apps Script Web App endpoint, updating the spreadsheet status and notifying the employee of the outcome.</li>
      </ol>

      <h2>Step 1: Setting Up the Linked Google Form & Sheet</h2>
      <p>Create a Google Form with the following fields: Employee Name, Employee Email, Expense Amount, Category, Manager Email, and Receipt Upload. Link the form to a destination Google Sheet. The sheet will automatically include a <strong>Timestamp</strong> column alongside your form fields.</p>

      <h2>Step 2: The Approval Web App Script</h2>
      <p>In your spreadsheet's Apps Script editor, implement both the form submission listener and the <code>doGet()</code> web app endpoint:</p>
      <pre><code>// 1. Triggered automatically on Form Submission
function onFormSubmit(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const row = e.range.getRow();
  const values = e.values;
  
  const timestamp = values[0];
  const employeeName = values[1];
  const employeeEmail = values[2];
  const amount = values[3];
  const managerEmail = values[4];
  
  // Generate a random unique verification token
  const approvalToken = Utilities.getUuid();
  sheet.getRange(row, 7).setValue("Pending");
  sheet.getRange(row, 8).setValue(approvalToken);
  
  // Deploy as Web App URL
  const webAppUrl = "YOUR_APPS_SCRIPT_WEB_APP_EXEC_URL";
  const approveLink = webAppUrl + "?action=approve&token=" + approvalToken + "&row=" + row;
  const rejectLink = webAppUrl + "?action=reject&token=" + approvalToken + "&row=" + row;
  
  const htmlBody = `
    &lt;div style="font-family: Arial, sans-serif; padding: 20px;"&gt;
      &lt;h3&gt;New Expense Approval Request&lt;/h3&gt;
      &lt;p&gt;&lt;strong&gt;Employee:&lt;/strong&gt; ${employeeName} (${employeeEmail})&lt;/p&gt;
      &lt;p&gt;&lt;strong&gt;Amount:&lt;/strong&gt; $${amount}&lt;/p&gt;
      &lt;div style="margin-top: 20px;"&gt;
        &lt;a href="${approveLink}" style="background-color: #16a34a; color: white; padding: 10px 20px; text-decoration: none; border-radius: 6px; font-weight: bold; margin-right: 10px;"&gt;Approve Request&lt;/a&gt;
        &lt;a href="${rejectLink}" style="background-color: #dc2626; color: white; padding: 10px 20px; text-decoration: none; border-radius: 6px; font-weight: bold;"&gt;Reject Request&lt;/a&gt;
      &lt;/div&gt;
    &lt;/div&gt;
  `;
  
  GmailApp.sendEmail(managerEmail, "Approval Required: Expense Request from " + employeeName, "", {
    htmlBody: htmlBody
  });
}

// 2. Handles Manager Click on Approve / Reject
function doGet(e) {
  const action = e.parameter.action;
  const token = e.parameter.token;
  const row = parseInt(e.parameter.row);
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const storedToken = sheet.getRange(row, 8).getValue();
  const currentStatus = sheet.getRange(row, 7).getValue();
  
  if (storedToken !== token) {
    return HtmlService.createHtmlOutput("&lt;h2 style='color:red;'&gt;Security Error: Invalid or expired token.&lt;/h2&gt;");
  }
  
  if (currentStatus !== "Pending") {
    return HtmlService.createHtmlOutput("&lt;h2 style='color:orange;'&gt;Notice: This request has already been " + currentStatus + ".&lt;/h2&gt;");
  }
  
  const newStatus = action === "approve" ? "Approved" : "Rejected";
  sheet.getRange(row, 7).setValue(newStatus);
  sheet.getRange(row, 9).setValue(new Date());
  
  // Notify employee
  const employeeEmail = sheet.getRange(row, 3).getValue();
  GmailApp.sendEmail(employeeEmail, "Update: Your Expense Request was " + newStatus, "Your request has been officially " + newStatus + " by your manager.");
  
  return HtmlService.createHtmlOutput("&lt;h2 style='color:green;'&gt;Success! The request has been marked as " + newStatus + ".&lt;/h2&gt;");
}</code></pre>

      <h2>Step 3: Deploying as a Web App</h2>
      <ol>
        <li>Click <strong>Deploy &gt; New deployment</strong>.</li>
        <li>Select type: <strong>Web app</strong>.</li>
        <li>Execute as: <strong>Me (your email)</strong>.</li>
        <li>Who has access: <strong>Anyone</strong> (this allows managers to click links from mobile email clients without re-authenticating).</li>
        <li>Copy the Web App URL and paste it into the <code>webAppUrl</code> variable in your script.</li>
      </ol>
    

Learn how to build an enterprise employee expense or leave approval workflow using Google Forms, Google Sheets, automated Gmail approval buttons, and Apps Script.

Google Apps Script Automation

Automating Gmail to Google Sheets: Build an Email Lead & Invoice Extractor with Apps Script

How to Auto-Generate & Email PDF Invoices from Google Sheets Using Apps Script

Creating Multi-Stage Approval Workflows with Google Forms, Sheets, and Apps Script