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 Sheets Shorts: Change the Column and Row Size (0)

Google Sheets: Columns and Rows

If you are new to Google Sheets or spreadsheets in general. This is the post for you.

In this tutorial, we walk through 3 ways to change the size of Columns and Rows. We also throw in some tips on change rows and columns in bulk to increase your workflow, before addressing some pitfalls you might face.

Watch this video on YouTube.

Here is a link to the Sheet containing the character matrix.

Resizing Columns and Rows

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.

 

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”

Count the Number of Selected Days of the Week in Google Sheets

Google Sheets: DAYS, NETWORKDAYS.INTL, NETWORKDAYS, VALUE

A very common spreadsheet task is to get the total count of the days between two dates. We might need this information to:

  • Find the number of days absent of staff or students.
  • Find the total days worked or attending a class.

Some times we need to avoid including weekends, national holidays or certain days of the week in our total account. Fortunately for us, Google Sheets has some custom functions to help us easily work this all out.

This tutorial will cover how to find the total number of days over a range minus any selected days you don’t want to be included. We’ll also cover how to get the total count of specific days of the week over a range.

NOTE! I have the dates set to the British standard e.g. 5 May 2020. You can change the formatting around to any gregorian date format for these examples.

Let’s get cracking!

Continue reading “Count the Number of Selected Days of the Week in Google Sheets”

Google Apps Script: Upload grades into a Google Classroom Coursework Assignment

Google Classroom, Google Sheets, Google Apps Script: Classroom API, SpreadsheetApp, map, forEach

At the date of writing this the world is in the midst of a worldwide pandemic – the Corona Virus.

For educators and their Igors administrators like me, it has been an incredibly busy couple of months.

Many of us have quickly sort to use online resources and tools to keep in contact with our students and take a crash course into the world of online teaching.

Google Classroom has become one of the darlings of the teaching world that has come out of this crisis. Its shallow learning curve and attractive UI made it an obvious choice for most educators. Oh, and I am sure the fact that it is free helped too.

For all of Google Classroom’s ease of use, I still feel the application is in its fledgling stages and does have a few limitations that, I’m sure, Google is constantly working on and improving.

One of the limitations is that you cannot simply upload grades from, say Google Sheets or another assessment tool. Well…not directly in the Google Classroom platform, yet.

However, you can still upload grades from an external source using Google Apps Script. You could use GAS to upload a whole courses grades from a Google Sheet. You could even use GAS to upload grades on a trigger from another assessment’s API into your Google Classroom.

In this tutorial, I am going to run you through the basics of uploading grades into Google Classroom using Google Apps Script with the Classroom API.

NOTE! This tutorial is for a wide audience. Feel free to read the post all the way through, or take what you need and get cracking!

Continue reading “Google Apps Script: Upload grades into a Google Classroom Coursework Assignment”

Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)

Google Sheets: ROUND, SPLIT, COUNTA

Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was only a way to count the number of sentences in a paragraph in a cell in Google Sheets!?

Well, you’re a bit weird, but I guess you are in the right place.

In this short tutorial, we will show you how to get the total count of words in a cell and the total count of sentences in a cell in Google Sheets. Hey, we will even throw in a how-to on getting the average sentence length in a paragraph, because we are nice like that – and we are a bit weird too.

We’ll give you the formulas for you to jump off on your own project straight away. We’ll also give you a detailed explanation of the formulas so you can figure out how it all works using our example.

Navigate through the table of contents to get to what you need or read on!

Continue reading “Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph)”