Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code (Updated Feb 2022)

Google Apps Script: SpreadsheetApp,  addEditor/s, removeEditor/s, alert. Javascript: try…catch, forEach, join, push, template literal. Google Sheets

In this tutorial, we will go over the basics of adding users as Editors to Google Sheets with Google Apps Script. We’ll go through the process step-by-step, starting with two very basic codes and then progress on to error handling so your code doesn’t break for your user.

In Google Sheets just like Docs, Slides, Forms and Sites you can add co-editors to work on your projects. This is usually done straight from Google Drive or within the chosen Google file in the top right with the Share button.

The rules for sharing a specific user as an editor are pretty simple. The user must have either a Gmail (name@gmail.com) account, GSuite for Education domain account (name@yourdomain.com) or Google Workspace (formerly, Gsuite) account with an email in the workspace’s domain (name@yagisanatode.com).

Now that we have all the basics, let’s go ahead and write some Google Apps Script code. First of all, open a Google Sheet. It can be one that you want to use to add and remove editors with code on a project you are working on or just a practice Google Sheet. Then go to Extensions > App Script.

A very basic add and remove for a single editor in Google Apps Script

Adding an Editor

Adding a single user as an editor on a Google Sheet is really simple. Take a look:

In our function assignSingleEditor(), we first call Google Apps Script’s SpreadsheetApp class and then get the active spreadsheet. The getActiveSpreadsheet() method references the currently active sheet which will usually be the one you have your Google Apps Script project connected too. We will assign this active spreadsheet the const variable SS.

Next, we create a const variable for our editor (Line 6) and add in the email of the user we want to add as an editor.

Finally, on line 8, we add the editor grabbing the SS variable for our active spreadsheet and calling the addEditor() method. This method takes one of two different variable types:

• An email: either a gmail account or a company’s domain account that users Google Workspace or GSuite for Education.
• A user: This is often generated programmatically via referenced something like an admin API and seems not to be the common practice to use anymore.

Of course, you could put all this into a single line of code, but it does look kinda messy and more difficult to follow:

SpreadsheetApp.getActiveSpreadsheet().addEditor("billy.groat@gmail.com");

Go ahead and run the code now. You might want to add in a  friend into your practice template or use another account. Then go back to your Google Sheet and click the Share button and see if you added the person correctly.

Removing an Editor

To remove an editor, it is just as simple. Let’s go ahead and create a simple function to remove our user:

The only thing we changed here is line 8, where instead of involving the addEditor() method we called the removeEditor() method.

Real simple, right?

But what if we are not sure if the user has an accepted email? You are going to end up with an error that will stop your code in its tracks.

What to do?

Add for a single editor in Google Apps Script with error handling

This time around we’ll put our script in a Javascript try and catch statement. So that if the email is accepted then the script will proceed, but if it is an unaccepted email, then it will record the error and do something with it, but the code will still continue to run.

Take a look at the code for Adding an editor:

So what has changed?

As you can see in the highlighted lines we are using our try and catch statements we mentioned above.

First, on line 9 of the assignSingleEditorWithValidation() function, we use try to attempt to add our user.

If the email address is not suitable or is incorrect, then the code goes to the catch statement. Inside the catch statement, we can assign a bunch of rules should the attempt to run this part of the code fail. The “e” you see between the brackets is a parameter that can be used to get the details of the error for you or the user.

For our example, we are going to construct an alert popup for the user so that they know that an error occurred. We will have a special message for the alert for each of the functions. So that if we attempt to run our code on a bad email then the user will see this:

Looking down at the first line of our catch statement (line 13), we create our message for the alert popup. We use the backtick (`)syntax to make our message. This is known as a template literal. Within the message, we can assign our e for error variable directly into the message by using a dollars sign and surrounding it with curly braces (${e}).

Next, we will call the user interface method with getUi() from the SpreadsheetApp class. Line 15

This UI method allows us to create an alert popup (Line 16). The alert() method can take between 1 and 3 arguments. We’ll use all 3 arguments here. They are:

1. Title of your alert. In our example, ‘Warning’
2. Text of your alert. Our message variable.
3. They type of buttons you want for your alert. For example an okay button or yes/ no buttons. For us that is ui.ButtonSet.OK. ButtonSet has a number of options that can be found here.

But what about the removeEditor? Don’t you need a try-catch statement there too?

Good question but, no. All the removeEditor() method needs to do is look at all the current accounts that have edit permissions and remove the relevant one if it exists or does nothing if it doesn’t exist. If an account had made it through validation to be added successfully then there is no need to do that validation to remove it.

Adding Multiple Editors to a Google Sheet

If we want to add multiple editors to a group, then Google Apps Script has you covered too. However, there are two approaches that you need to consider.

Adding multiple editors when you are confident all email addresses will be successfully parsed.

If you are 100% certain that our emails are going to be accepted as editors the code is super simple:

It’s pretty clear what has changed here.

Where before, on line 7  the EDITOR variable was a string of a single email, it is now an array of email strings. I am sure that there is an upper limit but, I have yet to reach it.

Then instead of addEditor we use the addEditors() method.

See. Told ya it was easy 😉.

The addEditors() method will batch-load all selected editors into your Google Sheet as editors. This means that it is a pretty fast process compared to the function below at least.

This function would be useful if you already have a list of user’s emails that you know for certain will be acceptable as editors.

Adding multiple editors when you are not certain that the email will be a Google-approved email.

Unfortunately, we can’t simply apply the same try-catch statement to addEditors() and hope that it will skip the erroneous email and continue with the rest. What actually occurs is that if there is a bad email it will stop the process at that bad email point in the string and no further emails will be added as editors.

So what to do?

We’ll the best approach then is to go back to good old trusty assignSingleEditorWithValidation() and give it a tweak.

We will need to make a few changes to our function. Take a look:

Highlighted in yellow in the code above are the bits that have changed.

First, on line 17, we create an empty array and set it to  badEmails. If a bad email does appear during the process, we will store the error details for that email in an array here.

Next, on line 20, we create a forEach loop on our list of EDITORS. The forEach look requires a function call to be made. To do this we are using an arrow function ( => ). Within the arrow function, we iterate through each email and try and add the user as an editor. If the email doesn’t work, then the catch statement is called and the error is pushed to the badEmails array (Line 25).

Once we have complete the task we want to let the user know if there were any errors or if the whole attempt was successful. First, we will create our message variable. We know that our original badEmails array was empty at the start, which means that it contained a length of zero. If any error occurred then the length of the array will be greater than zero and we will send our user a message with a list of errors.

We use this logic in an if statement in line 33 and then assign an error message. The message contains a warning that there were errors on line 34. Then on line 35, we use the Javascript join method to form our badEmail array into one string with each item of the string separate by a new line which is represented with a \n.

The resulting error message would look like this:

If all users were added successfully, then the else condition is met and the “All users assigned” Message is posted.

Making it all a bit more usable

Throughout this tutorial, we have strived to keep our code all contained in one function. However, it not particularly useful to have a big array of emails in the function.

The codes presented above can become infinitely more useful as a part of a larger project by making the list of EDITORS or the single EDITOR a parameter of the each of the functions. Then that function can be called by another function after it has obtained your email list.

Let’s update our final example with this in mind:

In our next tutorial, we will run through two clear examples on how to assign and remove editors for a list of emails in a Google Sheet. This will really clarify how you might want to automate adding and removing editors based on task results in a Google Sheet and hopefully give you some inspiration for your own project.

To get a notification when the next tutorial is out, you can always subscribe. I post 2 to 6 times a month about Google Workspace and Google Apps Script with the occasional relevant Javascript tutorial thrown in. You know, just enough for it to be interesting, but not enough for you to feel overwhelmed.

Check out the next tutorial here!

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Work in Progress!!! Stay Tuned!)

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.