Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)

Google Apps Script, Google Sheets, Javascript

Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet?

I know that I have come across these conditions a number of times in the past. Maybe you have a task list that you want to send your team each time a task is allocated to them. Once they let you know that they are done, by say, entering a set of values or checking a task complete box on the row they need to work on, you want to be able to remove their edit permissions from your sheet.

In this post, we have created a Google Apps Script that will add and remove editors to a Google Sheet based on the spreadsheet’s data. More specifically, this script will:

• Grab the users name and email in each row along with whether or not they have complete the task or if the Google Sheet has been shared and sent to the user.
• Share the assigned user to the Google Sheet.
• Send an email to the user. A separate Google Sheet tab is added to the sheet so an administrator can add their custom email message.
• Automatically check a reference column of checkboxes indicating that the assigned user has been shared as an editor on the Google Sheet and an email has been sent to them.
• Once the task has been complete the user check the “Edit Complete” checkbox in their assigned row.
• Either automatically each day or when the Google Sheets administrator clicks the button,  each user who has completed all assigned tasks is removed from having edit permissions to the Google Sheet.

The best way to probably understand this script is through an example…

Note! As always take what you need from this and skip the rest. The content below will help you to skip to what you want to read. 

[EZ-TOC]

The Example: Assigning famous editors, editorial tasks, by giving them edit permission

*Ah… the things I do to amuse myself. 😁🤓

I have a fictional editorial company and I am using a Google Sheet to assign editing tasks to my editors on my Google Sheet named, Awaiting  Edit. I only want to give my editors access to the Google Sheet if they have a task and once they are done, I want to be able to either manually remove all editors who have currently completed the task or they will be removed automatically at the end of each day.

Let’s take a look a the main Edit sheet tab of our Awaiting Edit Google Sheet.

Awaiting Edit: Edit tab

Take a look at our example sheet below:

In this tab, we have a list of documents to be added, their reference numbers and then the assigned editor’s name and email. We will use the name and email (columns C and D) to send personalised emails and assign or remove edit permissions to our sheet.

Next, we have a list of two columns of checkboxes:

• Edit Complete: Assigned editors check this box to indicate that they have completed the task.
• Email Sent Admin Only: This box will be checked when the Google Sheets administrator/s clicks the green Add & Email Editors button.

Add & Email Editors button

In our example, the administrator either by hand, from a form or programmatically, enters new tasks to the sheet and assigns and editor. Once they have assigned new tasks and editors to the sheet for that session, they then click the Add & Email Editors button.

This runs the Google Apps Script Automation that will grab all the data from the editor’s name, their email and the Email Sent checkboxes. Any row that contains an email and checkbox that is not checked will be:

1. Added as an editor if they are not already and editor.
2. Sent a single email even if the editor has multiple tasks.
3. The Email Sent checkbox is checked to indicate that the automation is complete.

Error handling

The main area where an error might occur is when an email is entered incorrectly. The automation will throw an alert on the Google Sheet but will continue to assign editors to the sheet and send them an email.

If all emails were correct, then the follow alter will appear to indicate to the administrator  that there are not problems:

More details on how editors are added to sheets and the error handling can be found here.

Preparing the Emails Message

On the Awaiting Edit Google Sheet, there is a separate Sheet Tab called Email.

Here the assigned range is blue, in our case A4:A9 contains a space for the Google Sheet administrator/s to create an email template. Luckily for them, the Subject line, salutation and link to this Google Sheet is added automatically for them.

Removing the Editor

Removing editors can be done manually by clicking the button, Remove Editors or with a time trigger to be done on a daily basis.

Editors are only removed if:

1. They are not also assigned administrators of the Google Sheet.
2. They have complete all assigned tasks and check the corresponding Task Complete check box.

 

Quick-Use Guide

There are a few steps you need to take to implement this automation for your own project. Don’t worry, you won’t need to know any code and I will walk you through each step.

Let’s get started:

Setup Your Google Sheet

Requirements

You can incorporate this Google Apps Script automation into your own project. However, your data must contain a column with the name of your editor and another column with the editors corresponding email. In our example, this is columns C and D.

For your own project, names and emails can be in any column. I often hide the email column or put it at the end so it isn’t in the way for the user. You can change the header for these to any text or language you like.

Adding Some maintenance checkboxes

This automation uses two columns of checkboxes. It’s probably easier to talk about them in reverse order.

In our example, we have an Email Sent Admin Only column in grey in column F. This column will be checked automatically at the end of the automation process. Not only do the checked checkboxes of this column indicate to your Google Sheet admins which user has received an email and has been given edit permissions, but it is also used in the Google Sheet automation to help find what users need to be added as editors and sent an email.

It is recommended that you as the developer protect this column for all users except for the administrators of this Google Sheet.

In our example, we also have a Task Complete column of checkboxes. Assigned editors update this checkbox once they have completed their assigned task. This helps the admin of this Google Sheet to know that the task is complete and they can use the remove button to remove the user. Alternatively, this column is referenced when the Remove Editor automation is run daily on a time trigger.

In both of these checkbox columns, you can place them anywhere in your sheet adjacent to the rest of your data. You can also rename the headers to whatever you desire.

Adding an “email” tab to your Google Sheet

We want our Google Sheet admin to be able to update the email template that the automation will send to the users.

In your Google Sheet, create a new sheet tab and name it whatever you want; just make sure you take note of the name of this new sheet tab. In our example, we named our sheet tab “Email”.

You will assign a single column for your admin to be able to insert their email template message. You can make it any length of rows. In our example, we made it 6 rows.

It is probably a good idea to leave a set of instructions for the admin and let them know that the personalised salutation and link to this Google Sheet will be handled by the Google Apps Script automation.

Oh, and also make sure they protect the sheet from other users and hide it when they are done.

Setup Your Code

Now that you have your Google Sheet all set up it is time to add your Google Apps Script code. If you have never worked with Google Apps Script before the tutorial below is a good primer, but not essential:

Can I modify Google Sheets with code?

To access the script editor in your Google Sheet, go to Extensions > App Script 

Paste the code

Once you are in the Script Editor, delete out the demonstration function in the Code.gs file and paste in the following code:

If you hover over the code above there will be a little header menu at the top that appears. One of the options will be a copy button.

Once you have pasted in the code, save your project by clicking the save button on the header menu. You will be asked to name your  Google Apps Script project. I often name mine to the same title as the connected sheet.

Update Global object with your own data

You will need to update a few things in the code for it to work for your own project. Fortunately, everything you need to update can be found at the top of the Code.gs file within the MY_DATA object with instruction on what you should change.

As you can see, all column locations are referenced as a number. So column A is 1, B is 2, C is 3, etc. 

The final object on line 19 is the list of permanent editors, these are more than likely going to be your administrators of the Google Sheet. To add more than one editor separate them by a comma, for example:

create buttons and time triggers

Now that we have updated our code it’s time to add buttons and a time trigger.

Buttons

Buttons in Google Sheets are just images with the shape of a button that you then add a textbox with text to. To draw a button, click on the cell you want to add it to and then go to Insert > Drawing. You will find the button in the shapes menu.

We labelled our buttons Add & email editors in green and Remove Editors in grey.

Once you have created your button, click Save and Close in the Drawing frame and the button will appear. Go ahead and resize them to fit for your own preference.

Next, you will need to assign your Google Apps Script function to each of your buttons. You can do this by right-clicking the button, clicking on the ellipses that appear in the top right corner and then select Assign a script. Add the following functions on the right of the button name below to each button (remember, if you changed the names, it will need to go into a new button).

• Add & email editors – shareAndSend
• Remove editors – removeEditors

That’s it done. Your buttons are ready to go. If you want to learn more about creating buttons, you can check out this post:

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

Note, the first time you run the code you and your users with access to the buttons will face a warning and will ask you to authenticate the code. More on this here:

Running Google Apps Script for the First Time: What’s with all the Warnings!

Set a Time Trigger to Remove Editors Daily (optional)

If you don’t want the hassle of manually going into your Google Sheet each day or week to remove editors who have completed their task you can add a time trigger to the removeEditors() function.

To do this, go back into the script editor. In the side, menu select  triggers. This will open a separate browser tab into a triggers page for your Google Apps Script trigger.

Down the bottom right of the page click the blue box called Add trigger. A popup window with some options will appear. The first option will ask you to assign a function. Go ahead and assigned the function removeEditors.

Ensure Choose which deployment should run is set to Head. 

In the following option, Select event source as Time driven. This will change the menu options below it. Under Select type of time based trigger select, Day timer. Then under Select time of day, choose, Midnight to 1am.

When you are all done, hit Save down the bottom right. The popup menu will close and you will see a summary of your trigger in your projects Triggers menu. You can close the triggers menu now.

You can access the example Google Sheet here:

Awaiting Editors Google Sheet

So that is all there is to implementing this automation into your own project. I would love to hear how you applied this code to your own projects in the comments below.

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.

A brief code breakdown

If you have been following along from the previous post on adding and removing editors from a Google Sheet with Google Apps Script, then you would have seen a familiar function in the code. If not, I recommend that you take at that tutorial:

Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code

I’ve taken pains to comment on all the code and ensured that the functions and variable names are readable.

In this section, I’ll just highlight a few interesting parts of the code and what each function does with links to relevant documentation.

Before I go on, you’ll note that some of the functions end with an underscore. Google Apps Script recognises these as private functions and it will not display these functions in your script run menu.

I’ve also added a MY_DATA object as a global variable here to make it easier for non-coders to customise the variables for their own project.

shareAndSend()

This is the main driving function that is called when the Add & email editors button is clicked.

From lines 7-15 we update our function with our global variable. During this process, we call the current spreadsheet (line 9) with the SpreadsheetApp Class and the getActiveSpreadsheet() method. We then prepare both the Editors and Email sheet tab with the getSheetByName() method.

Our next task is to get the range we will use to find our editor’s details. One of the trickiest things is to find out where the columns start and end based on the locations of the name, email and, share and sent columns. To do this we use the Object.keys() function on the forEach() method to iterate over the object of COLS found on lines 11 to 15.

We then use a start and end column ternary operator that is basically an inline if-statement that updates the start_col if the COLS object’s value is less than the current value of the start_column and updates the end_col if the  value is greater than the current end_col. Lines 22-28

On lines 31 and 32 we first get the range of data from the DATA_SHEET sheet using the getRange method. There are multiple options to invoke this method, but for us, we will use the 4-argument approach. This approach takes the start row, the start column, the row depth, and the column width. To get the row depth we use the getLastRow() method. Then we subtract the start row from it.

Once the range is collected we use the getValues() method to grab the data in this range as a 2d array of values.

Lines  34 – 40 reestablish the locations of the columns on the data_vals variable.

Then, on lines 42-48 our goal is to reduce the data_vals array to exclude all previously sent emails. We do this with a filter method, which includes only those rows that contain a name and an unchecked Share and Send box.

Next, on lines 50-53  we prepare our data and send it off to the assignMultipleEditorsWithValidation_(emails); for users to be added as editors. This function only takes a 1d array of emails. To create this we use the map method to create an array of only emails.  We know that some of these emails may be duplicates if the editor has more than one task. These are removed by using new Set() in a spread operator.

On lines 55 to 57, we send our new editors an email to let them know they have a task. First, we need to readd names back to our list of unique emails. We invoke a small function called addEmailsAndNames_ which takes three arguments, the unique email list, the trimmed set of data values and the new column number. This function will return a 2d array of  unique emails with their assigned names called emailsAndNames .

Once complete, we send emailsAndNames to our sendEmail_ function along with the email_sheet and text range the message template is in on that sheet.

When the emails have been sent, we then need to check all the corresponding boxes in the Email Sent column. Lines 61-65 finds the first unchecked row of the Email Sent column by counting all the checkboxes in the row from the start of the data. On line 68, this is added to the getRange method. This time we only invoke 3 arguments for this method. The start row, column and row depth. We get the row depth by counting the length of the data_vals_trimmed 2d array. Once we have the range, we set all checkboxes to checked on line 69.

addEmailsAndNames_

This short function takes 3 parameters, the email array, the trimmed 2d array of data and the object of new column positions. The function then maps through the email array. Within each iteration of the map, it searches the data by referencing the email and if it finds a match, it adds the name to the email.

The function then returns a 2d array of unique emails and their names.

sendEmail_

The sendEmail_ function takes 3 parameters:

1. editors – the 2d array of emails and names we created earlier.
2. sheet – the email sheet object
3. A1range – the range the message is in.

The first task for this function is to create the message from the Email Google Sheet Tab. We collect the values using another approach to getRange, applying an A1notation value which is the start cell, found by the column letter and the row number and the end cell found the same way.

We then use getValues to get the data from each cell in the range. On lines 16-19 we stack a bunch of methods to transform the message into a string. First, we use flat to transform the 2d array of values into a single dimensional array. Then we filter out any element of the array that does not have text. Finally, we use join to convert the array to a string that is separated by an HTML break (<br>).

The next task is to grab both the name of the Google Sheet document and its URL. We get the name by referencing the active spreadsheet method and then called the getName() method. Then we use the getURL() method to grab the URL. Thanks for making things so easy Google Apps Script. For the last part, we create a link to the Google Sheet that we will put at the end of our email. Lines 22-24

Lastly, we loop through the editor array from lines 27 to 41. During each iteration, we grab the email of the recipient and create a subject line that includes the name of the document. We then combine the body of the email by adding the salutation that includes the name of the editor, the message and the link to the Google Sheet at the end. Here we are using template literals to create our text and insert our variables inline.

Our final task is then to attempt to send the email using the mailApp class using the sendEmail method. This method can take a number of approaches but the one we are using will take an object containing the keys: to, subject and HTMLbody. This will allow us to add an HTML link and use the break HTML tag to return each line. We run this inside a try...catch statement just in case there is a bad email.

assignMultipleEditorsWithValidation_

We have already covered this in our previous tutorial below:

Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code

removeEditors

This function is initialised when either the time trigger is activated or the Remove Editors button is clicked.

First up, we bring in all our globals and gather our needed sheet data on lines 3-8.

Next, we need to find our start column again. This time we are only referencing the email column and the task complete column we use a ternary operator to determine which column is the smallest and that becomes the start column (Line 11). Once we have that we need to determine the column width by subtracting the email column by the task complete column. We use the Math.abs to ensure that the end result is a positive number and then add 1 (Line 13).

Now that we have the start col and col width we can get the range of our data and their values. Lines 16 & 17.

Lines 20 and 21 update the column locations of the email and task complete with the new 2d array, vals.

We need to determine which rows and editors who have not completed their tasks because we should not remove them. We do this on lines 25 to 27  with the filter method by first checking the row contains an email and then checking that the Task Complete box has not been checked. Then we create a map of all the emails of those whose task is incomplete.

Back in our global object, we made a list of all the permanent editors who should not be removed from the Google Sheet. Using the spread syntax again, we first use new Set on our incomplete email list and then add our permanent editor list to create a brand new array on line 32.

Next, we want to compare the currently available editors on our Google Sheet with our list of editors we want to remove. On line 35 we use the Google Apps Script getEditors() method to get a list of editors. Within this method, we need to call getEmails() as we map through each editor to create a list of emails of the editors. Lines 35-37

To crosscheck our list of editors with our list of incomplete and required editors we use forEach to loop through our editor list and if that email is not included (using the includes method) in our list of uniqueEditorIncomplete we remove the email from the list with the removeEditor method. Lines 41-45

Our last task is to send the user an alert to let them know that the task is complete. Lines 48-50.

Conclusion

Towards the end of writing this tutorial, I discovered that I wrote a similar tutorial on this last year 🤦‍♂️:

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

It was actually quite interesting the change in my coding style towards a more functional approach using methods like map and filter now. Also compared to the previous tutorial, I have more cross-checking conditions that I learned over time were more necessary. This current tutorial also makes it easier for a non-coding admin of the Google Sheet to update the email and modify the global variable object for their own project. Finally, I added in a ‘remove editor’ process to more fully help out with automation.

I can honestly say that writing this version, took a lot less time than the first version last year.

Anyway, I hope you found this useful and you found some good snippets for your own projects.

If you would like to see more of these types of coding projects hit the like button so I know that the big write up was worthwhile. If you are interested in a regular dose of all things Google Workspace, Google Apps Script and JS, please subscribe (top right or just below the comments section).

~Yagi