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:

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

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

/******************************************************************
 * Give Edit Permissions and Send Email to selected users on Spreadsheet
 *
 * sendEmail() is initialized from a button. The script:
 * 1. Finds all the emails and removed duplicates
 * 2. Select only those users who have not been checks as having an email sent.
 * 3. Provided edit permission for the email account to the selected Google Sheet
 * 4. Sends and email to the list of email account
 * 
 * To create a clickable button and link it to your script, follow this tutorial:
 * https://yagisanatode.com/2019/03/07/google-apps-script-how-to-connect-a-button-to-a-function-in-google-sheets/
 *
 * You can add your custom details to the GLOBALS varialbe area
 * 
 * You can add your email as a HTML in the Email.html file. You will need to add a custom loction for each item
 * in the PERS_COLS array.
 */

//****GLOBALS****
var SS_ID = "1UrTL57Rq6MYLmA9Yy76hA1AxfJDT8s_oDMve7YLeqUc"; // << insert SpreadsheetID
var SHEET_NAME = "Makeups"; // << insert Sheet ID

var EMAIL_COL = 5; // << insert email column number e.g. col E === 5
var COMPLETE_COL = 7 // << insert checkbox column that will check if email sent or not
var PERS_COLS = [3,4]/*If == null, no perstonalisation. otherwise create an array of 
                      * columns you want to add to your email template.
                      * This could be used to add a custom name or id in the title. 
                      * In the example we have the teacher's name and class number. 
                      */
var START_ROW = 4 // First data row after header, if you have one.
var MESSAGE_HEADER = "Makeup Test Assessment";

/******************************************************************
 * Executes when a custom button is clicked in Google Sheets.
 */
function sendEmail() {
  var ss = SpreadsheetApp.openById(SS_ID);
  var ssURL = ss.getUrl();
  var ssName = ss.getName();
  var ssLinkData = [ssName,ssURL];
  
  var sheet = ss.getSheetByName(SHEET_NAME);
  
  var lastRow = sheet.getLastRow();
  
  
  var uniqueEmails
  

  
  //If there are no personalized columns, just send email and give edit permisssions.
  if(PERS_COLS){
    PERS_COLS.unshift(EMAIL_COL); //Adds EMAIL_COL to start of array.
    PERS_COLS.push(COMPLETE_COL); //Adds COMPLETE_COL to end of array.
    
    var columnRanges = [];
    for(var col in PERS_COLS){
      columnRanges.push("R" + START_ROW + "C" + PERS_COLS[col] + ":R" + lastRow + "C" + PERS_COLS[col]);
    };


    //Get columns 
    var rangeOfColumns = sheet.getRangeList(columnRanges);
    
    var ranges = rangeOfColumns.getRanges();
    var valsForEachCol = [];
    for(range in ranges){
      valsForEachCol.push(ranges[range].getValues())
    };
    
    var lastDataCol = getLastDataCol(valsForEachCol[0]);
    
    uniqueEmails = getUnique(valsForEachCol);

    
    
  }else{
    //Email Range
    var emailRange = sheet.getRange(START_ROW,EMAIL_COL,lastRow-START_ROW);
    
    //Get the range of the complete column
    var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,lastRow-START_ROW);
    
    var emailRangeVals = [emailRange.getValues(),completeRange.getValues()];
    
    
    var lastDataCol = getLastDataCol(emailRangeVals[0]);
    
    //Remove duplicate Emails
    uniqueEmails = getUnique(emailRangeVals);
    
    
  };
  

  // Give edit access and send email, then returns any faulty emails errors.
  var badEmails = permissionsNemail(uniqueEmails, ssLinkData, ss);
  
  //Udate the COMPETE_COL as sent.
  var CheckSentEmail = markCheckBoxIfSent(lastDataCol, sheet);
  
  
  //Send completion prompt with any eronious emails. 
  var ui  = SpreadsheetApp.getUi()
  ui.alert("Complete",(uniqueEmails.length - badEmails.length)+" emails sent.\n"+
            "The following emails could not be sent:\n\n"+  
            badEmails.join("\n"),ui.ButtonSet.OK);
  
  
};

//******************************************************************
/* Gives permissions to each user and sends email to users. Adds any personalizatoin
 * values to the email. NOTE! Email message must be edited first.
 *
 * @param emailList {array} : takse the list of emails and personalization values.
 * @param linkData {array} : array of two values: Spreadsheet name, Spreadsheet URL
 * @param ss {obj} : Spreadsheet method.
 * @returns list of failed emails.
 */
function permissionsNemail(emailList, linkData, ss){
  var badEmails = [];
  
  
  for(user in emailList){
    try{
      //Give permissions to edit. 
      ss.addEditor(emailList[user][0]); 
      
      //Send emails. User, Sub, Body
      GmailApp.sendEmail(emailList[user][0],
                         MESSAGE_HEADER,
                         getGmailMessage(emailList[user], linkData));
      

    }catch(e){
      badEmails.push(emailList[user][0]);
    };
  };
  
  return badEmails;
};


//******************************************************************
/* Gets the unique value of a 3D array of columns from the GAS getRangeList method.
 * The identifying column is always the first array. It then reduces the array to its
 * unique column value and adds all/any other selected column values to the unique array.
 * 
 * @param rng {array} : 3D array of columns from GAS getRangeList
 * @returns 2D array of unique value based on the first column.
 *
 */
function getUnique(rng){
   rng = removeSentEmails(rng);
   var unique = [];
  
  for(var row in rng[0]){
    
    //Add first row.
    if(row ==0){ 
     var row_vals = [];
     for(var col in rng){
       row_vals.push(rng[col][row][0]);
       };
       
     unique.push(row_vals); 
     };
    
    //Compare against unique array and check there are no matches.
    var isUnique = true;
    for(var unq in unique){
    
      if(unique[unq][0] === rng[0][row][0]){
        isUnique = false;
        break;
      };
    };
    
    if(isUnique){
      var row_vals = [];
      for(var col in rng){
        row_vals.push(rng[col][row][0])
      };
      
      unique.push(row_vals);
    };  
  };
  return unique;

};
//******************************************************************
/* Removes any row that has been checked as sent.
 * It iterates through the last column, which is the COMPLETE_COL value.
 * If a row has true, it splices out that row in each of the columns.
 * 
 * @param rng {array} : 3D array of columns from GAS getRangeList
 * @returns {array} :3D array of columns minuse those that have already been sent.
 *
 */
function removeSentEmails(range){
  var lastCol = range.length-1
  
  for(var row = 0; row < range[lastCol].length; row++){
    if(range[lastCol][row][0] === true){
    
      for(var col in range){
        range[col].splice(row,1);
      };
      row--; //Used to reset the row lenght after splice.
    };
    
  };
  
  return range;
};

//******************************************************************
/* Gets the Last data row of the Column to remove any empty rows in the range.
 * 
 * @param emailRows {array} : 2D array of the email column
 * @returns {number} : The last non-empty row in the column
 *
 */
function getLastDataCol(emailRows){
  var count = START_ROW
  
  for(row in emailRows){
    if(emailRows[row] != ""){
      count++
      };
  };
  return count;
};

//******************************************************************
/* Marks all checkboxes within the range of all the email rows to to checked.
 * 
 * @param last_row {number} : Created from getLastDataCol();
 * @param sheet {object} : GAS Sheet class
 *
 */
function markCheckBoxIfSent(last_row, sheet){

  var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,last_row-START_ROW);
  completeRange.check(); 

};

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

/******************************************************************

* Give Edit Permissions and Send Email to selected users on Spreadsheet

* sendEmail() is initialized from a button. The script:

* 1. Finds all the emails and removed duplicates

* 2. Select only those users who have not been checks as having an email sent.

* 3. Provided edit permission for the email account to the selected Google Sheet

* 4. Sends and email to the list of email account

* To create a clickable button and link it to your script, follow this tutorial:

* https://yagisanatode.com/2019/03/07/google-apps-script-how-to-connect-a-button-to-a-function-in-google-sheets/

* You can add your custom details to the GLOBALS varialbe area

* You can add your email as a HTML in the Email.html file. You will need to add a custom loction for each item

* in the PERS_COLS array.

//****GLOBALS****

var SS_ID = "1UrTL57Rq6MYLmA9Yy76hA1AxfJDT8s_oDMve7YLeqUc"; // << insert SpreadsheetID

var SHEET_NAME = "Makeups"; // << insert Sheet ID

var EMAIL_COL = 5; // << insert email column number e.g. col E === 5

var COMPLETE_COL = 7 // << insert checkbox column that will check if email sent or not

var PERS_COLS = [3,4]/*If == null, no perstonalisation. otherwise create an array of

* columns you want to add to your email template.

* This could be used to add a custom name or id in the title.

* In the example we have the teacher's name and class number.

var START_ROW = 4 // First data row after header, if you have one.

var MESSAGE_HEADER = "Makeup Test Assessment";

/******************************************************************

* Executes when a custom button is clicked in Google Sheets.

function sendEmail() {

var ss = SpreadsheetApp.openById(SS_ID);

var ssURL = ss.getUrl();

var ssName = ss.getName();

var ssLinkData = [ssName,ssURL];

var sheet = ss.getSheetByName(SHEET_NAME);

var lastRow = sheet.getLastRow();

var uniqueEmails

//If there are no personalized columns, just send email and give edit permisssions.

if(PERS_COLS){

PERS_COLS.unshift(EMAIL_COL); //Adds EMAIL_COL to start of array.

PERS_COLS.push(COMPLETE_COL); //Adds COMPLETE_COL to end of array.

var columnRanges = [];

for(var col in PERS_COLS){

columnRanges.push("R" + START_ROW + "C" + PERS_COLS[col] + ":R" + lastRow + "C" + PERS_COLS[col]);

};

//Get columns

var rangeOfColumns = sheet.getRangeList(columnRanges);

var ranges = rangeOfColumns.getRanges();

var valsForEachCol = [];

for(range in ranges){

valsForEachCol.push(ranges[range].getValues())

};

var lastDataCol = getLastDataCol(valsForEachCol[0]);

uniqueEmails = getUnique(valsForEachCol);

}else{

//Email Range

var emailRange = sheet.getRange(START_ROW,EMAIL_COL,lastRow-START_ROW);

//Get the range of the complete column

var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,lastRow-START_ROW);

var emailRangeVals = [emailRange.getValues(),completeRange.getValues()];

var lastDataCol = getLastDataCol(emailRangeVals[0]);

//Remove duplicate Emails

uniqueEmails = getUnique(emailRangeVals);

};

// Give edit access and send email, then returns any faulty emails errors.

var badEmails = permissionsNemail(uniqueEmails, ssLinkData, ss);

//Udate the COMPETE_COL as sent.

var CheckSentEmail = markCheckBoxIfSent(lastDataCol, sheet);

//Send completion prompt with any eronious emails.

var ui = SpreadsheetApp.getUi()

ui.alert("Complete",(uniqueEmails.length - badEmails.length)+" emails sent.\n"+

"The following emails could not be sent:\n\n"+

badEmails.join("\n"),ui.ButtonSet.OK);

};

//******************************************************************

/* Gives permissions to each user and sends email to users. Adds any personalizatoin

* values to the email. NOTE! Email message must be edited first.

* @param emailList {array} : takse the list of emails and personalization values.

* @param linkData {array} : array of two values: Spreadsheet name, Spreadsheet URL

* @param ss {obj} : Spreadsheet method.

* @returns list of failed emails.

function permissionsNemail(emailList, linkData, ss){

var badEmails = [];

for(user in emailList){

try{

//Give permissions to edit.

ss.addEditor(emailList[user][0]);

//Send emails. User, Sub, Body

GmailApp.sendEmail(emailList[user][0],

MESSAGE_HEADER,

getGmailMessage(emailList[user], linkData));

}catch(e){

badEmails.push(emailList[user][0]);

};

return badEmails;

};

//******************************************************************

/* Gets the unique value of a 3D array of columns from the GAS getRangeList method.

* The identifying column is always the first array. It then reduces the array to its

* unique column value and adds all/any other selected column values to the unique array.

* @param rng {array} : 3D array of columns from GAS getRangeList

* @returns 2D array of unique value based on the first column.

function getUnique(rng){

rng = removeSentEmails(rng);

var unique = [];

for(var row in rng[0]){

//Add first row.

if(row ==0){

var row_vals = [];

for(var col in rng){

row_vals.push(rng[col][row][0]);

};

unique.push(row_vals);

};

//Compare against unique array and check there are no matches.

var isUnique = true;

for(var unq in unique){

if(unique[unq][0] === rng[0][row][0]){

isUnique = false;

break;

};

if(isUnique){

var row_vals = [];

for(var col in rng){

row_vals.push(rng[col][row][0])

};

unique.push(row_vals);

};

return unique;

};

//******************************************************************

/* Removes any row that has been checked as sent.

* It iterates through the last column, which is the COMPLETE_COL value.

* If a row has true, it splices out that row in each of the columns.

* @param rng {array} : 3D array of columns from GAS getRangeList

* @returns {array} :3D array of columns minuse those that have already been sent.

function removeSentEmails(range){

var lastCol = range.length-1

for(var row = 0; row < range[lastCol].length; row++){

if(range[lastCol][row][0] === true){

for(var col in range){

range[col].splice(row,1);

};

row--; //Used to reset the row lenght after splice.

};

return range;

};

//******************************************************************

/* Gets the Last data row of the Column to remove any empty rows in the range.

* @param emailRows {array} : 2D array of the email column

* @returns {number} : The last non-empty row in the column

function getLastDataCol(emailRows){

var count = START_ROW

for(row in emailRows){

if(emailRows[row] != ""){

count++

};

return count;

};

//******************************************************************

/* Marks all checkboxes within the range of all the email rows to to checked.

* @param last_row {number} : Created from getLastDataCol();

* @param sheet {object} : GAS Sheet class

function markCheckBoxIfSent(last_row, sheet){

var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,last_row-START_ROW);

completeRange.check();

};

Email Script: Email.gs

//******************************************************************
/* Gets the email message and add any personalize infomation
 * Use the pers_data array to feed custom information into each email. The pers_data array will 
 * contain the following: 
 *    [email, 1st custom column, 2 custom column, ... , ... , checkbox column]
 * Use the link_data to get the Spreadsheet Name and URL address:
 *    [Spreadsheet name, Spreadsheet URL]
 * Delete the example below and add your own.
 *
 * @param pers_data {array} : Personalized data contains 2d array with email, pers data, pers data, ...
 * @param link_data {array} : Link data
 * @returns : email message
 *
 */
 
 function getGmailMessage(pers_data, link_data){
   var message = "Hello,"+ pers_data[2]+
    "\nYou have one or more students in your section ("+ pers_data[1] + ") who need to complete a makeup test. " +
    "Please come to my office and pick up a copy of the test along with "+
    "the answer key." + 
    "\n You will need to arrange a time with your student/s to complete the " +
    "test in the next few days. Once you have completed the test, please mark " +
    "it and add it tothe spreadsheet that is linked below: " +
    "\n" + link_data[0] + ": \n" + link_data[1];
    
    return message;
 };

//******************************************************************

/* Gets the email message and add any personalize infomation

* Use the pers_data array to feed custom information into each email. The pers_data array will

* contain the following:

* [email, 1st custom column, 2 custom column, ... , ... , checkbox column]

* Use the link_data to get the Spreadsheet Name and URL address:

* [Spreadsheet name, Spreadsheet URL]

* Delete the example below and add your own.

* @param pers_data {array} : Personalized data contains 2d array with email, pers data, pers data, ...

* @param link_data {array} : Link data

* @returns : email message

function getGmailMessage(pers_data, link_data){

var message = "Hello,"+ pers_data[2]+

"\nYou have one or more students in your section ("+ pers_data[1] + ") who need to complete a makeup test. " +

"Please come to my office and pick up a copy of the test along with "+

"the answer key." +

"\n You will need to arrange a time with your student/s to complete the " +

"test in the next few days. Once you have completed the test, please mark " +

"it and add it tothe spreadsheet that is linked below: " +

"\n" + link_data[0] + ": \n" + link_data[1];

return message;

};

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:

A column with email addresses.
A column with a checkbox to mark that the email has been sent.
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

/******************************************************************
 * Give Edit Permissions and Send Email to selected users on Spreadsheet
 *
 * sendEmail() is initialized from a button. The script:
 * 1. Finds all the emails and removed duplicates
 * 2. Select only those users who have not been checks as having an email sent.
 * 3. Provided edit permission for the email account to the selected Google Sheet
 * 4. Sends and email to the list of email account
 * 
 * To create a clickable button and link it to your script, follow this tutorial:
 * https://yagisanatode.com/2019/03/07/google-apps-script-how-to-connect-a-button-to-a-function-in-google-sheets/
 *
 * You can add your custom details to the GLOBALS varialbe area
 * 
 * You can add your email as a HTML in the Email.html file. You will need to add a custom loction for each item
 * in the PERS_COLS array.
 */

//****GLOBALS****
var SS_ID = "1UrTL57Rq6MYLmA9Yy76hA1AxfJDT8s_oDMve7YLeqUc"; // << insert SpreadsheetID
var SHEET_NAME = "Makeups"; // << insert Sheet ID

var EMAIL_COL = 5; // << insert email column number e.g. col E === 5
var COMPLETE_COL = 7 // << insert checkbox column that will check if email sent or not
var PERS_COLS = [3,4]/*If == null, no perstonalisation. otherwise create an array of 
                      * columns you want to add to your email template.
                      * This could be used to add a custom name or id in the title. 
                      * In the example we have the teacher's name and class number. 
                      */
var START_ROW = 4 // First data row after header, if you have one.
var MESSAGE_HEADER = "Makeup Test Assessment";

/******************************************************************

* Give Edit Permissions and Send Email to selected users on Spreadsheet

* sendEmail() is initialized from a button. The script:

* 1. Finds all the emails and removed duplicates

* 2. Select only those users who have not been checks as having an email sent.

* 3. Provided edit permission for the email account to the selected Google Sheet

* 4. Sends and email to the list of email account

* To create a clickable button and link it to your script, follow this tutorial:

* https://yagisanatode.com/2019/03/07/google-apps-script-how-to-connect-a-button-to-a-function-in-google-sheets/

* You can add your custom details to the GLOBALS varialbe area

* You can add your email as a HTML in the Email.html file. You will need to add a custom loction for each item

* in the PERS_COLS array.

//****GLOBALS****

var SS_ID = "1UrTL57Rq6MYLmA9Yy76hA1AxfJDT8s_oDMve7YLeqUc"; // << insert SpreadsheetID

var SHEET_NAME = "Makeups"; // << insert Sheet ID

var EMAIL_COL = 5; // << insert email column number e.g. col E === 5

var COMPLETE_COL = 7 // << insert checkbox column that will check if email sent or not

var PERS_COLS = [3,4]/*If == null, no perstonalisation. otherwise create an array of

* columns you want to add to your email template.

* This could be used to add a custom name or id in the title.

* In the example we have the teacher's name and class number.

var START_ROW = 4 // First data row after header, if you have one.

var MESSAGE_HEADER = "Makeup Test Assessment";

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

SS_ID: string: This is the unique id for your Spreadsheet that you can find in your spreadsheet’s URL.
SHEET_NAME: string: The name of the spreadsheet you will be referencing.
EMAIL_COL: number: The column where your emails can be found.
COMPLETE_COL: number: The column where your checkboxes are to indicate the email has been sent.
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.
START_ROW: number: The first row after your header rows.
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:

[email address, pers col item 1, pers col item 3, ... , ...]

1	[email address, pers col item 1, pers col item 3, ... , ...]

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 can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

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.

//******************************************************************
/* Gets the email message and add any personalize infomation
 * Use the pers_data array to feed custom information into each email. The pers_data array will 
 * contain the following: 
 *    [email, 1st custom column, 2 custom column, ... , ... , checkbox column]
 * Use the link_data to get the Spreadsheet Name and URL address:
 *    [Spreadsheet name, Spreadsheet URL]
 * Delete the example below and add your own.
 *
 * @param pers_data {array} : Personalized data contains 2d array with email, pers data, pers data, ...
 * @param link_data {array} : Link data
 * @returns : email message
 *
 */
 
 function getGmailMessage(pers_data, link_data){
   var message = "Hello,"+ pers_data[2]+
    "\nYou have one or more students in your section ("+ pers_data[1] + ") who need to complete a makeup test. " +
    "Please come to my office and pick up a copy of the test along with "+
    "the answer key." + 
    "\n You will need to arrange a time with your student/s to complete the " +
    "test in the next few days. Once you have completed the test, please mark " +
    "it and add it tothe spreadsheet that is linked below: " +
    "\n" + link_data[0] + ": \n" + link_data[1];
    
    return message;
 };

//******************************************************************

/* Gets the email message and add any personalize infomation

* Use the pers_data array to feed custom information into each email. The pers_data array will

* contain the following:

* [email, 1st custom column, 2 custom column, ... , ... , checkbox column]

* Use the link_data to get the Spreadsheet Name and URL address:

* [Spreadsheet name, Spreadsheet URL]

* Delete the example below and add your own.

* @param pers_data {array} : Personalized data contains 2d array with email, pers data, pers data, ...

* @param link_data {array} : Link data

* @returns : email message

function getGmailMessage(pers_data, link_data){

var message = "Hello,"+ pers_data[2]+

"\nYou have one or more students in your section ("+ pers_data[1] + ") who need to complete a makeup test. " +

"Please come to my office and pick up a copy of the test along with "+

"the answer key." +

"\n You will need to arrange a time with your student/s to complete the " +

"test in the next few days. Once you have completed the test, please mark " +

"it and add it tothe spreadsheet that is linked below: " +

"\n" + link_data[0] + ": \n" + link_data[1];

return message;

};

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.

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.

/******************************************************************
 * Executes when a custom button is clicked in Google Sheets.
 */
function sendEmail() {
  var ss = SpreadsheetApp.openById(SS_ID);
  var ssURL = ss.getUrl();
  var ssName = ss.getName();
  var ssLinkData = [ssName,ssURL];
  
  var sheet = ss.getSheetByName(SHEET_NAME);
  
  var lastRow = sheet.getLastRow();
  
  
  var uniqueEmails
  

  
  //If there are no personalized columns, just send email and give edit permisssions.
  if(PERS_COLS){
    PERS_COLS.unshift(EMAIL_COL); //Adds EMAIL_COL to start of array.
    PERS_COLS.push(COMPLETE_COL); //Adds COMPLETE_COL to end of array.
    
    var columnRanges = [];
    for(var col in PERS_COLS){
      columnRanges.push("R" + START_ROW + "C" + PERS_COLS[col] + ":R" + lastRow + "C" + PERS_COLS[col]);
    };


    //Get columns 
    var rangeOfColumns = sheet.getRangeList(columnRanges);
    
    var ranges = rangeOfColumns.getRanges();
    var valsForEachCol = [];
    for(range in ranges){
      valsForEachCol.push(ranges[range].getValues())
    };
    
    var lastDataCol = getLastDataCol(valsForEachCol[0]);
    
    uniqueEmails = getUnique(valsForEachCol);

    
    
  }else{
    //Email Range
    var emailRange = sheet.getRange(START_ROW,EMAIL_COL,lastRow-START_ROW);
    
    //Get the range of the complete column
    var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,lastRow-START_ROW);
    
    var emailRangeVals = [emailRange.getValues(),completeRange.getValues()];
    
    
    var lastDataCol = getLastDataCol(emailRangeVals[0]);
    
    //Remove duplicate Emails
    uniqueEmails = getUnique(emailRangeVals);
    
    
  };
  

  // Give edit access and send email, then returns any faulty emails errors.
  var badEmails = permissionsNemail(uniqueEmails, ssLinkData, ss);
  
  //Udate the COMPETE_COL as sent.
  var CheckSentEmail = markCheckBoxIfSent(lastDataCol, sheet);
  
  
  //Send completion prompt with any eronious emails. 
  var ui  = SpreadsheetApp.getUi()
  ui.alert("Complete",(uniqueEmails.length - badEmails.length)+" emails sent.\n"+
            "The following emails could not be sent:\n\n"+  
            badEmails.join("\n"),ui.ButtonSet.OK);
  
  
};

/******************************************************************

* Executes when a custom button is clicked in Google Sheets.

function sendEmail() {

var ss = SpreadsheetApp.openById(SS_ID);

var ssURL = ss.getUrl();

var ssName = ss.getName();

var ssLinkData = [ssName,ssURL];

var sheet = ss.getSheetByName(SHEET_NAME);

var lastRow = sheet.getLastRow();

var uniqueEmails

//If there are no personalized columns, just send email and give edit permisssions.

if(PERS_COLS){

PERS_COLS.unshift(EMAIL_COL); //Adds EMAIL_COL to start of array.

PERS_COLS.push(COMPLETE_COL); //Adds COMPLETE_COL to end of array.

var columnRanges = [];

for(var col in PERS_COLS){

columnRanges.push("R" + START_ROW + "C" + PERS_COLS[col] + ":R" + lastRow + "C" + PERS_COLS[col]);

};

//Get columns

var rangeOfColumns = sheet.getRangeList(columnRanges);

var ranges = rangeOfColumns.getRanges();

var valsForEachCol = [];

for(range in ranges){

valsForEachCol.push(ranges[range].getValues())

};

var lastDataCol = getLastDataCol(valsForEachCol[0]);

uniqueEmails = getUnique(valsForEachCol);

}else{

//Email Range

var emailRange = sheet.getRange(START_ROW,EMAIL_COL,lastRow-START_ROW);

//Get the range of the complete column

var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,lastRow-START_ROW);

var emailRangeVals = [emailRange.getValues(),completeRange.getValues()];

var lastDataCol = getLastDataCol(emailRangeVals[0]);

//Remove duplicate Emails

uniqueEmails = getUnique(emailRangeVals);

};

// Give edit access and send email, then returns any faulty emails errors.

var badEmails = permissionsNemail(uniqueEmails, ssLinkData, ss);

//Udate the COMPETE_COL as sent.

var CheckSentEmail = markCheckBoxIfSent(lastDataCol, sheet);

//Send completion prompt with any eronious emails.

var ui = SpreadsheetApp.getUi()

ui.alert("Complete",(uniqueEmails.length - badEmails.length)+" emails sent.\n"+

"The following emails could not be sent:\n\n"+

badEmails.join("\n"),ui.ButtonSet.OK);

};

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:

PERS_COL = [1,3,4,11,13,...];

1	PERS_COL = [1,3,4,11,13,...];

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…

PERS_COL = null;

1	PERS_COL = null;

…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:

The uniqueEmail 2d array
The ssLinkData, an array containing the Google Sheet name and URL
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)

//******************************************************************
/* Gets the Last data row of the Column to remove any empty rows in the range.
 * 
 * @param emailRows {array} : 2D array of the email column
 * @returns {number} : The last non-empty row in the column
 *
 */
function getLastDataCol(emailRows){
  var count = START_ROW
  
  for(row in emailRows){
    if(emailRows[row] != ""){
      count++
      };
  };
  return count;
};

//******************************************************************

/* Gets the Last data row of the Column to remove any empty rows in the range.

* @param emailRows {array} : 2D array of the email column

* @returns {number} : The last non-empty row in the column

function getLastDataCol(emailRows){

var count = START_ROW

for(row in emailRows){

if(emailRows[row] != ""){

count++

};

return count;

};

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()

//******************************************************************
/* Gives permissions to each user and sends email to users. Adds any personalizatoin
 * values to the email. NOTE! Email message must be edited first.
 *
 * @param emailList {array} : takse the list of emails and personalization values.
 * @param linkData {array} : array of two values: Spreadsheet name, Spreadsheet URL
 * @param ss {obj} : Spreadsheet method.
 * @returns list of failed emails.
 */
function permissionsNemail(emailList, linkData, ss){
  var badEmails = [];
  
  
  for(user in emailList){
    try{
      //Give permissions to edit. 
      ss.addEditor(emailList[user][0]); 
      
      //Send emails. User, Sub, Body
      GmailApp.sendEmail(emailList[user][0],
                         MESSAGE_HEADER,
                         getGmailMessage(emailList[user], linkData));
      

    }catch(e){
      badEmails.push(emailList[user][0]);
    };
  };
  
  return badEmails;
};

//******************************************************************

/* Gives permissions to each user and sends email to users. Adds any personalizatoin

* values to the email. NOTE! Email message must be edited first.

* @param emailList {array} : takse the list of emails and personalization values.

* @param linkData {array} : array of two values: Spreadsheet name, Spreadsheet URL

* @param ss {obj} : Spreadsheet method.

* @returns list of failed emails.

function permissionsNemail(emailList, linkData, ss){

var badEmails = [];

for(user in emailList){

try{

//Give permissions to edit.

ss.addEditor(emailList[user][0]);

//Send emails. User, Sub, Body

GmailApp.sendEmail(emailList[user][0],

MESSAGE_HEADER,

getGmailMessage(emailList[user], linkData));

}catch(e){

badEmails.push(emailList[user][0]);

};

return badEmails;

};

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:

sendEmail(email, subject, message)

1	sendEmail(email, subject, message)

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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

markCheckBoxIfSent()

//******************************************************************
/* Marks all checkboxes within the range of all the email rows to to checked.
 * 
 * @param last_row {number} : Created from getLastDataCol();
 * @param sheet {object} : GAS Sheet class
 *
 */
function markCheckBoxIfSent(last_row, sheet){

  var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,last_row-START_ROW);
  completeRange.check(); 

};

//******************************************************************

/* Marks all checkboxes within the range of all the email rows to to checked.

* @param last_row {number} : Created from getLastDataCol();

* @param sheet {object} : GAS Sheet class

function markCheckBoxIfSent(last_row, sheet){

var completeRange = sheet.getRange(START_ROW,COMPLETE_COL,last_row-START_ROW);

completeRange.check();

};

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.

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

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

Fabio Ramalho says:

at 10:10 pm

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?

Loading...

1. Yagi says:
  
  at 7:32 am
  
  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.
  
  Loading...
  
Shita says:

at 5:53 pm

Great tutorial! Could you maybe share the end result as well (sheet)

Loading...

Santiago says:

at 5:10 pm

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.

Loading...

Santiago says:

at 10:45 pm

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

Loading...

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

The Code

Quick Use Guide

Global Variables

Editing the Email Message

Creating A Button

Code Breakdown

sendEmail()

getLastCol(emailRows)

getUnique(rng)

permissionsNemail()

markCheckBoxIfSent()

Conclusion

Like this:

Related

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

Leave a ReplyCancel reply

The Code

Quick Use Guide

Global Variables

Editing the Email Message

Creating A Button

Code Breakdown

sendEmail()

getLastCol(emailRows)

getUnique(rng)

permissionsNemail()

markCheckBoxIfSent()

Conclusion

Share this:

Like this:

Related

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

Leave a ReplyCancel reply