How to send emails to multiple addresses at once from Google Sheets
Tags: googlecloud, tutorial, typescript, webdev
Published: 2022-02-27 04:00:23

I recommend you to check out this article if you are looking for a tool to send emails to multiple addresses. 

I want to share how to make a tool to send a lot of emails at once with Google Sheets and Google Apps Script in this article.

## Create a new spreadsheet

Prepare a new spreadsheet at first and create two sheets like below.

- Email List
- Email Content

![Sheet name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lctqefnnyazotjn27iqj.png)

### Email List Sheet

Write down a title and descriptions in the first row, and then fill the items' names in the second row.

![Email List Sheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xb1wqxhwjagt7i2hdhji.png)

| No. |   Company   | Department | Person in charge |       Email address |
| --- | :---------: | ---------: | ---------------: | ------------------: |
| 1   |  Test Inc.  |         QA |        Test Name |  `example@test.com` |
| 2   | Smaple Inc. |  Marketing |      Sample Name | `example@test2.com` |

Click on Insert > Drawing from within Google Sheets.
Make a send button as you like and assign a `main` script.

[📖 Add A Google Sheets Button To Run Scripts](https://www.benlcollins.com/apps-script/google-sheets-button/)

### Email Content Sheet

Create this table and fill the content of the email you want to send.
If you want to specify multiple CCs and BCCs, connect them with commas.

![Email Content Sheet](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ytar2a2g5dk05zydm8r5.png)

You can also use these variables to change each content as send emails.

```
Company: {COMPANY}
Department: {DEPARTMENT}
Person in charge: {PIC}
```

| Item               |      Description      |
| ------------------ | :-------------------: |
| Test email address |  `example@test.com`   |
| CC                 | `example@testcc.com`  |
| BCC                | `example@testbcc.com` |
| Body               |     Email content     |

Click on Insert > Drawing from within Google Sheets.
Make a test email button as you like and assign a `testEmail` script.

## Prepare Apps Script

Open Apps Script from within Google Sheets.

[📖 Extending Google Sheets](https://developers.google.com/apps-script/guides/sheets#get_started)

Use this template if you would like to customize Google Apps Script.

[🗂 gas-spreadsheet-mail](https://github.com/Kazuki-tam/gas-spreadsheet-mail)

Otherwise create a `sendEmail.gs`, and then copy and paste this code.

```javascript
function main(){}(()=>{"use strict";var e,t,a,r={708:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.getUserName=void 0,t.getUserName=function(){var e=Session.getActiveUser(),t=ContactsApp.getContact(e.toString());return{fullName:t.getFullName(),familyName:t.getFamilyName(),givenName:t.getGivenName()}}},598:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.sendEmailToAll=void 0;var r=a(708);t.sendEmailToAll=function(e,t,a,n){var i=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),l=SpreadsheetApp.getUi(),o=(0,r.getUserName)(),s=o.fullName,c=o.familyName,u=o.givenName;if(i){for(var d="",p="",m="",g="",f=i.getLastRow()-1,v=0;v<f;v++){var h=i.getRange(2+v,1).getValue(),A=i.getRange(2+v,2).getValue();"CC"===h&&(m=A),"BCC"===h&&(g=A),"Subject"===h&&(d=A.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",n)),"Body"===h&&(p=A.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",n).replace("{MY_FULL_NAME}",s).replace("{MY_FAMILY_NAME}",c).replace("{MY_LAST_NAME}",u))}var E={cc:m,bcc:g};GmailApp.sendEmail(e,d,p,E)}else l.alert("🚨 The sheet name may be incorrect. It should be Email Content.")}},690:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.checkDuplicate=void 0,t.checkDuplicate=function(e){return e.filter((function(e,t,a){return a.indexOf(e)===t&&t!==a.lastIndexOf(e)}))}},506:(e,t,a)=>{Object.defineProperty(t,"__esModule",{value:!0}),t.validateEmail=void 0,t.validateEmail=function(e){return/^[A-Za-z0-9]{1}[A-Za-z0-9_.-]*@{1}[A-Za-z0-9_.-]+.[A-Za-z0-9]+$/.test(e)}}},n={};function i(e){var t=n[e];if(void 0!==t)return t.exports;var a=n[e]={exports:{}};return r[e](a,a.exports,i),a.exports}i.g=function(){if("object"==typeof globalThis)return globalThis;try{return this||new Function("return this")()}catch(e){if("object"==typeof window)return window}}(),e=i(598),t=i(506),a=i(690),i.g.main=function(){var r=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email List"),n=SpreadsheetApp.getUi(),i=Browser.msgBox("Confirmation","Are you sure to send email？👀",Browser.Buttons.YES_NO);if(r&&"yes"==i){for(var l=2,o=3,s=4,c=5,u=r.getLastColumn(),d=1;d<=u;d++){var p=r.getRange(2,d).getValue();"Company"===p&&(l=d),"Department"===p&&(o=d),"Person in charge"===p&&(s=d),"Email address"===p&&(c=d)}var m=r.getLastRow()-2,g=[],f=[],v=[];for(d=0;d<m;d++){var h=r.getRange(3+d,l).getValue(),A=r.getRange(3+d,o).getValue(),E=r.getRange(3+d,s).getValue(),N=r.getRange(3+d,c).getValue(),y={company:h,department:A,pic:E,address:N};(0,t.validateEmail)(N)?(g.push(y),f.push(N)):""===N||(0,t.validateEmail)(N)||v.push(N)}if(v.length>0)return n.alert("🚨 Invalid email address: ".concat(v.join(", ")));var _=(0,a.checkDuplicate)(f);if(_.length>0)return n.alert("🚨 Duplicate email address: ".concat(_.join(", ")));g.forEach((function(t){""!=t.address&&(0,e.sendEmailToAll)(t.address,t.company,t.department,t.pic)})),n.alert("📤 Sent email","It's done!",n.ButtonSet.OK)}else"no"==i?n.alert("Send canceled!"):n.alert("🚨 The sheet name may be incorrect. It should be Email List.")}})();
```

[🗂 sendEmail.gs](https://gist.github.com/Kazuki-tam/b7d6fd8be32af0a581bb7a989248728e)

Create one more script file named `testEmail.gs` in Apps Script.

```javascript
function testEmail(){}(()=>{"use strict";var e,t,a={598:(e,t)=>{t.__esModule=!0,t.sendEmailToAll=void 0,t.sendEmailToAll=function(e,t,a,r){var i=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),n=SpreadsheetApp.getUi();if(i){for(var l="",o="",s="",d="",c=i.getLastRow()-1,p=0;p<c;p++){var g=i.getRange(2+p,1).getValue(),u=i.getRange(2+p,2).getValue();"CC"===g&&(s=u),"BCC"===g&&(d=u),"Subject"===g&&(l=u),"Body"===g&&(o=u.replace("{COMPANY}",t).replace("{DEPARTMENT}",a).replace("{PIC}",r))}var m={cc:s,bcc:d};GmailApp.sendEmail(e,l,o,m),n.alert("📤 Sent email","It's done!",n.ButtonSet.OK)}else n.alert("🚨 The sheet name may be incorrect. It should be Email Content.")}},506:(e,t)=>{t.__esModule=!0,t.validateEmail=void 0,t.validateEmail=function(e){return/^[A-Za-z0-9]{1}[A-Za-z0-9_.-]*@{1}[A-Za-z0-9_.-]+.[A-Za-z0-9]+$/.test(e)}}},r={};function i(e){var t=r[e];if(void 0!==t)return t.exports;var n=r[e]={exports:{}};return a[e](n,n.exports,i),n.exports}i.g=function(){if("object"==typeof globalThis)return globalThis;try{return this||new Function("return this")()}catch(e){if("object"==typeof window)return window}}(),e=i(598),t=i(506),i.g.testEmail=function(){var a=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Content"),r=SpreadsheetApp.getUi();if(a){for(var i="",n=a.getLastRow()-1,l=0;l<n;l++){var o=a.getRange(2+l,1).getValue(),s=a.getRange(2+l,2).getValue();"Test email address"===o&&""!==s&&(i=s)}(0,t.validateEmail)(i)?(0,e.sendEmailToAll)(i,"Test Inc.","QA","Test Name"):r.alert("🚨 Invalid email address!")}}})();
```

[🗂 testEmail.gs](https://gist.github.com/Kazuki-tam/63be03b8bae8ede2117e847314dbbace)

## Test Apps Script

That's all to set up the script. Let's send emails to confirm it is not a problem with this tool.

## Notes

You need to pay attention to Gmail sending limits when you use Google Apps Script to send emails.  
https://support.google.com/a/answer/166852?hl=en