Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data

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…

Continue reading “Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data”

Google Apps Script: Create an HTML email reminder service from Google Sheet data.

Google Apps Script: SpreadsheetApp, Utilities, MailApp, UrlFetchApp, HtmlService, onOpen

Ever wanted a quick way to send reminders to staff, letting them know that their task is overdue?

I often have to provide Google Sheet checklists that administration team managers can crosscheck to see if their team members have complete a certain task.

Reminding staff to complete a task can be time consuming and irritating. What if you could send an email to all those staff members who haven’t completed the task with a simple click of the button?

In this tutorial, we will learn how to create a Google Apps Script HTML email mail merge service that will send an email to a staff member who has not completed their task, indicated by a missing tick on a tickbox.

We will use the sample sheet below:

the badger Google Sheet

The Google Sheets reminder is triggered by a custom menu item.  You can see it at the top right of the image.

When the reminder is sent, it collects the rows of staff that have not indicated that they have completed the task.  It then sends an email to those staff members  with the reminder below:

HTML Email Reminder Google Apps Script
Get it! Badger! Ha!

As you can see, I’ve added some custom HTML to highlight the header and modify the width of the image. The email contains a number of unique pieces of data gathered from the Google Sheet Row:

  • The name of the user.
  • The deadline.
  • The name of the sheet (In this case, Tester).
  • The number of days overdue.

It also contains a link to the check sheet for the user to update once they have completed their task.

Back at The Badger Google Sheet, an alert window appears once the code is completed and also indicates any emails that could not be sent.

To get started you can Make a Copy of the Google Sheet by following this link:

Continue reading “Google Apps Script: Create an HTML email reminder service from Google Sheet data.”