Filter and Monitor Emails From Google Sheets

How to Filter and Collect Emails into Google Sheets? Google Apps Script Tutorial

I encountered a task on Upwork that involved monitoring multiple Gmail emails. While I couldn’t land the gig, I discovered that using a script can streamline this process considerably. To assist fellow Gmail users, I’ve put together this simple guide on how to effectively monitor your emails automatically directly from a spreadsheet.

TLDR;

1. Create a Search Filter in Gmail.
2. Assign that filter to a Gmail Label.
3. Create a New Spreadsheet, with Five Columns: Date, Email IDs, From, Subject, and Message.
4. Open Google Apps Script from the Extensions tab. Replace the code with the one below.
5. Replace the value, from-my-other-emails for the Email Label in line 1 of the code with your label.
6. Select and Run the first Function from the tab in Apps Script Editor. Give the permissions if asked and run again.
7. Now, create a trigger as instructed in Step 4. Setup A Trigger For Email Collection below.

Done!

Step 1. Create a Label in Gmail:

1. Create a label with a proper name.
2. This label will serve as the destination for emails that are fetched from the monitored accounts.

Step 2. Create a Search Filter and Assign it to the Label

1. Search for a subject/email id/topic in Gmail search.
2. Create a filter and customize it as much as you can.
3. !IMPORTANT Don’t forget to assign this filter the label you created earlier.

Preview On Process to Create Email Filters and Assign It to a Label

Step 3. Create a Google Apps Script:

1. Create a New Spreadsheet, with Five Columns: Date, Email IDs, From, Subject, and Message.

Google Sheets Preview

2. Open the Google Apps Script editor for this spreadsheet and replace the code there with the following code.

const EMAIL_LABEL = "from-my-other-emails";

/**
 * Saves the target email label to the bound Spreadsheet.
 * @returns {void}
 */
function saveLabelledEmailsToSheets() {
  const messages = getLabelledEmails();
  if (messages.length <= 0) return; // return if there's no emails in that label

  // Get the active sheet
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  // get ids for the messages to filter new ones 
  const emailID = sheet.getRange(2, 2, data.length, 1).getValues().flat();


  const newData = [];
  messages.forEach(message => {
    message.forEach(m => {
      if (!emailID.includes(m.getId())) {
        newData.push([m.getDate(), m.getId(), m.getFrom(), m.getSubject(), m.getPlainBody()]);
      }
    });
  });

  if (newData.length <= 0) return; // if new data is empty return

  sheet.getRange(data.length + 1, 1, newData.length, newData[0].length).setValues(newData);
}


/**
 *  * Returns emails from the Gmail inbox in the given Gmail Label
 * @returns {Array<Array>?}
 */
function getLabelledEmails() {
  // Fetch email threads from the Gmail inbox with a label search query 
  const labelThreads = GmailApp.search(`label:${EMAIL_LABEL}`);
  // get all the messages for the current batch of threads
  const messages = GmailApp.getMessagesForThreads(labelThreads);
  return messages;
}

3. This script will handle the monitoring and data transfer process.

4. Before running it make sure to replace the value for const EMAIL_LABEL = "Your EMAIL LABEL";.

Step 4. Setup A Trigger For Email Collection:

To automate this process, let’s trigger this function to repeat once a day.

1. Select Trigger From the Side Bar.
2. For Functions to run, select the saveLabelledEmailsToSheets function.
3. For Event Source, select Time-Driven.
4. For Type of time-based, select Day-Timer.
5. For the Time of the day, I’ll be triggering it during my Midnight. And hit the save button.

Create a New Trigger In Google Apps Script

Conclusion

Following this workflow and logic, you can effectively monitor any email in your Gmail inbox, extract relevant information from emails, and save the data to a Google Sheet. This will allow you to centralize and analyze the emails in a structured and organized manner.

If you want to integrate AI tools like ChatGpt into your sheets to analyze your Email, here’s a Guide I’ve written on Integrating ChatGPT with Google Sheets Using Google Apps Script. You can use it to create your formula, for instance, summarize_email.

PS: Comment your next step after Collecting Emails in your Spreadsheet.

Please do like and share this post. Consider subscribing to the email list to get notified regularly about my posts.

If you want me to customize this project to fit your business needs just let me know. I am Nibesh Khadka, a Google Apps Script consultant. I help my clients achieve their automation goals in Google Workspace.