Streamline Your Workflow with Google Apps Script: Easily Transfer Gmail Content to Google Sheets

Do you find it time-consuming and tedious to manually transfer information received in Gmail to a Google Sheet?

When you need to extract only the necessary information from numerous emails, copying and pasting manually can significantly lower your work efficiency.

In this article, we’ll introduce a method to automatically transfer Gmail content to a Google Sheet using Google Apps Script.

By leveraging Google Apps Script, you can streamline your tasks and eliminate wasted time.

Example Output

In this example, we focus on retrieving error emails that match specific conditions from Gmail and transferring their content to a Google Sheet.

By setting criteria such as keywords in the subject line or body, as well as the retrieval period, the system reflects the email’s date, sender, subject, body, and extracted keywords in the spreadsheet.

Entering the Required Information in the Spreadsheet

In the search criteria input area at the top (cells B1 to B4), enter the conditions for the emails you want to retrieve.

Next, enter the email fields to be fetched in columns A to E.

検索条件の記入例

• Cell B1：”Error”
• B2セル：”Email delivery system”
• B3セル：Start date of the search period: “2024/10/04”
• B4セル：End date of the search period: “2024/10/10”

Setting Up Google Apps Script

function saveEmailsToSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Get the subject to search for from cell B1
  const searchPhrase = sheet.getRange("B1").getValue();
  if (!searchPhrase) {
    Logger.log("The search phrase is empty.");
    return; // Exit if B1 is empty
  }

  // Get the start and end dates for email retrieval from cells B3 and B4
  const startDate = new Date(sheet.getRange("B3").getValue());
  const endDate = new Date(sheet.getRange("B4").getValue());
  if (isNaN(startDate.getTime()) || isNaN(endDate.getTime())) {
    Logger.log("Invalid start or end date.");
    return; // Exit if the dates are invalid
  }

  // Get the keyword from cell B2
  const keyword = sheet.getRange("B2").getValue();

  // Create a search query using the subject, start date, and end date
  const formattedStartDate = Utilities.formatDate(startDate, Session.getScriptTimeZone(), "yyyy/MM/dd");
  const formattedEndDate = Utilities.formatDate(endDate, Session.getScriptTimeZone(), "yyyy/MM/dd");
  const searchQuery = `subject:"${searchPhrase}" after:${formattedStartDate} before:${formattedEndDate}`;

  // Search Gmail for email threads matching the query
  const threads = GmailApp.search(searchQuery);

  let row = 7; // Start writing data from row 7

  threads.forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(message => {
      const date = message.getDate();
      const from = message.getFrom();
      const subject = message.getSubject();
      const body = message.getPlainBody();

      // Check if the keyword is in the email body, and extract it if present
      let keywordExtract = "";
      if (keyword && body.includes(keyword)) {
        keywordExtract = keyword; // Extract the keyword if it's found in the body
      }

      // Write data to the spreadsheet (starting from row 7)
      sheet.getRange(row, 1).setValue(date);
      sheet.getRange(row, 2).setValue(from);
      sheet.getRange(row, 3).setValue(subject);
      sheet.getRange(row, 4).setValue(body);
      sheet.getRange(row, 5).setValue(keywordExtract); // Write the extracted keyword in column E

      row++;
    });
  });
}

This script transfers emails that match specific conditions in Gmail to a Google Sheet.

By entering search criteria into the designated cells in the spreadsheet, the script retrieves information such as the subject, body, and sender details of emails that meet those conditions.

STEP

Open Apps Script Editor

Open a Google Spreadsheet and click on “Extensions” in the menu, then “Apps Script” to open the Apps Script editor.

STEP

Create the Script

Copy the code above and paste it into the editor.

STEP

Save the Script

After writing the script, name and save it.

(e.g., “Fetch Emails”)

STEP

Run the Script

Run the script to fetch emails that match the specified conditions.

If you’re running the script for the first time, you need to authorize it.

Therefore, press “Review Permissions.“

Detailed Authorization Steps

Press “Advanced.”

Press “Go to Untitled project (Unsafe).”

After that, press “Allow.”

Gmail Fetch Results

When the script is executed, the email data matching the specified conditions will be written to the spreadsheet starting from row 7.

Benefits of Using Google Apps Script

When manually transferring error email content to a spreadsheet, you need to open each email, copy the content, and paste it into the spreadsheet every time.

By using Google Apps Script, all these tasks are automated, significantly reducing the time required.

Conclusion

Manual data entry can be time-consuming, but automating the process with Google Apps Script saves time and allows you to focus on other tasks.

Once the script is set up, transferring data to a spreadsheet becomes effortless. This makes it an excellent solution for anyone looking to streamline their workflow and improve efficiency.

Our company offers support for improving work efficiency through the use of Google Apps Script.
If you need assistance with Google Apps Script customization or error resolution, please feel free to contact us.
We are fully committed to supporting your business improvements.

Contact us here