How to Export Emails from Gmail to Google Sheets Using Apps Script

If you’ve ever wanted to organize your emails into a Google Sheet, automatically categorize and track conversations, or extract specific details from your inbox, you’re in the right place!

Google Apps Script allows you to easily export emails from Gmail and organize them in Google Sheets without manually copying and pasting.

In this post, we’ll walk you through creating a simple Gmail-to-Google Sheets integration using Google Apps Script.

This will allow you to extract emails with a specific Gmail label, along with various details such as the sender, subject, date, and even the entire email body.

Why Export Emails to Google Sheets?

Managing emails can be tedious, especially if you’re dealing with hundreds of messages. Exporting emails into Google Sheets gives you the power to:

• Track specific conversations: Extract emails by labels such as “Work” or “Important.”
• Analyze email data: Identify patterns or trends in communication.
• Organize data for easy access: Store email details for later reference, without digging through your inbox.

How to Export Emails to Google Sheets?

First you will need to make sure you labeled your emails in Gmail. If you don’t know how to do it, you can read this post on How to Label Your Emails in Gmail for Better Organization.

Once your emails have been labeled, follow the steps below:

Step 1: Create Your Google Sheet

• Open Google Sheets.
• Name the sheet appropriately, such as “Email Tracker.”
• You’ll use this sheet as the destination for the email data.

Step 2: Open Apps Script in Google Sheets

• In your Google Sheet, click on Extensions > Apps Script.
• This will open the Google Apps Script editor where you can write code to pull emails.

Step 3: Copy the Script to Extract Gmail Emails

In this example, I’ll be using the “INBOX/YOUTUBE” label, which we use to filter all emails related to comments on our YouTube channel.

Here’s a script that extracts emails from Gmail, based on a specific Gmail label.

function exportEmailsToSheet() { var labelName = ‘YOUR LABEL’; // Replace with your Gmail label var label = GmailApp.getUserLabelByName(labelName); if (label) { var threads = label.getThreads(); var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Clear previous data sheet.clear(); // Set headers sheet.appendRow([‘Date’, ‘Sender’, ‘Subject’, ‘Full Body’]); for (var i = 0; i < threads.length; i++) { var messages = threads[i].getMessages(); for (var j = 0; j < messages.length; j++) { var message = messages[j]; var date = message.getDate(); var sender = message.getFrom(); var subject = message.getSubject(); var fullBody = message.getPlainBody(); // Or use getBody() for HTML format // Append the data to the sheet sheet.appendRow([date, sender, subject, fullBody]); } } Logger.log(‘Emails exported successfully!’); } else { Logger.log(‘Label not found!’); } }

Step 4: Customize the Script

• Replace ‘YOUR_LABEL’ with the Gmail label you want to track, like ‘Work’, ‘Important’, or a custom label.
• Save the script by clicking the floppy disk icon or by pressing Ctrl + S.

Step 5: Run the Script

• After saving, click the Run button to execute the script.
• You will be asked to authorize the script the first time you run it. Just follow the prompts and approve the necessary permissions.
• After the script runs, the emails with the specified label will populate your Google Sheet with details such as the date, sender, subject, and the full body of the message.

Adding Automation: Schedule the Script to Run Daily

Wouldn’t it be great if this process was fully automated? We got ya!

You can set up the script to run daily, capturing new emails and appending them to your Google Sheet.

Here’s how to automate the script:

1. In the Apps Script editor, click on the clock icon in the left sidebar (Triggers).
2. Click the + Add Trigger button.
3. Set it up like this:
   • Choose which function to run: exportEmailsToSheet
   • Select event source: Time-driven
   • Select type of time-based trigger: Day timer
   • Select time of day: Choose the time you prefer (e.g., every morning at 9 AM).

Now, the script will run every day, automatically updating your Google Sheet with the latest emails under the specific Gmail label.

More of a Visual Learner? Watch this Short video on how to use this script

What Data Can You Extract from Gmail?

With Google Apps Script, you can extract various pieces of information from each email.

Here’s a list of some of the data points you can pull into Google Sheets:

• Date: When the email was sent (getDate()).
• Sender: The email address of the sender (getFrom()).
• Recipients: The email addresses of the recipients (getTo()).
• CC: CC recipients (getCc()).
• BCC: BCC recipients (getBcc()).
• Subject: The subject of the email (getSubject()).
• Body (Plain Text): The plain text body of the email (getPlainBody()).
• Body (HTML): The HTML version of the email body (getBody()).
• Snippet: A short preview of the email (getSnippet()).
• Thread ID: The unique identifier for the email thread (getThread().getId()).
• Message ID: The unique identifier for the individual email (getId()).
• Starred: Whether the email is starred (isStarred()).
• Unread: Whether the email is unread (isUnread()).
• Important: Whether the email is marked as important (isImportant()).
• Attachments: The file names of email attachments (getAttachments()).

You can customize the script further to extract only the data that’s relevant to your needs.

Want this script already integrated into Google Sheets? Download it here 👇

Practical Use Cases

This integration between Gmail and Google Sheets opens up many possibilities:

1. Customer Support Tracking: You can track support requests and responses by extracting emails from your support inbox into Google Sheets.
2. Sales Leads: Extract emails from your “Sales” label and organize lead information in Sheets for easy follow-up.
3. Project Management: Track conversations and progress for specific projects by extracting emails labeled for different projects.
4. Content Creation: Collect content submission emails from contributors or clients, and keep them organized in a Sheet for review, etc.

How to Use ChatGPT to Tweak Your Script (No Coding Skills Required!)

If you’re not familiar with coding but want to make changes to your script, no worries! You can use ChatGPT to help customize the script to fit your needs, even if you’re not a developer.

How to Ask ChatGPT for Help with the Script

To make sure you get the most useful response, here’s how you can ask ChatGPT to help you tweak or modify your script:

1. Explain Your Goal Clearly: When asking for help, be clear about what you want to achieve. For example, you might want to extract additional data from the email, change the time the script runs, or filter emails by a specific condition.

2. Provide the Existing Script: If you already have a working script, share it with ChatGPT. This helps the AI understand the context and makes it easier to suggest specific improvements or changes.

3. Specify the Customization You Need: Be specific about the modification you’re looking for. Here are some examples of how you can phrase your request:
   • “I want to extract email attachments and save the file names in my Google Sheet.”
   • “Can you help me modify this script to pull only unread emails?”
   • “I’d like this script to run every 6 hours instead of once a day. How do I change that?”

4. Test the Script: Once ChatGPT provides a modified version of your script, you can copy and paste it into your Google Apps Script editor. Test it to see if it works as expected.

Example ChatGPT Request:

You could ask ChatGPT something like:

I have a Google Apps Script that exports emails from Gmail to Google Sheets. Here’s the code I’m using: [INSERT YOUR SCRIPT HERE] I’d like to modify it so that it only pulls emails from the past week. Can you help me adjust the script to include this filter?

Or, if you’re unsure about something specific, you could say:

I don’t know how to extract email attachments with my current Google Apps Script. Can you help me add this functionality?

Love this post? pin one of these images below and save it for later!