Mail merge trên google sheets
Show
Coding level: Beginner Objectives
About this solutionAutomatically populate an email template with data from Google Sheets. The emails are sent from your Gmail account so that you can respond to recipient replies. Important: This mail merge sample is subject to the email limits described in Quotas for Google services.
How it worksYou create a Gmail draft template with placeholders that correspond to data in a Sheets spreadsheet. Each column header in a sheet represents a placeholder tag. The script sends the information for each placeholder from the spreadsheet to the location of the corresponding placeholder tag in your email draft. Apps Script servicesThis solution uses the following services:
PrerequisitesTo use this sample, you need the following prerequisites:
Set up the scriptCreate the Apps Script project
If you change the name of the Recipient or Email Sent columns, you must update the corresponding code in the Apps Script project. You can open the Apps Script project from the spreadsheet by clicking Extensions > Apps Script. Create an email template
Run the script
If you applied a filter to the sheet, the script still emails the filtered participants, but it won't add the timestamp. Review the codeTo review the Apps Script code for this solution, click View source code below: View source codeCode.gsModificationsYou can edit the mail merge automation as much as you'd like to fit your needs. Below are a few optional changes you can make to the source code. Add Bcc, Cc, ReplyTo, or From email parametersThe sample code includes a number of additional parameters, currently commented out, that let you control the name of the account the email is sent from, reply to email addresses, as well as Bcc and Cc email addresses. Activate the parameters you want to add by removing the forward slashes The following sample shows an excerpt from the GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, { htmlBody: msgObj.html, bcc: '', cc: '', from: '', name: 'name of the sender', replyTo: '', // noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users) In the above sample, the Include unicode characters in your emailsIf you want to include unicode characters, such as emojis, in your emails, you must update the code to use Mail service instead of Gmail service. In the sample code, update the following line: GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, { Replace the line with the following code: MailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, { ContributorsThe sample was created by Martin Hawksey, Learning Design and Technology Lead at Edinburgh Futures Institute, blogger, and Google Developer Expert.
This sample is maintained by Google with the help of Google Developer Experts. Next steps
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates. Last updated 2022-12-29 UTC. [{ "type": "thumb-down", "id": "missingTheInformationINeed", "label":"Missing the information I need" },{ "type": "thumb-down", "id": "tooComplicatedTooManySteps", "label":"Too complicated / too many steps" },{ "type": "thumb-down", "id": "outOfDate", "label":"Out of date" },{ "type": "thumb-down", "id": "samplesCodeIssue", "label":"Samples / code issue" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }] [{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }] |