Script: send emails via Google Sheets

Here's a new tip for Google Sheets, allowing you to quickly send predefined emails from a spreadsheet. This little feature will save you time and productivity, since it will save you from having to type your emails manually, especially when they are recurring and boring emails linked to your managerial or operational activity.

Personally, I use it to inform my colleagues that the schedule for the week or month is ready. Planning that I share with them in a fragmented way, as I explain in this tutorial. Once the schedule is finished, I invite my partners to consult it and confirm their presence directly from Google Sheets, without having to multiply the tasks and means of information.

Here is the relatively simple procedure to follow:

Create your newsletter

Open a new Google Sheets document, then enter the email addresses of your future recipients in the first column. Then, in front of each recipient, type the message you would like to send to each:

Create and run the email sending script

Click “Tools > Script Editor” and copy and paste the following script:

function sendEmails() { var sheet = SpreadsheetApp.getActiveSheet(); var startRow = 2; // Type the first row to process var numRows = 2; // Type here the total number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, 2) // Fetch values for each row in the Range. var data = dataRange.getValues(); for (i in data) { var row = data[i]; var emailAddress = row[0]; // 0 if the emails are in the first column var message = row[1]; // 1 if the message is in the second column var subject = "Subject of your email"; // Type the subject of the email you are going to send MailApp.sendEmail(emailAddress, subject, message); } }

Save the script. When you need to notify the recipients of your email, all you have to do is click on “Script Editor”, then select “SendEmails” from the list of functions (especially if you have other functions in your editor ), then click on the “run” button (shaped like a play button).

Google will ask you to authorize the function, then the emails will be sent to the recipients set up in your table.

In the case of a schedule: be notified of confirmation of recipients

If, like me, you want to use this script to send a schedule to your colleagues, you can also invite them to type “OK” in their schedule so that they confirm it. And for you to be notified of their confirmation, you must go to each person's schedules, and click on “Tools > Notification rules”.

I advise you to check the box “be informed of changes that are made”, as well as “E-mail (daily summary)”, in order to have an email encompassing several confirmations and not an email each time someone confirms the planning.

Source : Google Developers.