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.