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: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times

Google Apps Script, Google Sheets, Javascript: Spread operator, fill, new Array, forEach, isArray, flat, every, slice, length, typeOf

Imagine if you have a set of data, say, Cat, Dog, Goat and you want to create a new column where each item is repeated 3 times.

Spread by number google sheets

Essentially what we are doing here is spreading out our original list of numbers in multiples of 3.

What if we wanted to specify how many times each item will be spread out into our new column? For example, maybe we want cat to repeat 2 times, dog to repeat 4 times and goat to repeat 3 times. Something like this:

Spread by value google sheets

Finally, what if we want to evenly assign our list of cat, dog and goat to another list? Imagine we have a bunch of people wanting to pet an animal. We need to evenly assign our animals to the list of people.

Spread by range google sheets

There is no real easy approach to do these things with the standard Google Sheets Formulas (Known as Functions in Google Sheets). Fortunately, we can create custom functions with Google Apps Script to do the job.

In this post, I will go over the 3 custom functions that I created to solve these three problems. I’ll then provide a quick-use guide if you just want to dive in and get started using them. Finally, I go over some of the interesting parts of the Google Apps Script code for those of you who wish to learn more about creating your own custom functions.

Continue reading “Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times”

Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee

Google Apps Script: DriveApp, PropertiesService, SpreadsheetApp, GmailApp, SlidesApp. Google Slides, Google Sheets.

Have you ever wondered how people create certificates en mass and send them out automatically? You are in the right place.

In this tutorial, we are going to:

  1. Create Certificates of Attendance for multiple attendees using Google Slides and a list of attendees in Google Sheets.
  2. Send those certificates as an attached PDF to the attendees.

We’ll set it up so it is super user-friendly with a handy menu in your Google Slide template so that all you have to do is to update your Google Sheet of names each time you run the course and then click a few buttons.

Google Apps Script Menu Items in Google Slides

Also, we will run an example so you can see how it all works and what you need to do to set it up.

For the coders out there, I think I have documented the Google Apps Script code enough for you to figure out how to quickly implement your own project. However, I have also added a smalls discussion of some parts of the code at the end.

This is a standalone tutorial. However, it draws from two main tutorials if you want to explore those first (Though it is not essential):

Let’s get cracking.

Continue reading “Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee”

Google Sheets Beginners: Conditional Formatting (09)

Google Sheets: Conditional Formatting

Conditional formatting is a powerful tool that helps you to highlight cells that meet a certain value. You can format cells, that contains:

  • date ranges
  • Number ranges or specific numbers
  • Exact text or partial text

Once you have identified your condition you can change the cell’s background colour, font-weight or font colour.

You can access conditional formatting in Google Sheets by going to Format > Conditional formatting. Make sure you select the range you want to format first, though.

In this video tutorial we will cover the basics of Conditional Formatting in Google Sheet:

  1. What is conditional formatting and how to use it.
  2. Basics of conditional formatting.
  3. Example 1: Conditional Formatting Grade Ranges 0:23
  4. Example 2: Conditional Formatting empty cells or cell only containing values. 4:36
  5. Example 3: Conditional Formatting text that contains certain words or letters. 6:06
Watch this video on YouTube.

If you are keen to follow along here is a link to the Google Sheet I was working in:

Unformatted template to follow along with the video.

Simply, go to File > Make a copy to have your own copy of the file.

If you want to create custom conditions that are not in the list you really should check out this tutorial:

Google Sheets: Conditional Formatting with Custom Formula

If you want to check out more on my Google Sheets Beginners course you can check the playlist out here:

Google Sheets: Beginners

Want a solid step-by-step course to become a pro at Google Sheets? Udemy has some professional courses that will turn you into an admin ninja!

I’m a huge fan of Justin Mares, Mastering Google Sheets course. Sign up today*

 

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

~Yagi

Create a Student Certificate of Attendance with Google Slides and Export it as a PDF or Print it

Google Slides – Beginners

I always enjoy finding new ways to create documents using the Google Suite. One project I have recently worked on has been to build a Certificate of Attendance template that I can update for non-grades courses or conferences.

Why do this using a GSuite package?

Well, 1, if you haven’t noticed, I’m kinda into Google stuff and, 2, I can automate quickly using Google Apps Script and a list of students in Google Sheets so that I can produce all my student’s certificates all at once and automatically convert them to PDF.

If you are new to Google Slides, no problem. We will keep this basic. If you are a pro, skim through for some hot tips and stay tuned for a more technical follow up.

Continue reading “Create a Student Certificate of Attendance with Google Slides and Export it as a PDF or Print it”