Lesson 15: Generating Documents

15.1 Documents from Data

Two jobs have been on Maya's list since the club started. At the end of the season, members who volunteered at least 10 hours get a certificate. And every week, someone writes up the harvest for the club's newsletter. Both mean copying numbers out of the spreadsheet into a document by hand, which is slow and easy to get wrong.

Apps Script can do both, with two more services: DriveApp, for files and folders in Google Drive, and DocumentApp, for the contents of Google Docs.

15.2 Certificates from a Template

The easiest way to make many similar documents is to design one by hand as a template, with placeholders where the details go, and let a script make a copy for each person and fill in the placeholders. You design the certificate in Google Docs, with fonts, a border and the club's name, and type the placeholders {{First Name}}, {{Last Name}} and {{Hours}} where the details belong. The double curly braces aren't special to Google Docs. They just make the placeholders easy to see, and unlikely to appear anywhere else in the document by accident.

Here's the request, in a new chat with your learner profile from Lesson 14. Email, Calendar, and Dates and the garden context:

Read. The assistant listed the new methods first, which makes the code easier to follow. Take it in two parts.

createVolunteerCertificates is familiar: read the Members sheet, loop over the rows, and call makeCertificate for each member with enough hours.

makeCertificate does the new work, one step per line:

1. Get the Certificates folder and the template file from Drive, by their IDs.
2. makeCopy copies the template into the folder, named after the member, and getId gets the copy's ID.
3. DocumentApp.openById opens the copy for editing, and getBody gets its text.
4. replaceText swaps each placeholder for the member's details. String(hours) turns the number into text, because replaceText works with text.
5. saveAndClose saves the changes. This matters: without it, the PDF in the next step could be made from the unchanged copy.
6. getAs("application/pdf") makes a PDF version of the copy, and createFile saves it in the folder.
7. setTrashed(true) moves the Google Docs copy to the trash, so only the PDF is left.

15.2.1 IDs and settings

The first three lines are new in another way: they're written outside any function. A variable declared at the top of the file, outside all functions, can be used by every function in the file. The assistant used this for settings: the template's ID, the folder's ID, and the minimum hours. Written in capitals, as you saw in Lesson 12. Objects and JSON, they tell you these are values to set, not values that change as the script runs. If the club changes the threshold to 12 hours, there's one place to change it.

Every file and folder in Google Drive has an ID, a long string of letters and numbers that never changes, even if the file is renamed or moved. You'll find it in the address bar when the file is open: in a document's address, it's the part between /d/ and the next /, and in a folder's address, it's the part after /folders/. Scripts use IDs because names aren't unique: you could have three files named "Certificate," but only one with a given ID.

15.2.2 Before you run it

• Test with one. The assistant's testMakeCertificate makes a single certificate for a made-up person. Run it, open the PDF, and check it before making all of them. (The profile's dry-run rule mentions only email and calendars, so the assistant didn't write a dry run for this. It offered a one-certificate test instead, which is the right idea.)
• Read the permission screen. This script asks for access to your Google Drive and your documents, including permission to delete files, since it trashes the copies. That matches what it does, and it's also a reason to be sure it only trashes what it should: copyFile, never templateFile.
• Running it twice makes duplicates. Each run makes a new PDF for every qualifying member. If you run it again after fixing a typo in the template, delete the first batch.

For the book's members, six people qualify: Maya, Ava, Dev, Farah, Hana and Keisha.

15.3 Building a Document from Scratch

The newsletter doesn't need a template. Its contents change every week, so the script builds the document itself. Continuing the chat:

Read. createNewsletter reads like a table of contents: get the data, total it by crop, create the document, then add a heading, a paragraph and a table. Each step is its own function:

• getCropTotals is the counting-by-name pattern from Lesson 12. Objects and JSON, adding kilograms instead of 1.
• addHeading appends a paragraph and makes it a heading. DocumentApp.ParagraphHeading.HEADING1 is a built-in constant that means "Heading 1," the same style you'd pick from the Docs toolbar.
• addHarvestTable builds an array of arrays, a header row and then one row per crop, and appendTable turns it into a table. The totals go through toFixed(1), which also turns them into strings, as appendTable needs.

Two small things to know when you run it. The new document is saved in the top level of your Drive, called My Drive, and each run creates another document named Garden News. And a new Google Doc starts with one empty paragraph, so the heading, appended after it, comes out one line down from the top. It's harmless, and your assistant can show you how to remove it.

The assistant decided this was "safe to run for real" because it doesn't send email or touch a calendar. That's the letter of your profile's rule. Creating a document is easy to undo, since you can delete it, so here it's a reasonable call. But the rule should cover files too, and you'll update it at the end of this lesson.

15.3.1 Testing the part you can test

Documents can't be created on this page, but look at how the newsletter script is organized. getCropTotals takes an array of rows and returns an object. It doesn't create anything or talk to Drive, so you can test it here, on the club's Harvests sheet:

{"sheetName": "Harvests", "rows": 36, "columns": 8, "data": [{"range": "A1:E33", "values": [["Date", "Bed ID", "Crop", "Kilograms", "Logged By"], ["2027-04-18", "B2", "Radish", 1.2, "elena.rossi@example.com"], ["2027-05-13", "B4", "Spinach", 2.4, "ben.okafor@example.com"], ["2027-05-15", "B2", "Lettuce", 3.9, "hana.kim@example.com"], ["2027-05-21", "B2", "Lettuce", 2.5, "dev.patel@example.com"], ["2027-05-21", "B4", "Spinach", 0.6, "gabe.martinez@example.com"], ["2027-05-28", "B4", "Kale", 2.2, "isaac.cohen@example.com"], ["2027-05-31", "B2", "Lettuce", 2.4, "gabe.martinez@example.com"], ["2027-06-05", "B4", "Kale", 0.6, "keisha.brown@example.com"], ["2027-06-09", "B1", "Basil", 2, "gabe.martinez@example.com"], ["2027-06-11", "B4", "Kale", 1.8, "dev.patel@example.com"], ["2027-06-13", "B3", "Zucchini", 0.5, "elena.rossi@example.com"], ["2027-06-13", "B6", "Carrot", 1.3, "gabe.martinez@example.com"], ["2027-06-18", "B1", "Basil", 1.3, "cam.nguyen@example.com"], ["2027-06-21", "B3", "Zucchini", 3.2, "dev.patel@example.com"], ["2027-06-22", "B3", "Bean", 2.6, "elena.rossi@example.com"], ["2027-06-24", "B5", "Tomato", 3.1, "ben.okafor@example.com"], ["2027-06-29", "B3", "Bean", 3.8, "dev.patel@example.com"], ["2027-06-30", "B3", "Zucchini", 0.6, "maya.thompson@example.com"], ["2027-07-01", "B1", "Tomato", 2.6, "dev.patel@example.com"], ["2027-07-01", "B5", "Tomato", 0.9, "ava.lopez@example.com"], ["2027-07-01", "B8", "Cucumber", 2.6, "isaac.cohen@example.com"], ["2027-07-03", "B5", "Pepper", 2, "elena.rossi@example.com"], ["2027-07-05", "B7", "Mint", 3.6, "farah.haddad@example.com"], ["2027-07-07", "B3", "Bean", 3.7, "hana.kim@example.com"], ["2027-07-08", "B1", "Tomato", 2.2, "elena.rossi@example.com"], ["2027-07-09", "B7", "Mint", 2.4, "keisha.brown@example.com"], ["2027-07-10", "B8", "Cucumber", 2.9, "maya.thompson@example.com"], ["2027-07-13", "B1", "Tomato", 1.4, "gabe.martinez@example.com"], ["2027-07-16", "B8", "Cucumber", 3.9, "farah.haddad@example.com"], ["2027-07-17", "B7", "Mint", 2.5, "ben.okafor@example.com"], ["2027-08-10", "B8", "Squash", 1.9, "gabe.martinez@example.com"], ["2027-08-16", "B8", "Squash", 2.3, "dev.patel@example.com"]]}], "formats": [{"range": "A1:E1", "fontWeight": "bold"}, {"range": "A2:A33", "numberFormat": "@"}]}

function testGetCropTotals() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const harvestSheet = spreadsheet.getSheetByName("Harvests")
  const harvestRange = harvestSheet.getDataRange()
  const harvestData = harvestRange.getValues()
  const cropTotals = getCropTotals(harvestData)
  console.log(JSON.stringify(cropTotals, null, 2))
}

// Adds up kilograms harvested for each crop, using an object
function getCropTotals(harvestData) {
  const totals = {}

  // Loop over the rows, starting at 1 to skip the header row
  for (let i = 1; i < harvestData.length; i++) {
    const crop = harvestData[i][2]
    const kilograms = harvestData[i][3]

    // If we haven't seen this crop yet, start it at 0
    if (totals[crop] === undefined) {
      totals[crop] = 0
    }

    totals[crop] = totals[crop] + kilograms
  }

  return totals
}

You'll see some totals with a long tail of digits, like the ones in Lesson 10. College Community Garden: Case setup: College Community Garden: Case Setup, which is why the newsletter's table rounds them.

This is a design habit worth copying. Keep the calculating separate from the doing. The calculating part is where most bugs are, and it can be tested anywhere, quickly and safely. The part that creates files or sends messages stays small, and you test it once, carefully.

15.4 Your Learner Profile

The dry-run rule is replaced with a broader one that covers files, and allows a one-item test as an alternative, which is often more useful for documents than a log.

15.5 Summary

DriveApp works with files and folders, and DocumentApp with the contents of Google Docs. A template with placeholders like {{First Name}} turns into a finished document when a script copies it, fills it in with replaceText, saves it with saveAndClose, and can save it as a PDF with getAs. A new document can also be built from nothing with appendParagraph, setHeading and appendTable. Scripts find files by their IDs, which appear in their web addresses, and settings like IDs are best kept as constants at the top of the file. Keeping the calculating separate from the file-creating lets you test the part most likely to have bugs without creating anything. Next, you'll write your own spreadsheet functions, the kind you type into a cell.