Email to Google Sheets: How to automate Gmail data extraction

Your email inbox is a treasure trove of valuable business data. It holds everything—from customer inquiries, sales orders, invoices, and shipping notifications—but let's be honest: trying to make sense of all that information when it's scattered across countless email accounts and threads is a nightmare. That's where converting your emails to Google Sheets helps.

In this guide, we'll walk you through how to export data from email to Google Sheets. Whether you're a sales executive looking to keep tabs on leads, a customer service specialist aiming to resolve tickets faster, or an accountant looking to process invoices, efficiently exporting emails to Google Sheets is the way to go.

Export email data into sheets in a snap!

Handle various email formats and data variations without the need for templates or rules. Extract data from attachments, transform data into your desired format, and automate your entire email data extraction workflow.

Start Parsing Emails

How to export email to sheets

We will be covering three methods to help you export email data to Google Sheets–

1. Emails to Sheets by CloudHQ

CloudHQ’s export email to sheets lets you parse and extract specifics from emails, like resume or invoice details, and backup your Gmail messages, including attachments to Google Sheets. It is available as a Gmail Add-on or as a Chrome Web Extension. Here’s how it works:

1. Visit the Chrome Web store to install Export Email to Sheets by CloudHQ extension. NOTE: you can also install Chrome Extensions on Microsoft Edge or Brave Browser.
2. Once installed, you will see a confirmation screen and a CloudHQ icon will appear on your Gmail account.
3. You will need to Sign Up to CloudHQ to use the extension. You can do so by clicking on Create Account and using your Gmail account to sign.
4. Provide required permissions and Click on Allow.
5. Select your emails and click on CloudHQ icon -> Save selected email messages to a new spreadsheet.
6. This will open up a new pop-up window. Enter the Spreadsheet name.
7. Select the information that you wish to extract from the emails and click on Next.
8. View Summary of Steps and Click on Save to Spreadsheet.
9. Once successful, the following window opens.
10. Click on Open the Spreadsheet and check the Export. Emails have been exported successfully.

Gmail to Google Sheets using Apps Script

Apps Script is a scripting language that can be used to create applications to integrate Google Workspace Applications with each other and automate tasks. We will be using Apps Script to parse email to google sheets.

1. Create a New Google Spreadsheet and name it Mails.

2. Click on Extensions -> Apps Script to open Apps Script editor.

3. The Apps Script editor opens up in a new browser Tab.

4. For now, we want to export the top five emails for the below search term.

5. Copy the code snippet. Paste it into Apps Script and modify it accordingly as per your use case.

   function onOpen ( ) {
    SpreadsheetApp.getUi( ).createMenu("Custom").addItem( "Bring Items", "bringEmails").addToUi();
   }
   
   
   function bringEmails ( ) {
    var searchQuery = 'zapier';
    var ws = SpreadsheetApp.getActiveSpreadsheet()
    var ss = ws.getSheetByName( "Mails")
    var emails=GmailApp.search(searchQuery, 0, 5)
    Logger.log(emails)
    var msgTxt = emails[4].getMessages()
    emails.forEach ( email => {
      var messageThread = email.getMessages()
      var msgTxt = messageThread [0].getPlainBody()
      var msgDate = messageThread[0].getDate()
      var msgSubject = messageThread[0].getSubject()
      var msgSender = messageThread [0].getFrom()
      ss.appendRow ( [msgDate,msgSender,msgSubject,msgTxt] )
   })
   }
   

   a. For example, if you want to get emails from inbox, type in label:inbox . For starred emails, use is:starred. Basically you need to type in your search string from Gmail.
   b. We are only exporting the date, sender, subject, and body of the email. You can select other email attributes that you wish to export. Just create a variable using these methods (getBcc, getBody, etc.) and add the variable name to the appendRow method.

6. Once you have copied the code, click on Save -> Run. Ensure that the onOpen function is selected.

7. An Authorization window opens up. Click on Review Permissions->Choose your Gmail Account->Allow to authorize the connection via Apps Script.

8. Upon running Successfully, you shall see Custom option. Click on ‘Bring Items’ to export emails to google sheet.

9. Emails are parsed and exported successfully to Google Sheets.

3. Email to Google Sheets using Nanonets

With Nanonets, you can handle various email formats without needing templates or rules. It offers automated workflow features like email ingestion, data validation, enrichment, and integration with other apps. You'll be able to set up a fully automated Gmail to Sheets pipeline in just a few clicks.

1. Sign up or log in to your Nanonets account at app.nanonets.com.

2. Choose a pre-trained model based on your document type (e.g., invoices, receipts, purchase orders) and create your email data extractor within minutes.

3. Verify the data extracted by Nanonets using an email screenshot or a file. Your email data extraction model is ready now.

4. Navigate to the Workflow section in the left navigation pane and go to the import tab.

5. Click on "Receive files via Email" and find the auto-generated email address created by Nanonets in the expanded view.

6. Once the integration is set up, all new incoming emails will be imported into Nanonets, processed by your model, and have data extracted from them.

7. Set up auto-forwarding from your Gmail account to Nanonets using the unique receiving email address provided for your model. Nanonets will automatically import the forwarded emails for data extraction.

8. Nanonets will parse the relevant data from the email body, subject line, sender information, and attachments. It supports a wide range of attachment formats, including PDFs, images, CSV, Excel, JSON, and XML files. You can verify the extracted data and make corrections if needed, then approve the data extraction.

9. Integrate Nanonets with your Google Sheets account. Map the extracted data fields to the corresponding columns in your Google Sheet.

10. Set up the export trigger in Nanonets. You can choose to export emails to Google Sheets automatically upon extraction or only after manual approval.

11. As new emails arrive, they will be automatically forwarded to Nanonets, the data extracted and the approved data will be exported to your Google Sheet in real-time.

Alternatively, you can look at the demo below to get started and set up your email data extraction workflow.

Your Nanonets email data extraction model will now automatically look for incoming emails, parse the information from the body and attachments, and export it to Google Sheets. You won't have to make it a periodic task to update the spreadsheet—it will happen automatically in the background. Moreover, you don't need to write code or complicated scripts to get started.

Once the automated workflow is in place, your Google Sheets will be updated with the latest data from Gmail. This saves you time and helps you be more proactive in managing your business processes. Nanonets is also GDPR, SOC-2, and CCPA compliant and employs advanced encryption techniques to safeguard your data, ensuring it remains secure and confidential.

Final thoughts

In 2022, approximately 333 billion emails were exchanged daily worldwide. As global email users are projected to reach 4.6 billion by 2025, email volume will continue to grow. Without automation, businesses will struggle to keep up with the deluge of data trapped in emails — customer support, sales, finance, and HR teams will spend more time on manual data entry and less on tasks that move the needle.

Take time to evaluate your email workflows and identify opportunities for automation. With Nanonets, you can easily set up email-to-Google Sheets workflows that extract data from emails and attachments, saving your team countless hours of manual data entry. Plus, you can use the extracted data to automate workflows in other apps, ensuring data accuracy and compliance.

Discover how Nanonets can transform your business.

Book a demo to see how businesses across various industries are using Nanonets to save time, reduce manual data entry, and increase efficiency. From saving 90% of time for accounts payable to reducing manual data entry by 95%, Nanonets has helped thousands of companies achieve their automation goals.

📅 See Nanonets in Action