{"id":15962,"date":"2024-11-15T01:42:34","date_gmt":"2024-11-14T16:42:34","guid":{"rendered":"https:\/\/84office.jp\/?p=15962"},"modified":"2024-11-15T02:11:42","modified_gmt":"2024-11-14T17:11:42","slug":"gas%ef%bd%b0send-email","status":"publish","type":"post","link":"https:\/\/84office.jp\/en\/gas%ef%bd%b0send-email\/","title":{"rendered":"Boost Productivity: Automate Gmail to Multiple Recipients with Google Sheets and Apps Script"},"content":{"rendered":"
<\/div>\n
<\/path><\/path><\/svg>Recommended for<\/span><\/div>
\n
    \n
  • For those who find sending Gmail each time to be cumbersome<\/li>\n\n\n\n
  • For those who want to send emails to multiple recipients by only changing part of the message<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n

    When sending emails to customers or contacts managed in a spreadsheet, do you find yourself sending similar content to each one individually?<\/p>\n\n\n\n

    For example, if you need to send emails to multiple recipients by only changing the meeting schedule in the email body, doing it manually can lead to mistakes and take a lot of time.<\/p>\n\n\n\n

    In such cases, it would be convenient to have a method to automatically send emails using Google Sheets.<\/p>\n\n\n\n

    In this article, we will introduce how to automate email sending using Google Sheets and Google Apps Script.<\/p>\n\n\n\n

    This allows you to focus on other important tasks without spending time on sending emails every time.<\/p>\n\n\n\n

    Completed example<\/h2>\n\n\n\n
    \"\"<\/figure>\n\n\n\n

    Gmail Content<\/span><\/strong><\/p>\n\n\n

    \n
    \"\"<\/figure>\n<\/div>\n\n\n

    Here is a completed example of how to automatically send emails using the spreadsheet and Google Apps Script we will create this time.<\/p>\n\n\n\n

    Based on the information for each recipient entered in the Google Sheet, a portion of the email body is automatically customized, and then the email is sent via Gmail.<\/p>\n\n\n\n

    In this example, the changes to be inserted into the email body include the name<\/strong>, meeting date<\/strong>, and meeting time<\/strong>.<\/p>\n\n\n\n

    A pre-prepared email draft is duplicated, and these pieces of information are automatically replaced, allowing for personalized emails to be sent.<\/p>\n\n\n\n

    Google Sheet Input Fields<\/strong><\/p>\n\n\n\n

    Column A (First Name)<\/strong><\/td>Enter the recipient’s first name. This name will be inserted into the email body sent via Gmail.<\/td><\/tr>
    Column B (Last Name)<\/strong><\/strong><\/td>Enter the last name. In this example, it will not appear in the email body.<\/td><\/tr>
    Column C (Email Address)<\/strong><\/td>Enter the recipient’s email address.<\/td><\/tr>
    Column D (Meeting Date)<\/strong><\/td>Enter the meeting date.<\/td><\/tr>
    Column E (Meeting Time)<\/strong><\/td>Enter the meeting time.<\/td><\/tr>
    Column F (Check)<\/strong><\/td>Check the box for individuals who are the target of the email.
    This column is used to select whether to send the email or not.<\/td><\/tr>
    Column G (Sent Time)<\/strong><\/td>The timestamp of when the email was sent is automatically recorded.
    This allows you to verify whether the email was sent.<\/td><\/tr>
    Sheet Name<\/strong><\/td>As an example, the name “Email Sending List<\/strong>” is used.
    This sheet name will be used when executing the Google Apps Script.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

    How to Use the Check Box in Column F<\/strong><\/p>\n\n\n\n

    By using the checkboxes in Column F, you can easily select the recipients to whom the emails will be sent.<\/p>\n\n\n\n

    For example, if you want to send emails to Mr. Smith and Mr. Johnson but want to send one to Mr. Brown later, you would uncheck Mr. Brown.<\/p>\n\n\n\n

    Emails are sent only for the rows where the checkbox is checked, and the send time is recorded in Column G after the email is sent.<\/p>\n\n\n\n

    Google Apps Script Settings<\/h2>\n\n\n\n
    <\/path><\/path><\/svg><\/span>
    function<\/span> <\/span>sendEmails<\/span>() {<\/span><\/span>\n  <\/span>var<\/span> sheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>getSheetByName<\/span>(<\/span>'Email Sending List'<\/span>);  <\/span>\/\/ Specify the tab name "Email Sending List"<\/span><\/span>\n  <\/span>var<\/span> range <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>"A2:G"<\/span>);  <\/span>\/\/ Get the data range from A2 to column G<\/span><\/span>\n  <\/span>var<\/span> data <\/span>=<\/span> range.<\/span>getDisplayValues<\/span>();  <\/span>\/\/ Get the displayed data within the range<\/span><\/span>\n  <\/span>var<\/span> draftId <\/span>=<\/span> <\/span>getDraftIdBySubject<\/span>(<\/span>'Regarding Meeting Schedule'<\/span>);  <\/span>\/\/ Email draft subject<\/span><\/span>\n  <\/span><\/span>\n  <\/span>if<\/span> (<\/span>!<\/span>draftId) {<\/span><\/span>\n    Logger.<\/span>log<\/span>(<\/span>"Error: Draft not found."<\/span>);<\/span><\/span>\n    <\/span>return<\/span>;  <\/span>\/\/ Exit if the draft is not found<\/span><\/span>\n  }<\/span><\/span>\n<\/span>\n  data.<\/span>forEach<\/span>(<\/span>function<\/span>(<\/span>row<\/span>, <\/span>index<\/span>) {<\/span><\/span>\n    <\/span>var<\/span> sentTime <\/span>=<\/span> row[<\/span>6<\/span>];  <\/span>\/\/ Column G: Sent Time<\/span><\/span>\n    <\/span>if<\/span> (row[<\/span>5<\/span>] <\/span>===<\/span> <\/span>'TRUE'<\/span> <\/span>&&<\/span> <\/span>!<\/span>sentTime) {  <\/span>\/\/ Check if the checkbox in column F is checked<\/span><\/span>\n      <\/span>var<\/span> recipient <\/span>=<\/span> row[<\/span>2<\/span>];  <\/span>\/\/ Column C: Email Address<\/span><\/span>\n      <\/span>var<\/span> subject <\/span>=<\/span> <\/span>'Regarding Meeting Schedule'<\/span>;  <\/span>\/\/ Subject of the email to be sent<\/span><\/span>\n      <\/span>var<\/span> body <\/span>=<\/span> <\/span>getDraftMessageBody<\/span>(draftId);  <\/span>\/\/ Get the draft message body<\/span><\/span>\n      <\/span>var<\/span> attachments <\/span>=<\/span> <\/span>getDraftAttachments<\/span>(draftId);  <\/span>\/\/ Get the draft attachments<\/span><\/span>\n<\/span>\n      <\/span>\/\/ Replace placeholders with data from the spreadsheet<\/span><\/span>\n      body <\/span>=<\/span> body.<\/span>replace<\/span>(<\/span>\/{First Name}\/<\/span>g<\/span>, row[<\/span>0<\/span>])       <\/span>\/\/ Column A: First Name<\/span><\/span>\n                 .<\/span>replace<\/span>(<\/span>\/{Meeting Date}\/<\/span>g<\/span>, row[<\/span>3<\/span>])     <\/span>\/\/ Column D: Meeting Date<\/span><\/span>\n                 .<\/span>replace<\/span>(<\/span>\/{Meeting Time}\/<\/span>g<\/span>, row[<\/span>4<\/span>]);    <\/span>\/\/ Column E: Meeting Time<\/span><\/span>\n      <\/span><\/span>\n      <\/span>\/\/ Send email using GmailApp.sendEmail (with HTML body)<\/span><\/span>\n      GmailApp.<\/span>sendEmail<\/span>(recipient, subject, <\/span>''<\/span>, {<\/span><\/span>\n        htmlBody: body,<\/span><\/span>\n        attachments: attachments  <\/span>\/\/ Add attachments to the email<\/span><\/span>\n      });<\/span><\/span>\n<\/span>\n      <\/span>\/\/ Record the sent time in Column G<\/span><\/span>\n      <\/span>var<\/span> userTimezone <\/span>=<\/span> Session.<\/span>getScriptTimeZone<\/span>();  <\/span>\/\/ Get the user's script timezone<\/span><\/span>\n      <\/span>var<\/span> localTime <\/span>=<\/span> Utilities.<\/span>formatDate<\/span>(<\/span>new<\/span> <\/span>Date<\/span>(), userTimezone, <\/span>'MM\/dd\/yyyy HH:mm:ss'<\/span>);<\/span><\/span>\n      sheet.<\/span>getRange<\/span>(index <\/span>+<\/span> <\/span>2<\/span>, <\/span>7<\/span>).<\/span>setValue<\/span>(localTime);  <\/span>\/\/ Record the sent time in Column G<\/span><\/span>\n    }<\/span><\/span>\n  });<\/span><\/span>\n}<\/span><\/span>\n<\/span>\n\/\/ Function to get draft attachments<\/span><\/span>\nfunction<\/span> <\/span>getDraftAttachments<\/span>(<\/span>draftId<\/span>) {<\/span><\/span>\n  <\/span>var<\/span> draft <\/span>=<\/span> GmailApp.<\/span>getDraft<\/span>(draftId);<\/span><\/span>\n  <\/span>var<\/span> message <\/span>=<\/span> draft.<\/span>getMessage<\/span>();<\/span><\/span>\n  <\/span>return<\/span> message.<\/span>getAttachments<\/span>();  <\/span>\/\/ Get attachments<\/span><\/span>\n}<\/span><\/span>\n<\/span>\n\/\/ Function to get draft ID by specified subject<\/span><\/span>\nfunction<\/span> <\/span>getDraftIdBySubject<\/span>(<\/span>subject<\/span>) {<\/span><\/span>\n  <\/span>var<\/span> drafts <\/span>=<\/span> GmailApp.<\/span>getDrafts<\/span>();<\/span><\/span>\n  <\/span>for<\/span> (<\/span>var<\/span> i <\/span>=<\/span> <\/span>0<\/span>; i <\/span><<\/span> drafts.length; i<\/span>++<\/span>) {<\/span><\/span>\n    <\/span>if<\/span> (drafts[i].<\/span>getMessage<\/span>().<\/span>getSubject<\/span>() <\/span>===<\/span> subject) {<\/span><\/span>\n      <\/span>return<\/span> drafts[i].<\/span>getId<\/span>();<\/span><\/span>\n    }<\/span><\/span>\n  }<\/span><\/span>\n  Logger.<\/span>log<\/span>(<\/span>"Error: No draft found with subject '"<\/span> <\/span>+<\/span> subject <\/span>+<\/span> <\/span>"'."<\/span>);<\/span><\/span>\n  <\/span>return<\/span> <\/span>null<\/span>;  <\/span>\/\/ Return null if no draft is found<\/span><\/span>\n}<\/span><\/span>\n<\/span>\n\/\/ Function to get the draft message body<\/span><\/span>\nfunction<\/span> <\/span>getDraftMessageBody<\/span>(<\/span>draftId<\/span>) {<\/span><\/span>\n  <\/span>var<\/span> draft <\/span>=<\/span> GmailApp.<\/span>getDraft<\/span>(draftId);<\/span><\/span>\n  <\/span>var<\/span> message <\/span>=<\/span> draft.<\/span>getMessage<\/span>();<\/span><\/span>\n  <\/span>return<\/span> message.<\/span>getBody<\/span>();  <\/span>\/\/ Get the draft HTML body<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
    \n
    \u30b9\u30af\u30ea\u30d7\u30c8\u306e\u8aac\u660e<\/span><\/i><\/i><\/span><\/summary>
    \n
    \n
    Main Process\uff1asendEmails()<\/code><\/dt>\n\n\n\n
    \n
    sendEmails()<\/code> is the main function that retrieves data from the Google Sheet and automatically sends emails corresponding to each row.<\/p>\n<\/dd>\n\n\n\n
    Retrieving Spreadsheet Data<\/dt>\n\n\n\n
    \n
    <\/path><\/path><\/svg><\/span>
      <\/span>var<\/span> sheet <\/span>=<\/span> SpreadsheetApp.<\/span>getActiveSpreadsheet<\/span>().<\/span>getSheetByName<\/span>(<\/span>'Email Sending List'<\/span>);  <\/span>\/\/ Specify the tab name "Email Sending List"<\/span><\/span>\n  <\/span>var<\/span> range <\/span>=<\/span> sheet.<\/span>getRange<\/span>(<\/span>"A2:G"<\/span>);  <\/span>\/\/ Get the data range from A2 to column G<\/span><\/span>\n  <\/span>var<\/span> data <\/span>=<\/span> range.<\/span>getDisplayValues<\/span>();  <\/span>\/\/ Get the displayed data within the range<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n