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: How to Add and Remove Editors to A Google Sheet with Code

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.

Share button Google Sheets

Google Sheets Share Edit permisssions

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 Tools > Script editor.

Continue reading “Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code”

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 Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)

Google Apps Script: SpreasheetApp, DocumentApp, DriveApp; Google Sheets, Google Docs

If you have ever worked in LibreOffice or Microsoft Excel you will probably be familiar with the mail merge. Traditionally, mail merge is used to create multiple versions of a document and snail-mail them to someone.

These days, we don’t often use the snail mail approach, but it is a regular occurrence for us to need to produce multiple versions of reports based on a data set usually from a spreadsheet.

In this tutorial, we will create a document merger that will create new Google Documents based on a dataset from a Google Sheet using Google Apps Script.

If you want to quickly jump into your own project with our script, I’ll provide you with a quick-use guide.

Then, we will set up a template for our Google Doc and generate our Google Sheet data (don’t worry, I’ll share the document so you can follow along).

Finally, we will jump into the breakdown of the code for those legends who are learning how to create their own Google Apps Script.

Let’s get started:

Note: As always, take what you need and don’t worry about the rest. 

Continue reading “Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)”

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet

Google Apps Script: DriveApp, Advanced Drive Service, SpreadsheetApp,  DocumentApp Javascript: spread operator, map, regex

Ah, bureaucracy at its finest.  Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. A… p…d…f.

Gee, thanks.

After some prostrations to the great Google Apps Script gods, I had a though.

“Hey, can’t we convert a PDF to a Google Doc with just a click of the button? Surely the great Google Apps Script devs have made it so we can do it programmatically too.”

And you know what? They bloody well did. The big legends.

The Scenario

I’ve just received a bunch of PDFs. The PFDs are all labelled by the class number. Take a look at the files in my Google Drive:

PDFs of sections in Google Drive

Each PDF file contains a list of student IDs that I need to extract and put into a Google Sheet.

PDF file with student IDs

The aim is to have a list of student IDs in column A and their corresponding sections in column B.

As you can see, we have some pretty standard text in the PDF that should be easy for Google to recognise so that we can extract the IDs.

The list of names in the demo sheets were randomly generated by AI!

NOTE! As always, I have tried to create this tutorial for varying levels. Feel free to follow along, or just grab what you need and get stuck into your own project.

If you are playing along, you can find a copy of the PDF files below. Simply add them to your own Drive before you get started:

PDF files by Section.

Continue reading “Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet”