Add the Editor’s Email when they Tick the Check Box in Google Sheets with Apps Script

This tutorial is for Google Workspace Domain accounts.

Recently, I was a guest interviewee at a Google Workspace Developer Summit in Singapore and an attendee asked me how they could automatically add the editor’s email to an adjacent cell when the user checks a check box in Google Sheets.

This can be incredibly handy for project managers to see when a user completes a task or, at a glance, who edited a row.

Of course, there are some simple ways of doing this out-of-the-box within Google Sheets.

1. A simple right-click > Show edit history on any cell can reveal who has edited the cell in the past.
   
2. If you want a more detailed history of edits on your Google Sheet workbook then you can always select the version history button in the top right of your Sheet.

   

The problem with these options is that it is not there on the screen for the user to quickly see who edited what line.

In this tutorial, we are going to use some Google Apps Script magic to automatically add the editor’s email to the row when they click that checkbox.

Let’s dive in!

 

Note! If you are a regular to Yagisanatode you may find that this tutorial is quite similar to the one on adding a date time stamp when a cell is added. Just to mix things up, I have used a slightly different approach this time to add users. My recommendation is that you should try and figure the script out for yourself based on the previous tutorial and then check out the current one to see how you compared. Have fun and good luck. ~Yagi.

Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated July 2023)

The Starter Sheet

Grab a copy of the Starter Sheet to play along!

Auto Add Email on Checkbox | STARTER

Then head to Extensions > Apps Script to get to the IDE.

The Video Tutorial

 

The Setup

On our Starter Sheet we we have two sheet tabs that require an email when the tickbox is ticked. We will need to gather the following information:

1. Tasks – Here we want to automatically add the email in column D each time the checkbox in Column C is ticked.
   1. Sheet name: “Tasks”
   2. Checkbox Colum Number: 3
   3. Checkbox Row Start: 2
   4. Column Offset for Email: 1
      
2. Pot Luck – Here we will check the box at the end and then automatically add the email to the start. Because we will insert our email to the left of the checkbox, we will use a negative integer to move the email 3 columns to the left.
   1. Sheet name: “Pot Luck”
   2. Checkbox Colum Number: 4
   3. Checkbox Row Start: 3
   4. Column Offset for Email: -3
      

What’s with the offset? Why not use a column reference?

We can use the <a href="https://developers.google.com/apps-script/reference/spreadsheet/range#offsetrowoffset,-columnoffset">offset method in Google Apps Script to quickly allow us to edit a location based on an origin. This saves us building a completely new range in the code.

Let’s go ahead and store this location data in our Apps Script project.

A Global Variable Containing Location Data

Create a constant variable called ‘LOCS’.

Inside this object, we are going to build an array of objects. Each item in the array will indicate a new Sheet tab. This will allow you, in future, to add other sheet tabs with new locations to apply your script in the same Google Sheets workbook.

This approach is much easier to update and read than adding all of your data directly into the code.

Add the following code:

To add another location, make a copy of one of the objects and add the 4 new properties.

Triggering the Event onEdit()

Next, add the onEdit() function below. This function is a reserved simple trigger that Google Apps Script uses. When the script is saved, the IDE will look for a function called onEdit() if it exists, then it will record a trigger for the connected (“bound”) Google Sheet.

Each time you or a fellow editor makes an edit on the sheet, the onEdit() trigger function will run your script.

This trigger also has a handy event parameter that is often indicated by the letter, ‘e’. This event parameter is an object that contains the value, range, previous value, email of the editor, trigger ID and source spreadsheet.

Looking at that list, you can probably guess what properties we might want to grab for our own script here.

In our project, we are going to just call and run another function from onEdit(). It is pretty good practice to abstract your processes to other functions outside of the onEdit() just in case you want to add some more trigger events in future.

Add the following function to your script:

Some Things Worth Noting About onEdit()

Running onEdit from the IDE

A common comment I get in onEdit() tutorials is that the function returns the following error when run from the IDE rather than the spreadsheet.

Why is this occurring?

This is because the event (e) parameter has nothing to draw from and returns undefined as a result. The script needs to be run from the spreadsheet for it to work.

You can still add logging, though.

Limitations to onEdit

There are a number of restrictions to what scripts you can run with the onEdit trigger the main ones developers fall victim to are:

• Trying to modify other files. The one bound to the script is fine but others will not be accessible.
• Other services like Gmail App or Calendar App, that require authorisation will not be accessible.
• The script will not run for users who have ‘view’ only access.
• Importantly for us, there is a rare authorisation occasion where the user’s email cannot be gleaned programmatically. It’s a rare occurrence but well worth noting.

Take a look at the full list of limitations here.

Another limitation is that users must also share the same domain. For example, all users on my @yagisanatode.com account will be able to display their emails automatically, but if an editor has a @gmail.com account then their email will not be shown.

Adding the Editor’s Email to the Google Sheet

Finally, we are going to write our code.

Our biggest task for this project is to ensure that the user is editing the correct location.

I have seen onEdit() functions get really bogged down and slow with bad implementation of location validation. My rules for a fast onEdit() process is simple:

1. Start from the sheet tab and work your way down. This means finding the correct:
   1. Sheet, then,
   2. Range, then,
   3. Cell value
2. Only grab the data that is needed at each stage of validation. For example, if all I need is the sheet name then don’t get the sheet range or or cell value or collect another range. This is not needed. If we are in the wrong location, we should stop our script as quickly as possible.

Time to look at the function.

The Code

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

The addEditorEmail(e) function takes one argument. The event (e) parameter retrieved from onEdit().

Line 8 – Retrieves the range property from the event parameter. This property is the same as SpreadsheetApp.getActiveRange() and allows you to use Spreadsheet App’s Range class methods on the edited range.

Lines 11&12 – From the range above we can now call the getSheet() method to retrieve the Sheet Class methods and then get the current sheet tab name with getName().

Line 14 – Now that we have the current sheet name we can compare it against our list of sheets in the ‘LOCS’ global variable we created at the start.

A fast way of doing this is to use the JavaScript ‘find’ method. This method allows us to iterate over our array with an arrow function and if we find an array object property with a matching sheet name then it will retrieve that object of data in the array.

Line 16 – However, if no sheet tab name was found the ‘find’ method will return ‘undefined’ and we will want to immediately stop our script.

Lines 20&21 – Our next task is to ensure that the user is on the column with the checkboxes. First, we need to get the column number and row number so we can compare them against our target column and row ranges in our LOCS global variable.

Line 23 – Next, we compare the current cell column against the checkBoxColumn in our location data. If it is a match, then we check if the current cell row is greater than or equal to the location data’s checkBoxRowStart value.

Line 25 – If we have a match, then we will now collect the current cell’s value for our final validation and store it in our checkboxValue variable.

Line 27 – At this stage, we know that we are going to apply either an email to the target email location if the box is checked or remove the email if it is unchecked. So here we will select the range and apply the offset using the Spreadsheet App Range class ‘offset’ method.

This method can take two arguments. The first is how many rows we should look up or down. For us, this will always be zero, but you could try and change this to see what happens. The second is how many rows we need to add our email either left or right of the current cell.

As I mentioned in the Globals section, a positive integer will offset to the right and a negative integer will offset to the left when setting the column argument.

Line 30-24 – Here we now check if the checkbox value is true. If it is, then we will set the value of our offset to the ‘user’ of the event parameter. This will display the user’s email.

Note! Prior to setting the email, you might want to do more validation. For example, you may wish to collect the current row data and ensure it is all filled in correctly first. If it is then insert the email. If not, then uncheck the box and add a Toast or Alert dialogue to inform the editor.

Lines 35-38 – If the checkbox is false, then we want to remove any existing email in the target cell. Here we can use the ‘clearContent‘ method to clear just the content from the cell.

Run the script

Once you have added in the last function above, head back to your connected Google Sheet and try and make an edit.

To test, first try and make an edit to the desired location and then double-check to ensure that editing other locations will not add an email.

Finally, create a new sheet tab with a different checkbox and email location and then update the global variable to see it work in action.

Conclusion and Where to Next

The onEdit() simple trigger function can be a handy tool to automate some of the work out of your team’s and your spreadsheet processes.

One thing I do to use consistency with my team is to italicise any column that should not be edited. These usually either have automation in them like this one or they contain formulas. I also add a note to the header of these columns to explain what is happening.

Why not just protect the range?

If we protect the range then the editor will get an error informing them that their automation was not able to update the cell. There are hacky workarounds, but a well-trained team should be okay with this. If you are super concerned, then should probably think about using a web app form or even a Google Form to add data to your sheet.

Where to next? 

See if you can automatically add a date and time stamp as well as the email to the row. Check out this tutorial for that:

Add the Current Date to a Sheet When Data Is Added So That The Date Does Not Change(Static) – Google Sheets (Updated July 2023)

Want to check all checkboxes with an all-powerful parent check box? Check out this tutorial:

Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

I also have a number of tutorials on onEdit on my site that you can browse here:

More on onEdit()

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I 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.

~ Yagi