Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click

Send Emails and Edit permission from Google Sheet Uniques list with Google Apps Script

Google Sheets, Google Apps Script, GmailApp

Ever wanted a quick way to give edit permissions to a user and then send them an email based on values in your Google Sheet?

This task crosses my table several times a week. Whether it is sending reminders to complete a Google Sheets task or share a Google Sheet to complete a task.

You might be thinking that it’s pretty easy to just go up to the big green Share button on Google Sheets and plug in all the people you need to give edit permission too. But what if you need to give edit permission to a number of people that are on your Google Sheet. Ever noticed that the Share button gets in the way when you are looking through your Sheet for those names or emails?

When you normally share a Google Sheet you have the option to send a message, but what if you want to send a custom message with the user’s name? You are kinda stuck to a brief message which sucks.

So I decided that it would be best to create a template script that I could quickly adapt to suit my need in different circumstances.  Below is the result.

Basically, this little piece of Google Apps Script allows you  to plug in a custom:

  1. Email column.
  2. Columns you want to use to create a unique email for each person you want to send your message to.
  3. Email sent checkbox location
  4. An Image (I use a button) that the user can click on to send the email and give edit permission to the Spreadsheet.

Google Sheet necessary items for GAS email and share template

The script will then get all the emails and their associated columns you want to use to personalize your email. Remove the duplicates and then give permission to edit the current Google Spreadsheet and send them an email.

If you are in a hurry to copy and paste the code into your own project the script is below with a short Quick Start Guide.

If you are looking to learn how it all works, I will go into the details after the Quick Start Guide.

The Code

Main Script: Code.gs

Email Script: Email.gs

Quick Use Guide

For ease of quick referencing, I have put the code into two Google Apps Script files (Code.gs and Email.gs). This should allow you to quickly add your unique data at the top of the Code.gs file and then modify your email message in Email.gs.

The script comes with an example in-place to help you see what you need to add.

In your Google Spreadsheet you will need a minimum of:

  1. A column with email addresses.
  2. A column with a checkbox to mark that the email has been sent.
  3. A button image or plain image to click and run the script

You can use the script to send an email with a heap of custom values based on user information in selected columns or you can send a standard email. We’ll go over the steps for both, but before we do, let’s take a look at the custom values you will need to enter into the Global Variables.

Global Variables

You can go ahead and modify these global variables for your own project.

  1. SS_ID: string: This is the unique id for your Spreadsheet that you can find in your spreadsheet’s URL.
  2. SHEET_NAME: string: The name of the spreadsheet you will be referencing.
  3. EMAIL_COL: number:  The column where your emails can be found.
  4. COMPLETE_COL: number: The column where your checkboxes are to indicate the email has been sent.
  5. PERS_COLS: array: An array of column numbers (These don’t have to be in order) what you want to use if you are making a custom or personalized emails. If you don’t wish to personalize the email message delete the array (e.g. [1,2]) and replace it with null.
  6. START_ROW: number: The first row after your header rows.
  7. MESSAGE_HEADER: string: The subject you wish to put into your email message.

Before we move on to looking at the email data it’s important to note that the EMAIL_COL will be added to the front of the PERS_COL. So the PERs_COL array will contain:

Editing the Email Message

For the purpose of this tutorial, we will use simple joined text strings for the message of our text.

The email message is contained in a separate Google Script file named email.gs. This file contains the function, getGmailMessage(pers_data, link_data).  The function takes two parameters, your pers_data which contains a string starting with the email, then any other column you have selected with personal data on it. It also contains the link_data which is a list containing the spreadsheet name and the spreadsheet URL.

You don’t need to touch the function parameters, but you do need to know what is in them if you are going to reference them in your email.

The function contains the message variable, where you will add the message of your email.

You can go ahead and make changes to the message to create your own message.

Check out this primer for the non-coder on working with and joining strings:

Google Apps Script: Basic Beginners Guide to Using Strings in Code

Creating A Button

Just like in the example you can create a button to run the code. To learn how to create and deploy a button in Google Apps Script check out this tutorial I created below:

Google Apps Script: How to Connect a Button to a Function in Google Sheets

When you right-click your button image and assign script, add the sendEmail function and you will be good to go.

When the button is clicked the code will run and give edit permission to the Google Sheet and send out your crafted email. It will then check all the rows that have had emails sent.

Upon completion, the script will display a popup of any emails that could not be sent so you know if you have any bad emails.

Google Apps Script Send bulk emails and edit permission

Obviously, all the emails are not real – I don’t know any of the directors of Altered Carbon – so the pop-up will show that all emails could not be sent. However, if all your emails are good you will not see any emails in the final pop-up.

That’s about all you need to know to use this code on your own if you have any questions or need more clarification, let me know in the comments at the bottom.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

For those of you still sticking around, 1 – luv yas, 2 – let’s take a look at the rest of the code:

Code Breakdown

sendEmail()

The function sendEmail is the main function of the code.

Lines 4 to 17 set the variables for the script, grabbing the selected spreadsheet and sheet. Lines 6-8 store the link data just in case you want to add it to your email message. Then we store the last row which will come in handy later to get our selected range. Finally, we will add an empty uniqueRange variable that we will fill later.

Next, we run our code based on two conditions. If you have decided to personalise your email and have added columns you want to use in your email then you will use the first if statement:

On line 20, we test if there is data in PERS_COL. If there is it is deemed truthy and we proceed. We add the EMAIL_COL to the start of PERS_COL and then add the COMPLETE_COL (the checkbox column) to the end.

Next, we iterate through each column to create a R1C1 notation (lines 24-25) and store it in columnRanges array. That array will then be used to getRangeList in our rangeOfColumns variable. (Line 31). We then iterate through the range of columns getting the values for each column and storing it in a 3d array.

Line 55, grabs the row of the last email using our getLastDataCol() function.

Finally, we use the getUnique function to remove any duplicate emails and return a 2d array of emails and personalise values (Line 41).

Alternatively, if you chose not to personalize your data and selected…

…you will go to the else statement. This one is much easier. All it needs to do is get the emailRange values and the range of the complete_col and store it in emailRangeValues (Lines 46-52).

Again, we store the email row value in the lastDataCol before running getUnique to find all the unique emails.

Once we have our uniqueEmails based on either of our conditions, we then go ahead and attempt to give the users edit permissions and send our email via the permissionsNemail function.  This function takes 3 parameters:

  1. The uniqueEmail 2d array
  2. The ssLinkData, an array containing the Google Sheet name and URL
  3. The ss or Google Sheet spreadsheet Class Object

The function then returns a list of any emails that could not be sent or given edit permission in the badEmails variable (Line 65).

We then need to update the Sheet checkbox column to mark as checked (Lines 67-68).

Our final task is to send a user interface (ui) alert to say we are done and list any emails that were not sent.

getLastCol(emailRows)

This short function iterates through the email column and counts each row until it gets to an empty cell. The count variable starts at the beginning of the START_ROW just in case there are any empty spaces above it that could bread the loop prematurely.

getUnique(rng)

The getUnique function essentially takes the 3d array and reduces it to a 2d array containing only the values for each original email.

I have written a full tutorial on this function because I found it to be a little mind bending. You can dive into it here:

Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column

permissionsNemail()

The permissionsNemail function loops through the email lists and uses try and catch to attempt to send an email to the user and give them edit permission for the spreadsheet.

It makes use of the GmailApp Google Apps Script class to send an email with the sendEmail method. The sendEmail method takes multiple parameters, but for our script it takes:

For our message, it calls the getGmailMessage() which contains our message in the Email.gs file.

If there is a faulty email, then that email is caught an stored in the badEmails array and returned back to the sendEmail function.

markCheckBoxIfSent()

This function grabs the last data column and the selected sheet Google Apps Script class.

It then gets the range of all the checkbox values from the START_ROW to the last_row and checks them as completed.

 

Conclusion

Wow! If you made it this far, you’re a legend. I wasn’t sure if I was going to make it.

I hope you find this script useful to email and share edit permissions for Google Sheets. I would love to hear what you are using it on.

 

5 thoughts on “Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click”

  1. Hello,

    thank you for your tutorial 🙂

    I have a problem that I can’t solve – every time that I click in the button I receive this message ‘TypeError: Unable to call “getLastRow” method from null’ – do you can help me to solve this?

    1. Hi Fabio,
      Can you share a sample of your code in the example below? It’s difficult for me to understand the problem without it.

  2. Hey! this is great. Im using it for a very similar case that I need, i removed some sections that i did not need (like checking for duplicate rows and giving edit permission and sending the file in the mail)

    But im having some troubles with 2 things

    1) I want the checkboxes to be marked ONLY for the cases in which the mail was correctly sent. As it is today even though you get the warning message, the checkbox will be marked

    2) I want the warning to be better. I want the “following mails were not sent” only to appear when there are errors”

    Could you please help me with this? my knowledge to write these down is quite limited to be honest.

  3. Hi! this tutorial is great, im using it and modifying some things to make it more suitable for what i need

    But as my coding skills are limited im struggling when i need something more complex than removing a part of the code.

    I need to do one thing, can you help me please?

    2) as the code is it right now, it marks the complete_col checkbox with all the range of emails considered, even if the emails were sent or not. I need that checkbox marked on the condition of the email was sent or not. I assumed i need to do some if and compare it to the list of “badEmails” but im clueless about how to actually do it

Leave a Reply