Gmail to Google Sheets automatically, with and without code

If you copy details from Gmail into Google Sheets by hand, three tools can do it for you. A free Apps Script runs inside the sheet; Zapier and Google’s Workspace Studio need no code. The script route has one trap. Gmail hands scripts whole conversations, not single emails, so a script that labels each conversation can lose the second email in it.

We wrote the script below and ran it on 13 sample emails we built, using stand-ins for Gmail and Sheets in Node.js, because our sandbox has no Google account. In that test a simple label-based script missed 2 of the 13 emails and wrote 4 rows wrong. Our script got all 13. Below are the steps for each route, the code, and the limits Google and Zapier document, all read on October 20, 2026.

Gmail gives scripts conversations, not emails

Apps Script reaches Gmail through GmailApp.search(), which returns threads, the conversations you see in your inbox. Gmail groups replies into one conversation, up to 100 emails before it starts a new one.

A simple script does three things. It searches for unprocessed mail, reads the first message of each thread, and marks the thread with a label such as Processed. That label is the weak spot. Google’s reference says addToThread() “adds this label to the given thread”, so the mark sits on the whole conversation.

So when a second email joins a thread that is already marked, the script has two choices, and both are wrong. If the search skips labeled threads, the new email never reaches the sheet. If the search returns the thread again, the script reads its first message a second time and writes a duplicate. We could not check which of the two real Gmail does, so we tested both.

The fix is to stop marking threads. Our script reads every message in each matching thread and writes the message’s own ID in column A. Before it writes a row, it checks whether that ID is already in the sheet.

Our test: 13 sample emails, two scripts, no Google account

We built 11 threads with 14 messages: 13 web-form notifications from one sender and 1 reply from a colleague. The names, companies and addresses are invented, on .example domains. The cases:

Emails	What they test
1, 6, 8	Plain Label: value lines; email 6 has a phone starting with +, email 8 one starting with 0
2	A message three lines long
3	No phone line
4	Company Name: above Name:
5	Each value on the line below its label
7	A message field that starts with =HYPERLINK(
9	The label spelled E-mail:
10, 12, 14	Three form emails in one conversation, with a colleague’s reply (email 11) between the first two
13	A CSV file and a logo attached

We could not run Apps Script itself. So we ran the code unchanged in Node.js 22 with small stand-ins for GmailApp, SpreadsheetApp, ScriptApp and Utilities that follow Google’s reference. That tests our logic, not Google’s servers. Gmail’s own conversion of HTML email to plain text, its search, and real quotas were not part of it.

We compared our script with a short label-based script that does those three things. Both ran once, then again after email 14 arrived in the old conversation:

Method	Emails with a row (of 13)	Rows fully right	What went wrong
Label-based script, labeled thread skipped	11	6	Emails 12 and 14 missed; 4 rows wrong; 1 formula
Label-based script, labeled thread returned	11, plus 1 duplicate	6	Same, and email 10 written twice
Our script	13	13	Nothing in this run

The 4 wrong rows: email 2 lost two lines of its message, email 4 got Dahl CPA as the person’s name, email 5 came out blank, and email 9 had no email address. The formula is email 7. Google’s reference for both appendRow() and setValues() says: “If a cell’s content begins with =, it’s interpreted as a formula.” Anyone who can fill in your form can put a working formula in your sheet.

One caveat is ours. We wrote the script while looking at these cases, so 13 of 13 is a best case. A sender with a layout we did not plan for will break it until you add the label.

1. Pick the route by account and budget

	Apps Script	Zapier	Workspace Studio
Cost	Free with any Google account	Free: 100 tasks a month; Professional from $19.99 a month billed yearly	Available in eligible Workspace editions
Personal Gmail	Yes	Not checked by us	Only through Google Workspace Experiments
How fast	Every 1 to 30 minutes	Checks every 15 minutes on Free, 2 on paid plans	When the email arrives
Splits fields	Yes, with the labels you list	Needs a third step, so a paid plan	Yes, with its AI Extract step
Old mail	Yes, anything the search finds	New mail, as the trigger’s name says	New mail only
Main limit	90 minutes of trigger runtime a day on personal accounts	Tasks per month	25 flows; a daily run limit

Our view: start with the script if you have a personal Gmail account or the emails follow a fixed layout. It is free, it backfills old mail, and you can read what it does. Use Workspace Studio if your company is on an eligible Workspace edition and the layouts vary. Use Zapier when the data must also go to tools outside Google.

2. Install the script in the sheet

1. Open the target spreadsheet and choose Extensions, then Apps Script. A script made this way belongs to that sheet.
2. Paste the code below over the empty myFunction, then save.
3. Edit CONFIG. Set from to the sender’s address and query to a Gmail search, for example from:noreply@yourform.example newer_than:7d. Under fields, list every label your senders use for each column.
4. Select setup in the toolbar and choose Run. It creates a time-driven trigger and fills the sheet from the mail the search finds. Google asks you to authorize access first: every GmailApp method needs the full https://mail.google.com/ scope.
5. Check the sheet. A Leads tab appears with one row per email. The Missing column names any field the script did not find.

// Gmail to Google Sheets: copy labeled fields from matching emails into a sheet.
// Paste into Extensions > Apps Script in the target spreadsheet, edit CONFIG, run setup() once.
const CONFIG = {
  from: 'noreply@yourform.example',       // only messages from this sender; '' for any sender
  query: 'from:noreply@yourform.example newer_than:7d', // Gmail search, same syntax as the search box
  sheet: 'Leads',
  fields: {                                // column: the labels senders use for it
    Name:    ['Name', 'Full name'],
    Email:   ['Email', 'E-mail'],
    Phone:   ['Phone', 'Mobile'],
    Company: ['Company', 'Company name'],
    Message: ['Message', 'Comments'],
  },
  multiline: ['Message'],                  // these keep reading until the next label
  csvSheet: 'CSV import',                  // rows from .csv attachments go here; '' to skip
};
const QUOTE = /^(-{2,}\s*(Forwarded message|Original Message)\s*-{2,}|On .+ wrote:)$/im;

function setup() {                         // run once: creates the sheets and a 15-minute trigger
  ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));
  ScriptApp.newTrigger('run').timeBased().everyMinutes(15).create();
  run();
}

function run() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = getSheet(ss, CONFIG.sheet, ['Message ID', 'Received', 'From', ...Object.keys(CONFIG.fields), 'Missing']);
  const seen = new Set(sheet.getRange(1, 1, Math.max(1, sheet.getLastRow()), 1).getValues().map(r => String(r[0])));
  const rows = [], csvRows = [];
  let csvHeader = [];
  for (const thread of GmailApp.search(CONFIG.query, 0, 100)) {
    for (const msg of thread.getMessages()) {   // every message, so a reply in an old thread still counts
      const id = msg.getId();
      if (seen.has(id) || !msg.getFrom().toLowerCase().includes(CONFIG.from.toLowerCase())) continue;  // done, or a reply from you
      seen.add(id);
      const row = parse(msg.getPlainBody());
      const cols = Object.keys(CONFIG.fields);
      rows.push([id, msg.getDate(), msg.getFrom(), ...cols.map(c => asText(row[c] || '')),
                 cols.filter(c => !row[c]).join(' ')]);
      if (CONFIG.csvSheet) {
        for (const file of msg.getAttachments({includeInlineImages: false})) {
          if (!/\.csv$/i.test(file.getName())) continue;
          const text = file.getDataAsString('UTF-8').replace(/^\uFEFF/, '');  // drop Excel's byte order mark
          const table = Utilities.parseCsv(text);
          csvHeader = table[0];
          table.slice(1).forEach(r => csvRows.push([id, file.getName(), ...r.map(asText)]));
        }
      }
    }
  }
  write(sheet, rows);
  if (csvRows.length) write(getSheet(ss, CONFIG.csvSheet, ['Message ID', 'File', ...csvHeader]), csvRows);
  console.log(`added ${rows.length} rows, ${csvRows.length} CSV rows`);
}

function parse(text) {
  const parts = text.split(QUOTE);
  if (parts.length > 1) text = parts[parts.length - 1];    // forwarded or replied: read only the original
  const labels = {};
  for (const [col, ls] of Object.entries(CONFIG.fields)) ls.forEach(l => labels[l.toLowerCase()] = col);
  const alts = Object.keys(labels).sort((a, b) => b.length - a.length).map(l => l.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'));
  const rx = new RegExp('^\\s*(' + alts.join('|') + ')\\s*:\\s*(.*)$', 'i');
  const row = {};
  let col = null;
  for (const raw of text.split(/\r?\n/)) {
    const line = raw.replace(/^(>\s?)+/, '').trim(), hit = line.match(rx);
    if (hit) {
      col = labels[hit[1].toLowerCase()];
      if (col in row) { col = null; continue; }            // keep the first value of each field
      row[col] = hit[2].trim();
    } else if (col && line) {
      if (!row[col]) row[col] = line;                      // value on the line below its label
      else if (CONFIG.multiline.includes(col)) row[col] += ' ' + line;
      else col = null;
    } else if (!line && col && row[col]) col = null;
  }
  return row;
}

function asText(v) {        // the apostrophe keeps =, +, -, @ and leading zeros as typed text
  v = String(v);
  return /^[=+\-@]|^0\d/.test(v) ? "'" + v : v;
}

function getSheet(ss, name, header) {
  let sheet = ss.getSheetByName(name);
  if (!sheet) { sheet = ss.insertSheet(name); sheet.appendRow(header); }
  return sheet;
}

function write(sheet, rows) {  // one call for all rows: much faster than appendRow in a loop
  if (!rows.length) return;
  const width = Math.max(...rows.map(r => r.length));
  rows = rows.map(r => r.concat(Array(width - r.length).fill('')));
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, width).setValues(rows);
}

How the script handles the traps

Labels match only at the start of a line, longest first, so Name doesn’t match inside Company Name. An empty label line takes the next line as its value. A message from anyone other than from is skipped, so your own replies in the thread never become rows.

The apostrophe in asText is there to keep such values as text. Our stand-in can’t show how a live sheet treats it, so check one row with a + phone number after your first run.

Rows are written in one setValues() call per run rather than one call per row. Google’s best-practices page says “Your scripts will run faster if you minimize service calls.” In its example, 10,000 cells written one at a time took about 70 seconds; batched, 1 second.

What it printed on our sample emails

This is the real output of three runs in our stand-in: the first run, a second run with no new mail, and a third after email 14 arrived in the old conversation.

--- run 1 (setup)
trigger: run every 15 minutes
added 12 rows, 2 CSV rows
--- run 2, no new mail
added 0 rows, 0 CSV rows
--- run 3, one new form email lands in an old conversation
added 1 rows, 0 CSV rows

Row 3 came out as Cara Lund | cara@lund.example | | Lund Bakery | No phone, email me. | Phone: a blank phone, and the gap named in Missing instead of hidden. Email 7’s message was written as '=HYPERLINK(...), as text.

Know Google’s quotas before the trigger runs all day

Apps Script limits are per user, and they reset “24 hours after the first request”, according to Google’s quotas page (updated September 3, 2026):

Limit	Personal Gmail	Google Workspace
Trigger runtime, total	90 minutes a day	6 hours a day
Gmail read and write (not sending)	20,000 a day	50,000 a day
One run	6 minutes	6 minutes
Triggers	20 per user per script	20 per user per script

The runtime line matters most. A 15-minute trigger runs 96 times a day, so on a personal account each run must average under 56 seconds (5,400 seconds divided by 96) to stay inside 90 minutes. If your runs take longer, set the trigger to 30 minutes in setup. everyMinutes() accepts only 1, 5, 10, 15 or 30.

When a quota runs out, the script stops with an error such as “Service using too much computer time for one day”. A failed trigger sends you an email titled “Summary of failures for Apps Script”, and the project’s Executions page shows each run.

Three more limits come from the code itself:

• 100 threads a run. The script asks for at most 100 threads per search. If more than 100 matching conversations arrive between runs, narrow the query or run more often.
• The trigger runs as you. Installable triggers “always run under the account of the person who created them”. It reads your mailbox, not a shared one, and it stops if your account goes.
• The sheet has a ceiling. Google Sheets holds up to 20 million cells. At 9 columns that is about 2.2 million rows, more than most inboxes will fill.

3. Pull CSV attachments into their own tab

Some systems email a CSV report every morning instead of one email per record. The script handles that too. For every attachment whose name ends in .csv, it parses the text with Utilities.parseCsv(). The rows go to a tab named CSV import, after the message ID and file name.

Two details came from our test file. A CSV saved by Excel as UTF-8 can start with an invisible byte order mark, which the script strips so the first header doesn’t carry it. And the test file had a quoted value with a comma, "Lind, Anna", which landed in one cell as it should.

The script skips inline images such as logos by passing includeInlineImages: false to getAttachments(); Google’s default includes them. Set csvSheet to '' if you don’t want this part.

4. Without code: Zapier

Zapier’s Gmail and Google Sheets page lists the pieces: the trigger New Email Matching Search and the action Create Spreadsheet Row.

1. Create a Zap with Gmail, trigger event New Email Matching Search, and enter the same search you would type in Gmail, for example from:noreply@yourform.example.
2. Add Google Sheets, action event Create Spreadsheet Row. The sheet needs a header row first. Map the email’s date, sender and body to columns.
3. Test, then publish.

That two-step Zap fits the Free plan: 100 tasks a month, two-step Zaps only, and a check for new mail every 15 minutes. Zapier counts a task as “any successful action”, and triggers are free, so each new row costs one task. A form that brings in five leads a day uses about 150 tasks a month and outgrows the free plan.

Splitting the body into Name, Phone and the rest takes a step in the middle. Zapier says its Formatter steps don’t count as tasks, but a three-step Zap needs a paid plan. Professional starts at $19.99 a month billed yearly for 750 tasks, or $29.99 billed monthly, and checks every 2 minutes. If you only want the whole email body in one cell, the free plan is enough.

When you run out of tasks without pay-per-task billing, Zapier “holds new Zap runs until your billing cycle resets”. They are not lost; you can replay them later. Note that your email text passes through Zapier’s servers, which the Apps Script route avoids.

5. Without code: Workspace Studio

Google’s own no-code tool, Workspace Studio, builds a flow from a starter and steps. Its guide to starters and steps lists the three you need:

1. Starter: When I get an email. Filter on From, Subject has, Has the words or Has attachment. Custom labels can’t be used here; only Gmail’s default labels work.
2. Step: Extract. Pick the email as content and name what to pull out (name, phone, company). This is an AI step, so it copes with layouts that move around.
3. Step: Add a row. Pick the spreadsheet and tab, and map each extracted item to a column.

It is the only route of the three that reacts when the email arrives instead of checking on a timer. The limits, from Google’s limits page: at most 25 flows per person, 20 steps per flow, and 25 active flows started from Gmail. A daily run limit covers all your flows together; the page doesn’t give the number. When you hit it, “your active flows stop running” until it resets 24 hours later.

The catch is access. It works on business, enterprise and education editions whose admin has turned on Gemini, and on personal accounts only through Google Workspace Experiments. It reacts to new mail, so it won’t backfill the emails already in your inbox.

Extract is AI, too: a wrong value looks exactly like a right one, so spot-check rows against their emails for the first week. We could not test it without a Google account.

Check the Missing column each week

Whichever route you pick, the failure to fear is the quiet one: a row that arrives half empty, or an email that never arrives at all. Sort the sheet by Missing once a week and open the emails behind any entry. If one sender’s rows start missing fields, its layout changed and you need a new label in CONFIG.

For the Outlook and Excel side of the same job, see email data into Excel, which tests the same field-splitting idea on 20 emails. Before you build, our one-week tally shows whether this is the task worth automating first. Once the sheet fills, the weekly report guide can send a summary of it on a schedule, and our checks for scheduled scripts make a silent failure speak up. More guides are under automation.

Start by sending your own form to yourself twice, once with the phone left out, and run setup. Two rows with one Missing entry mean your labels match. Then reply to one of those emails and run it again: no new row should appear.

Sources

• Google, GmailApp reference (search, authorization scope; updated April 13, 2026)
• Google, GmailLabel reference (labels apply to threads)
• Google, GmailMessage reference (getPlainBody, getAttachments)
• Google, Sheet reference and Range reference (appendRow, setValues, formulas)
• Google, Utilities reference (parseCsv)
• Google, ClockTriggerBuilder reference (everyMinutes)
• Google, Quotas for Google Services (updated September 3, 2026)
• Google, Installable triggers (updated September 3, 2026)
• Google, Container-bound scripts
• Google, Apps Script best practices
• Google, Gmail search operators and conversation view
• Google, Files you can store in Google Drive (Sheets cell limit)
• Google, Workspace Studio help: overview, starters and steps, limits
• Zapier, Gmail and Google Sheets integrations, pricing and Tasks in Zapier
• 28creation test, October 20, 2026: 14 sample messages in 11 threads, built by us; our script and a label-based script run in Node.js 22 against stand-ins for Google’s services, not in Apps Script or a live Gmail account