{"id":17045,"date":"2025-01-05T12:53:02","date_gmt":"2025-01-05T03:53:02","guid":{"rendered":"https:\/\/84office.jp\/?p=17045"},"modified":"2025-01-05T12:53:03","modified_gmt":"2025-01-05T03:53:03","slug":"gmail-transcript","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/gmail-transcript\/","title":{"rendered":"Streamline Your Workflow with Google Apps Script: Easily Transfer Gmail Content to Google Sheets"},"content":{"rendered":"
<\/div>\n

Do you find it time-consuming and tedious to manually transfer information received in Gmail to a Google Sheet?<\/p>\n\n\n\n

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

In this article, we\u2019ll introduce a method to automatically transfer Gmail content to a Google Sheet using Google Apps Script.<\/p>\n\n\n\n

By leveraging Google Apps Script, you can streamline your tasks and eliminate wasted time.<\/p>\n\n\n\n

Example Output<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n

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

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.<\/p>\n\n\n\n

Entering the Required Information in the Spreadsheet<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n

In the search criteria input area at the top (cells B1 to B4), enter the conditions for the emails you want to retrieve.<\/p>\n\n\n\n

Search Criteria Input Area at the Top (B1 to B4)<\/strong><\/strong><\/th><\/tr><\/thead>
Cell B1<\/td>Keyword included in the email subject (e.g., “Error”)<\/td><\/tr>
Cell B2<\/td>Keyword included in the email body (e.g., “Email delivery system”)<\/td><\/tr>
Cell B3<\/td>Start date for the emails to retrieve (emails within the specified date range will be extracted)<\/td><\/tr>
Cell B4<\/td>End date for the emails to fetch<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Next, enter the email fields to be fetched in columns A to E.<\/p>\n\n\n\n

Columns A to E<\/th><\/tr><\/thead>
Column A<\/td>Email date<\/td><\/tr>
Column B<\/td>Sender’s email address<\/td><\/tr>
Column C<\/td>Email subject<\/td><\/tr>
Column D<\/td>Email body<\/td><\/tr>
Column E<\/td>Extracted keyword from the email body, if a keyword is included<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

\u691c\u7d22\u6761\u4ef6\u306e\u8a18\u5165\u4f8b<\/strong><\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n
    \n
  • Cell B1\uff1a”Error”<\/li>\n\n\n\n
  • B2\u30bb\u30eb\uff1a”Email delivery system”<\/li>\n\n\n\n
  • B3\u30bb\u30eb\uff1aStart date of the search period: “2024\/10\/04”<\/li>\n\n\n\n
  • B4\u30bb\u30eb\uff1aEnd date of the search period: “2024\/10\/10”<\/li>\n<\/ul>\n\n\n\n

    Setting Up Google Apps Script<\/strong><\/h2>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    function<\/span> <\/span>saveEmailsToSheet<\/span>() {<\/span><\/span>\n  <\/span>const<\/span> sheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>getActiveSheet<\/span>();<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Get the subject to search for from cell B1<\/span><\/span>\n  <\/span>const<\/span> searchPhrase <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>"B1"<\/span>).<\/span>getValue<\/span>();<\/span><\/span>\n  <\/span>if<\/span> (<\/span>!<\/span>searchPhrase) {<\/span><\/span>\n    Logger.<\/span>log<\/span>(<\/span>"The search phrase is empty."<\/span>);<\/span><\/span>\n    <\/span>return<\/span>; <\/span>\/\/ Exit if B1 is empty<\/span><\/span>\n  }<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Get the start and end dates for email retrieval from cells B3 and B4<\/span><\/span>\n  <\/span>const<\/span> startDate <\/span>=<\/span> <\/span>new<\/span> <\/span>Date<\/span>(sheet.<\/span>getRange<\/span>(<\/span>"B3"<\/span>).<\/span>getValue<\/span>());<\/span><\/span>\n  <\/span>const<\/span> endDate <\/span>=<\/span> <\/span>new<\/span> <\/span>Date<\/span>(sheet.<\/span>getRange<\/span>(<\/span>"B4"<\/span>).<\/span>getValue<\/span>());<\/span><\/span>\n  <\/span>if<\/span> (<\/span>isNaN<\/span>(startDate.<\/span>getTime<\/span>()) <\/span>||<\/span> <\/span>isNaN<\/span>(endDate.<\/span>getTime<\/span>())) {<\/span><\/span>\n    Logger.<\/span>log<\/span>(<\/span>"Invalid start or end date."<\/span>);<\/span><\/span>\n    <\/span>return<\/span>; <\/span>\/\/ Exit if the dates are invalid<\/span><\/span>\n  }<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Get the keyword from cell B2<\/span><\/span>\n  <\/span>const<\/span> keyword <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>"B2"<\/span>).<\/span>getValue<\/span>();<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Create a search query using the subject, start date, and end date<\/span><\/span>\n  <\/span>const<\/span> formattedStartDate <\/span>=<\/span> Utilities.<\/span>formatDate<\/span>(startDate, Session.<\/span>getScriptTimeZone<\/span>(), <\/span>"yyyy\/MM\/dd"<\/span>);<\/span><\/span>\n  <\/span>const<\/span> formattedEndDate <\/span>=<\/span> Utilities.<\/span>formatDate<\/span>(endDate, Session.<\/span>getScriptTimeZone<\/span>(), <\/span>"yyyy\/MM\/dd"<\/span>);<\/span><\/span>\n  <\/span>const<\/span> searchQuery <\/span>=<\/span> <\/span>`subject:"<\/span>${<\/span>searchPhrase<\/span>}<\/span>" after:<\/span>${<\/span>formattedStartDate<\/span>}<\/span> before:<\/span>${<\/span>formattedEndDate<\/span>}<\/span>`<\/span>;<\/span><\/span>\n<\/span>\n  <\/span>\/\/ Search Gmail for email threads matching the query<\/span><\/span>\n  <\/span>const<\/span> threads <\/span>=<\/span> GmailApp.<\/span>search<\/span>(searchQuery);<\/span><\/span>\n<\/span>\n  <\/span>let<\/span> row <\/span>=<\/span> <\/span>7<\/span>; <\/span>\/\/ Start writing data from row 7<\/span><\/span>\n<\/span>\n  threads.<\/span>forEach<\/span>(<\/span>thread<\/span> <\/span>=><\/span> {<\/span><\/span>\n    <\/span>const<\/span> messages <\/span>=<\/span> thread.<\/span>getMessages<\/span>();<\/span><\/span>\n    messages.<\/span>forEach<\/span>(<\/span>message<\/span> <\/span>=><\/span> {<\/span><\/span>\n      <\/span>const<\/span> date <\/span>=<\/span> message.<\/span>getDate<\/span>();<\/span><\/span>\n      <\/span>const<\/span> from <\/span>=<\/span> message.<\/span>getFrom<\/span>();<\/span><\/span>\n      <\/span>const<\/span> subject <\/span>=<\/span> message.<\/span>getSubject<\/span>();<\/span><\/span>\n      <\/span>const<\/span> body <\/span>=<\/span> message.<\/span>getPlainBody<\/span>();<\/span><\/span>\n<\/span>\n      <\/span>\/\/ Check if the keyword is in the email body, and extract it if present<\/span><\/span>\n      <\/span>let<\/span> keywordExtract <\/span>=<\/span> <\/span>""<\/span>;<\/span><\/span>\n      <\/span>if<\/span> (keyword <\/span>&&<\/span> body.<\/span>includes<\/span>(keyword)) {<\/span><\/span>\n        keywordExtract <\/span>=<\/span> keyword; <\/span>\/\/ Extract the keyword if it's found in the body<\/span><\/span>\n      }<\/span><\/span>\n<\/span>\n      <\/span>\/\/ Write data to the spreadsheet (starting from row 7)<\/span><\/span>\n      sheet.<\/span>getRange<\/span>(row, <\/span>1<\/span>).<\/span>setValue<\/span>(date);<\/span><\/span>\n      sheet.<\/span>getRange<\/span>(row, <\/span>2<\/span>).<\/span>setValue<\/span>(from);<\/span><\/span>\n      sheet.<\/span>getRange<\/span>(row, <\/span>3<\/span>).<\/span>setValue<\/span>(subject);<\/span><\/span>\n      sheet.<\/span>getRange<\/span>(row, <\/span>4<\/span>).<\/span>setValue<\/span>(body);<\/span><\/span>\n      sheet.<\/span>getRange<\/span>(row, <\/span>5<\/span>).<\/span>setValue<\/span>(keywordExtract); <\/span>\/\/ Write the extracted keyword in column E<\/span><\/span>\n<\/span>\n      row<\/span>++<\/span>;<\/span><\/span>\n    });<\/span><\/span>\n  });<\/span><\/span>\n}<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    \n
    Script Explanation<\/span><\/i><\/i><\/span><\/summary>
    \n
    \n
    Retrieve the Spreadsheet Sheet Object<\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    const<\/span> sheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>getActiveSheet<\/span>();<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    The script retrieves the sheet object from the currently active spreadsheet. Subsequent operations will be performed on this sheet.<\/p>\n<\/dd>\n\n\n\n
    Retrieve the Subject to Search<\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    const<\/span> searchPhrase <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>"B1"<\/span>).<\/span>getValue<\/span>();<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    The script retrieves the subject (search phrase) entered in cell “B1” of the spreadsheet. If the cell is empty, the process is terminated by the following validation check.

    <\/p>\n<\/dd>\n\n\n\n
    Retrieve the Start and End Dates<\/strong><\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    const<\/span> startDate <\/span>=<\/span> <\/span>new<\/span> <\/span>Date<\/span>(sheet.<\/span>getRange<\/span>(<\/span>"B3"<\/span>).<\/span>getValue<\/span>());<\/span><\/span>\nconst<\/span> endDate <\/span>=<\/span> <\/span>new<\/span> <\/span>Date<\/span>(sheet.<\/span>getRange<\/span>(<\/span>"B4"<\/span>).<\/span>getValue<\/span>());<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    The script retrieves the start date and end date for email searches from cells “B3” and “B4”, respectively. Emails within this period will be targeted. If either date is invalid, the script will terminate at this point.<\/p>\n<\/dd>\n\n\n\n
    Retrieve the Keyword in the Body<\/strong><\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    const<\/span> keyword <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>"B2"<\/span>).<\/span>getValue<\/span>();<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    The script retrieves the keyword entered in cell “B2” of the spreadsheet and later checks whether this keyword is included in the email body.<\/p>\n<\/dd>\n\n\n\n
    Create the Search Query<\/strong><\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    const<\/span> searchQuery <\/span>=<\/span> <\/span>`subject:"<\/span>${<\/span>searchPhrase<\/span>}<\/span>" after:<\/span>${<\/span>formattedStartDate<\/span>}<\/span> before:<\/span>${<\/span>formattedEndDate<\/span>}<\/span>`<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    Using the retrieved search phrase, start date, and end date, the script creates a Gmail search query. The query is based on the “subject,” “start date,” and “end date” to find emails that meet the specified criteria.<\/p>\n<\/dd>\n\n\n\n
    Search Email Threads<\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    const<\/span> threads <\/span>=<\/span> GmailApp.<\/span>search<\/span>(searchQuery);<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    Using the search query, the script searches Gmail for email threads (collections of emails in a conversational format) that match the specified criteria.<\/p>\n<\/dd>\n\n\n\n
    Retrieve Email Data and Write to the Spreadsheet<\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    threads.<\/span>forEach<\/span>(<\/span>thread<\/span> <\/span>=><\/span> {<\/span><\/span>\n  <\/span>const<\/span> messages <\/span>=<\/span> thread.<\/span>getMessages<\/span>();<\/span><\/span>\n  messages.<\/span>forEach<\/span>(<\/span>message<\/span> <\/span>=><\/span> {<\/span><\/span>\n    <\/span>const<\/span> date <\/span>=<\/span> message.<\/span>getDate<\/span>();<\/span><\/span>\n    <\/span>const<\/span> from <\/span>=<\/span> message.<\/span>getFrom<\/span>();<\/span><\/span>\n    <\/span>const<\/span> subject <\/span>=<\/span> message.<\/span>getSubject<\/span>();<\/span><\/span>\n    <\/span>const<\/span> body <\/span>=<\/span> message.<\/span>getPlainBody<\/span>();<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    The script retrieves all email messages from each email thread and extracts the sent date, sender, subject, and body from each message. It then writes this data to the spreadsheet.<\/p>\n<\/dd>\n\n\n\n
    Keyword Verification and Extraction<\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    let<\/span> keywordExtract <\/span>=<\/span> <\/span>""<\/span>;<\/span><\/span>\nif<\/span> (keyword <\/span>&&<\/span> body.<\/span>includes<\/span>(keyword)) {<\/span><\/span>\n  keywordExtract <\/span>=<\/span> keyword;<\/span><\/span>\n}<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    The script checks whether the keyword is included in the email body. If the keyword is found, it extracts and records the keyword.<\/p>\n<\/dd>\n\n\n\n
    Writing Data to the Spreadsheet<\/strong><\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    sheet.<\/span>getRange<\/span>(row, <\/span>1<\/span>).<\/span>setValue<\/span>(date);<\/span><\/span>\nsheet.<\/span>getRange<\/span>(row, <\/span>2<\/span>).<\/span>setValue<\/span>(from);<\/span><\/span>\nsheet.<\/span>getRange<\/span>(row, <\/span>3<\/span>).<\/span>setValue<\/span>(subject);<\/span><\/span>\nsheet.<\/span>getRange<\/span>(row, <\/span>4<\/span>).<\/span>setValue<\/span>(body);<\/span><\/span>\nsheet.<\/span>getRange<\/span>(row, <\/span>5<\/span>).<\/span>setValue<\/span>(keywordExtract);<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    The retrieved data is written row by row into the spreadsheet. The columns are populated sequentially with the “Date,” “Email Address,” “Subject,” “Body,” and “Keyword Extraction Result.”<\/p>\n<\/dd>\n\n\n\n
    Prepare to Enter Data in the Next Row<\/strong><\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
    row<\/span>++<\/span>;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
    The script increments the row number after writing data to ensure the next email’s data is written to the subsequent row.

    <\/p>\n<\/dd>\n<\/dl>\n<\/div><\/details>\n<\/div>\n\n\n\n
    This script transfers emails that match specific conditions in Gmail to a Google Sheet.<\/p>\n\n\n\n
    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.<\/p>\n\n\n\n
    \n
    STEP<\/span><\/div>
    Open Apps Script Editor<\/div>
    \n
    \"\"<\/figure>\n<\/div>\n\n\n
    Open a Google Spreadsheet and click on \u201cExtensions<\/strong>\u201d in the menu, then \u201cApps Script<\/strong>\u201d to open the Apps Script editor.<\/p>\n<\/div><\/div>\n\n\n\n
    STEP<\/span><\/div>
    Create the Script<\/strong><\/div>
    \n
    Copy the code above and paste it into the editor.<\/p>\n\n\n
    \n
    \"\"<\/figure>\n<\/div><\/div><\/div>\n\n\n\n
    STEP<\/span><\/div>
    Save the Script<\/strong><\/div>
    \n
    \"\"<\/figure>\n\n\n\n
    After writing the script, name and save it.<\/p>\n\n\n\n
    (e.g., \u201cFetch Emails\u201d)<\/p>\n<\/div><\/div>\n\n\n\n
    STEP<\/span><\/div>
    Run the Script<\/strong><\/div>
    \n
    \"\"<\/figure>\n<\/div>\n\n\n
    Run the script to fetch emails that match the specified conditions.<\/p>\n<\/div><\/div>\n<\/div>\n\n\n\n
    \n
    If you’re running the script for the first time, you need to authorize it.<\/p>\n\n\n\n
    Therefore, press “Review Permissions.<\/strong>“<\/strong><\/p>\n\n\n
    \n
    \"\"<\/figure>\n<\/div>\n\n\n
    \n
    Detailed Authorization Steps<\/span><\/i><\/i><\/span><\/summary>
    \n
    Press “Advanced.”<\/strong><\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Press “Go to Untitled project (Unsafe).”<\/strong><\/p>\n\n\n
    \n
    \"\"<\/figure>\n<\/div>\n\n\n
    After that, press “Allow.”<\/strong><\/p>\n\n\n
    \n
    \"\"<\/figure>\n<\/div>\n\n\n
    <\/p>\n<\/div><\/details>\n<\/div>\n<\/div><\/div>\n\n\n\n
    Gmail Fetch Results<\/span><\/strong><\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    When the script is executed, the email data matching the specified conditions will be written to the spreadsheet starting from row 7.<\/p>\n\n\n\n
    Benefits of Using Google Apps Script<\/strong><\/h2>\n\n\n\n
    Key Advantages<\/strong><\/span><\/div>
    \n