Create impressive personalized reports instantly with Apps Scripts

For many teachers, there’s a very real tension between wanting to provide personalized feedback and attention and wanting to have personal time to avoid burnout. I am attempting to address some of this by greatly reducing the time it takes to provide personalized progress reports with grows and glows as well as information on missing work. By using bulk actions, you can create 25, 50, 100, or more versions of a file in the time it takes to create one.

Once you learn to use the tools in this automation, you will have an automated Swiss army knife for addressing far more than merely progress reports. You can use these strategies for creating graduation certificates, name tags, and even Google Slides if you make the same types of presentations repeatedly.

Additionally, you can power up the process in this automation by adding emails and Microsoft PowerAutomate integration so that progress reports are automatically emailed to parents after you create them.

How does it work?

This automation takes advantage of the power of Google App Scripts to create a Ui element in the Google Sheets menu bar. This simply means that after you’ve added the script to your Google Sheet, creating 90 custom progress reports is just as easy as saving a file.

What do I need?

Google Account

• Google Sheets
• Google Docs

List of students in each of your classes

Step 1: Create your progress report template

Start by creating a Google Doc to be the basis for your progress report.

Surround the fields you will want to replace in {{double curly brackets}}. Make the terms in the curly braces simple so they are easy to reference later. I use all lower case and replace spaces with an underscore. This standardizes the naming conventions so it is easier for to remember terms used and to spot errors later.

Here’s a link to my template if you wish to make a copy and riff off it.

Step 2: Set up your Google Sheet

Use the terms and order of the fields you want to replace on your template as headers for your Google Sheet columns.

I used “student_name”, “grade”, “letter”, “missing_assignment”, “glow”, and “grow” so I named my Google Sheet headers these terms respectively. You can change the heading terms if you really want to, but you need to make sure it easy to understand what goes in each row and in what order.

Here’s a link to my sheet with randomly generated names and data if you want to use something similar.

Step 3: Create a folder to store progress reports in.

You’ll want all the progress reports to be in one place so make a folder in Google Drive. Keep it handy as we’ll reference it in the next step.

Step 4: Write your app script

This is the part that gets a little complicated, but it’s not nearly as difficult as it looks. I’ll go through each part piece by piece.

Select Extensions then Apps Script to open a new script.

Here’s the totality of the script:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create Progress Report', 'createNewGoogleDocs');
  menu.addToUi();
  
} 
function createNewGoogleDocs() {
const month = ["January","February","March","April","May","June","July","August","September","October","November","December"];
const d = new Date();
let monthName = month[d.getMonth()];
let yearName = d.getFullYear();
console.log(yearName)
const googleDocTemplate = DriveApp.getFileById('1Yzy9_YSgl9VhMqk9LvvyL-h8ZJpRXM_dncjOVnJVINU');
const destinationFolder = DriveApp.getFolderById('1HttF6q4UlOfpPB2NG_PMFHa548Cc9lDr');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
const rows = sheet.getDataRange().getValues();
rows.forEach(function(row, index){
  if (index ===0) return;
  if (row[6]) return;
  const copy = googleDocTemplate.makeCopy(`${row[0]} Report ${monthName} ${yearName}`, destinationFolder);
  const doc =DocumentApp.openById(copy.getId());
  const body = doc.getBody();
  body.replaceText(`{{student_name}}`, row[0])
  body.replaceText(`{{grade}}`, row[1]) 
  body.replaceText(`{{letter}}`, row[2])
  body.replaceText(`{{missing_assignment}}`, row[3])
  body.replaceText(`{{glow}}`, row[4])
  body.replaceText(`{{grow}}`, row[5])
  doc.saveAndClose();
  const url = doc.getUrl();
  sheet.getRange(index + 1, 7).setValue(url);
})
}

Let’s start with the first function:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create Progress Report', 'createNewGoogleDocs');
  menu.addToUi();
  
} 

This function adds a ui element to your Google Sheet so that you can run your next function just by pressing AutoFill Docs then “Create Progress Report”.

Upon being clicked, the menu will run the createNewGoogleDocs function.

Here’s how that function works:

To begin, the function opens then I have declared some variables.

function createNewGoogleDocs() {
const month = ["January","February","March","April","May","June","July","August","September","October","November","December"];
const d = new Date();
let monthName = month[d.getMonth()];
let yearName = d.getFullYear();
console.log(yearName)

month is a constant list of all 12 months of the year.

Then I get today’s date and save it as d.

monthName gets the number of the month from d and goes to that numbered item in the month list. This gets the month name in word form and stores it as monthName for later use.

yearName gets the year and stores its value.

The next line is not necessary, but it is a good idea to run with your variable names to make sure everything will come out correctly later. console.log() will display whatever you tell it in the execution pane, and it can be very helpful for debugging.

const googleDocTemplate = DriveApp.getFileById('1Yzy9_YSgl9VhMqk9LvvyL-h8ZJpRXM_dncjOVnJVINU');
const destinationFolder = DriveApp.getFolderById('1HttF6q4UlOfpPB2NG_PMFHa548Cc9lDr');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
const rows = sheet.getDataRange().getValues();

Next we are going to collect all of our materials together in the script. First we retrieve the template by its file id. The url for your template probably looks something like this:

https://docs.google.com/document/d/1Yzy9_YSgl9VhMqk9LvvyL-h8ZJpRXM_dncjOVnJVINU/edit

The file id is the alphabet soup between /d/ and /edit. Copy and paste it into your code. Do the same with your destination folder.

To get the spreadsheet, we simply need to get the sheet the script is running on and specify Sheet1 or whatever you may have chosen to rename it.

rows.forEach(function(row, index){
  if (index ===0) return;
  if (row[6]) return;
  const copy = googleDocTemplate.makeCopy(`${row[0]} Report ${monthName} ${yearName}`, destinationFolder);
  const doc =DocumentApp.openById(copy.getId());
  const body = doc.getBody();
  body.replaceText(`{{student_name}}`, row[0])
  body.replaceText(`{{grade}}`, row[1]) 
  body.replaceText(`{{letter}}`, row[2])
  body.replaceText(`{{missing_assignment}}`, row[3])
  body.replaceText(`{{glow}}`, row[4])
  body.replaceText(`{{grow}}`, row[5])
  doc.saveAndClose();
  const url = doc.getUrl();
  sheet.getRange(index + 1, 7).setValue(url);
})
}

Finally we do the actions we wanted to complete in the first place. For each row in the spreadsheet, we will check to see if there is a url in our last column. If not, the code runs.

First a copy of the template is made. The copy is renamed as Student Name Month Year and placed in the destination folder. ex: Oscar Mayer October 2022.

Next the script focuses on the document body and replaces each field with the data from the spreadsheet. The fields in curly brackets here must exactly match their names on the template. The row numbers correspond to the columns on your sheet left to right starting from 0.

Finally, the code saves and closes the doc and pastes the url onto your sheet so you can easily access it and make individual changes.

Step 5: Deploy your code

The last step is to deploy the script to your sheet.

Deploy the script as a web app.

You will get a very serious looking warning asking if you trust the script. Click advanced then go to progress report. From there, you will be able to allow the script.

Clicking allow will let your script function. You are the developer so it’s essentially asking, “Do you trust yourself?”.

That’s all! You now have a functioning useful progress report automation and the knowledge to deploy these principles and skills in whatever way you choose.