Gmail AutomationDifficulty: Beginner⚡ Time-driven trigger (Every 10 minutes)

Extract Inbound Gmail Leads to Google Sheets Automatically

Scans unread customer inquiries in Gmail labeled "Leads", extracts the sender, timestamp, subject, and message body snippet, and appends them to a Google Sheet before marking the thread read.

Required OAuth Scopes: https://www.googleapis.com/auth/gmail.modify • https://www.googleapis.com/auth/spreadsheets
Code.gs (Apps Script V8 Runtime)
function extractGmailLeadsToSheets() {
  const query = 'label:leads is:unread';
  const threads = GmailApp.search(query, 0, 20);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');
  
  if (!sheet) {
    throw new Error('Tab named "Leads" not found in active spreadsheet');
  }

  threads.forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(msg => {
      if (msg.isUnread()) {
        const date = msg.getDate();
        const from = msg.getFrom();
        const subject = msg.getSubject();
        const bodySnippet = msg.getPlainBody().substring(0, 300);
        
        sheet.appendRow([date, from, subject, bodySnippet]);
        msg.markRead();
      }
    });
  });
}

Line-by-Line Code Explanation

Line 2: Defines search filter searching specifically for unread messages with the Gmail label "leads".
Line 3: Fetches up to 20 matching email threads in a single API call to stay within execution quotas.
Line 4-7: Resolves the target spreadsheet tab and validates existence to avoid null pointer exceptions.
Line 9-18: Iterates through threads and individual messages, extracting metadata and writing a row in Sheets.
Line 19: Marks the email message as read so it will not be processed again on the next trigger cycle.

Step-by-Step Setup & Deployment

  1. Create a label in Gmail named "leads" and apply it to incoming customer inquiry emails via Gmail Filters.
  2. In Google Sheets, navigate to Extensions > Apps Script and paste the snippet code into Code.gs.
  3. Create a sheet tab named "Leads" with columns: Date, From, Subject, Body Snippet.
  4. Click Triggers (clock icon) > Add Trigger > Select extractGmailLeadsToSheets > Time-driven > Every 10 minutes.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

  • Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
  • Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
  • OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Gmail Automation Snippets