Generating Docs from Google Sheets

Google Sheets allows for App Scripts to be ran which can provide robust, custom automatic reporting. The example outlined below may require some customization of code, but that could be made more efficient with some effort. Turning the App Script into an Add-on also allows for sharing report creation with others. An example could be running a report of expenditures from a Sheet filled with expenses. It’s possible to run a report from a Sheet so that Docs are automatically generated.

About

I was asked by my PTO to help organize a parent ran, lunch hour, fun time with the students. Planned for one day in each of 4 weeks. Parents sign up to teach students and students sign up for classes to attend during their lunch hour to stay out of the cold. Previously, the students signing up and attending one of 20 fun classes at lunch was done within Microsoft Access and was entered into a system by a single individual. While we did have all of those Access files and I’m well experienced with Access, the actual data entry was better suited within Google’s ecosystem. However there is no clear way to create reports for each student, teacher, and parent’s schedules once the data was entered. We needed to have the ability to generate reports based off of a Sheet.

Making Templates

The solution begins with creating template Docs that contain delimited words which would be replaced by cells from the Sheet. Using a simple system of delimiters like ## to define the variable words. Using left and right delimiters of two octothorpe, I created the necessary formats of the Docs that are needed.
The class schedule I need contains Student data with a page per student. This allows each student to be able to have his or her schedule in there bag or for their parents to see.

It’s best to have unique and repetitive naming of the variable words. In my case, I use all uppercase character that contain no spaces and are prepended with a generic naming scope. Student data begins with STUDENT_. I also have a possibility of N sessions per student where the student may have 0 to 4 classes. I called these SESSION_N_ data to easier find and replace. Proper names make the code much easier to deal with; take care in using naming conventions.

Creating Reports using the Template

Creating reports begins with writing an App Script within the Sheet. App Scripts can be created from within your sheet by finding the Tools menus and choosing App Script Editor.
The tools menu showing the script editor link
Once in the editor, you can create methods that will grab the template, create a new document, run through your sheets rows and add to a new document the template filled with the data. This script does quite a bit.

At the top of the file, setting up a mapping to the Sheets cells. Doing this allows to easily update where the variable names in the Doc template matches the key in SPREADSHEET_MAPPING and the value is the coulmn in the Sheet’s row as we loop through each row.

1
2
3
4
5
6
7
8
9
/**
 * SPREADSHEET_MAPPING
 * Contains Template variables and the columns 
 */
var SPREADSHEET_MAPPING = {
  HAS_FORM: 6,
  FIRSTNAME_CELL: 1,
  LASTNAME_CELL: 0,
  // ...

The onOpen method taps into the script editors ability to add a menu item that can be then used to run the app.

Appending to a Doc requires the code to handle what exactly is being appended. It’s best to append a single ‘paragragh’ of text or a ‘table’. This code, so keep it simple, ignores the need for handling other types.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
function appendElementToDoc(doc, object) {
  var type = object.getType(); // need to handle different types para, table etc differently
  var element = object;
  element = object.copy();
  
  Logger.log('Content ' + element.getText());
  if (type == "PARAGRAPH") {
    doc.appendParagraph(element);
  } else if (type == "TABLE") {
    doc.appendTable(element);
  } else {
    Logger.log('erm ' + type);
  }
}

Getting Sheet data is very simplistic. Grab the active Sheet and the active tab, and copy cells based on 2nd row, 1st column through last row and last column. It would make sense to make the beginning variables.

1
2
3
4
5
6
function getSpreadsheetData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
  return data;
}

Creating a document and adding a document to a folder do the bare minimum in script editor API code to move along.

Generating a student schedule; the magic. Some setup first at the top. Again it would be rudimentary to make some of this variables. We create a new document called ‘Studen Schedules’ with a timestamp here. Then we add this document to the foleder that is a variable; this is relative to the current script that is running (the Sheet you ran it from).

1
2
3
4
5
6
7
8
9
10
11
12
Logger.log('>>>');
  const templateid = "1hiopIDgPiQaBQi13QPbPjka5RgE9dtAK-CBumzSi03Q";
  const GENERATED_DATE = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");  
  const SESSION_1_DATE ='Thursday, February 1st';
  const SESSION_2_DATE ='Wednesday, February 7th';
  const SESSION_3_DATE ='Tuesday, February 13th';
  const SESSION_4_DATE ='Thursday, February 22nd';
  
  const data = getSpreadsheetData();
  const newDoc = createDocument("Student Schedules - " + Utilities.formatDate(new Date(), "GMT", "HH:mm dd/MM/yyyy"));
  addDocumentToFolder(newDoc,FOLDER_NAME);
  var content = '';

Now the code must Clone the template and create a new document of the template. For performance, the code loads the new document into a variable memory.

1
2
3
var docid = DriveApp.getFileById(templateid).makeCopy().getId();
var doc = DocumentApp.openById(docid);
var docbody = doc.getActiveSection();

Next is the looping through the Sheet‘s rows and pulling the content from each cell and replacing the items in the template (which we make another copy of on each loop).

1
2
3
4
5
6
7
8
9
10
11
12
for (var i in data){
  var row = data[i];
  // do nothing if no data or no lastname
  if(!row || !row[0]) { continue; }
  
  // Ignore students that don't have any signups
  if(row[SPREADSHEET_MAPPING.HAS_FORM] !== 'Yes') { continue; }
  
  // clone the template for injection
  var body = docbody.copy();
  body.replaceText("##STUDENT_NAME##", row[SPREADSHEET_MAPPING.FIRSTNAME_CELL] +' '+ row[SPREADSHEET_MAPPING.LASTNAME_CELL]);
  // ...

Mapping the variable above in setup makes this very non-custom and easy.

1
2
3
4
5
6
7
8
  // quickly loop through and update all mapped variables
  Object.keys(SPREADSHEET_MAPPING).forEach(function(key, index) {
    var value = row[SPREADSHEET_MAPPING[key]];
    if(!value || value.length < 1 || value === "#N/A" || value === "None") {
      value = '-';
    }
    body.replaceText('##'+key+'##',  value); 
});

Uh oh, first major performance issue that happens. It’s very easy and possible to push too much content into the Doc at once. As the code is looping, every hundred rows it will push the content to the document and buffer some more. This is a major performance increase to the code.

1
2
3
4
5
6
7
8
Logger.log("Appending to the main document "+body.getText());
appendToDoc(body, newDoc); // add the filled in template to the students
newDoc.appendPageBreak();
// batch output
if((i % 100) === 0) {
  newDoc.saveAndClose();
  newDoc = DocumentApp.openById(newDocId);
}

Last, close and save the template and let the user know with the ‘toast’ notification.

1
2
3
4
5
doc.saveAndClose();
DriveApp.getFileById(docid).setTrashed(true); // delete temporary template file

Logger.log('<<<');
SpreadsheetApp.getActiveSpreadsheet().toast("Reports have been complied");

Issues Encountered

Folder paths

Folder paths are seemingly easy, but I recommend sticking with the relative paths. The issue comes into fruition when the script is shared and others have much different folder paths and depth.

let, var, const

ES2015 is only partially supported. In fact the script editor is mainly JS 1.6 with some enhancements. Officially only partial support of 1.7 and 1.8 exists.

Performance

It can be tricky to get performance correct. Opening and appending to documents is the most intense. Batching output is highly recommended along with caching the document in memory and making changes there.

Logging errors

Logger works, but it can be hard to find and open. I had to hop between tabs and page refreshes to debug.

Appending content

It’s actually very difficult to take text Nodes and manage them into another document. For simple single elements, like a paragraph and table, it’s easy. Recommended to use a library of code to handle any significant scripting.

Wiping the last paragraph

You can’t remove the last paragraph from document. This means if you ‘copy’ the template, it will error if it’s the last item (as that will essentially remove it!). This is why the code above is ‘cloning’ the template into memory.

Final thoughts

Google App Script is really fun. The power that exists is very nice and it’s almost thrilling to get that kind of control which allows for some really great automation that can occur. I’ll likely continue at some point finding other automation scripts I can run.