Intermediate · I3

Google Docs with DocumentApp

Reports, letters and certificates are the same document with different details. In this chapter scripts write Docs from scratch, copy sheet data into them, clean them up, and fill a template once for every row of a sheet.

A document is a tree

Sheets are grids. Docs are a tree of elements, each inside another:

Level	Get it with	Contains
Document	`DocumentApp.create(name)`, `getActiveDocument()`, `openById(id)`	Body, header, footer
Body	`doc.getBody()`	Paragraphs, list items, tables, images
Paragraph / ListItem / Table	`body.getParagraphs()`, `body.getChild(i)`	Text
Text	`paragraph.editAsText()`	Characters you can style one by one

A standalone or Sheet-bound script uses create or openById. A script bound to a Doc (open the Doc → Extensions → Apps Script) can use getActiveDocument().

Finding a file's ID

It's the long code in the URL between /d/ and /edit: docs.google.com/document/d/1aBcD…xyz/edit. Every Google file and folder has one.

Next: write a Doc →

Build a report

Code.gs

function createReport() {
  const doc = DocumentApp.create('Class report – M.5/3');
  const body = doc.getBody();

  body.appendParagraph('Class report').setHeading(DocumentApp.ParagraphHeading.TITLE);
  body.appendParagraph('Computing, term 2').setHeading(DocumentApp.ParagraphHeading.SUBTITLE);

  body.appendParagraph('Summary').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph('The class worked hard this term. Three projects were finished on time.');

  body.appendParagraph('Highlights').setHeading(DocumentApp.ParagraphHeading.HEADING2);
  body.appendListItem('Built a web page about Thai festivals');
  body.appendListItem('Learned loops and arrays');
  body.appendListItem('Presented to M.4 students');

  body.appendHorizontalRule();
  const note = body.appendParagraph('Prepared by the Computing department');
  note.setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
  note.editAsText().setItalic(true).setForegroundColor('#5f6368');

  console.log(doc.getUrl());
}

Execution log

https://docs.google.com/document/d/1Xy…/edit

Open the link. You get a titled, structured document with a bulleted list and a right-aligned italic footnote.

Body method	Adds
`appendParagraph(text)`	A paragraph at the end
`insertParagraph(index, text)`	A paragraph at a position (0 = top)
`appendListItem(text)`	A bullet; `.setGlyphType(DocumentApp.GlyphType.NUMBER)` for 1, 2, 3
`appendTable(array2d)`	A table from a 2D array of strings
`appendHorizontalRule()` / `appendPageBreak()`	A line / a new page
`appendImage(blob)`	An image from Drive or the web

Headings use DocumentApp.ParagraphHeading: TITLE, SUBTITLE, HEADING1 to HEADING6 and NORMAL. Using real headings (not just big bold text) gives the document an outline and a working table of contents.

Next: sheet to Doc →

Copy a sheet into a table

Run this in the Marks scripts project:

Code.gs

// Copy the Marks tab into a Google Doc as a table.
function marksToDoc() {
  const data = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Marks')
    .getDataRange()
    .getDisplayValues();            // text, exactly as the sheet shows it

  const doc = DocumentApp.create('Marks table');
  const body = doc.getBody();
  body.appendParagraph('Marks').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  const table = body.appendTable(data);

  // Bold header row.
  const header = table.getRow(0);
  for (let c = 0; c < header.getNumCells(); c++) {
    header.getCell(c).editAsText().setBold(true);
    header.getCell(c).setBackgroundColor('#e8f0fe');
  }
  console.log(doc.getUrl());
}

appendTable wants a 2D array of strings, which is exactly what getDisplayValues() gives. Then the header row is styled cell by cell.

Reading a document

Open any Doc with some headings, make a Doc-bound project, and run:

Code.gs

// Run from a Doc-bound project (Extensions → Apps Script inside a Doc).
function listParagraphs() {
  const body = DocumentApp.getActiveDocument().getBody();
  const paras = body.getParagraphs();
  paras.forEach((p, i) => {
    const text = p.getText();
    if (text) console.log(i, p.getHeading(), text.slice(0, 40));
  });
  console.log(`Words: ${body.getText().split(/\s+/).filter(Boolean).length}`);
}

Execution log

0 TITLE Class report
2 HEADING1 Summary
3 NORMAL The class worked hard this term. Three
Words: 58

Next: find and replace →

Search and change text

Code.gs

function tidyDoc() {
  const body = DocumentApp.getActiveDocument().getBody();

  // replaceText uses a regular expression written as a string.
  body.replaceText('colour', 'color');
  body.replaceText('\s{2,}', ' ');              // squeeze double spaces
  body.replaceText('\[cite:\s*\d+\]', '');   // remove [cite: 123] markers
}

function highlightWord() {
  const body = DocumentApp.getActiveDocument().getBody();
  let found = body.findText('TODO');
  while (found) {
    const text = found.getElement().asText();
    text.setBackgroundColor(found.getStartOffset(), found.getEndOffsetInclusive(), '#fff475');
    found = body.findText('TODO', found);        // continue after the last match
  }
}

replaceText(pattern, replacement) changes every match in the body. The pattern is a regular expression in a string, so backslashes are doubled: '\\s' means "a space character".
findText(pattern) returns the first match (or null). Pass the previous result as the second argument to get the next one. The match knows its element and start and end offsets, so you can style just those characters.

Special characters

. * + ? ( ) [ ] { } \ ^ $ | mean something in a pattern. To match a real full stop or bracket, put \\ before it. For a literal placeholder like {{name}} the braces are safe because they don't form a valid repeat.

Next: templates →

One template, many documents

This is the most useful Docs pattern: a designed template with placeholders, and a script that makes one copy per row.

Make a Doc called Certificate template. Design it however you like and type {{name}}, {{room}} and {{date}} where the details go.
Make a Drive folder called Certificates.
Copy both IDs from their URLs into the two constants at the top of the script.
Run makeCertificates from the Marks project.

Code.gs

// One certificate per row of the sheet, made from a template Doc.
// The template contains {{name}}, {{room}} and {{date}} where values go.
const TEMPLATE_ID = 'PASTE_THE_TEMPLATE_DOC_ID_HERE';
const FOLDER_ID = 'PASTE_THE_OUTPUT_FOLDER_ID_HERE';

function makeCertificates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks');
  const [header, ...rows] = sheet.getDataRange().getValues();
  const template = DriveApp.getFileById(TEMPLATE_ID);
  const folder = DriveApp.getFolderById(FOLDER_ID);
  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'd MMMM yyyy');

  rows.forEach(([name, room, mark]) => {
    if (mark < 80) return;                                   // only for A grades
    const copy = template.makeCopy(`Certificate – ${name}`, folder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    body.replaceText('{{name}}', name);
    body.replaceText('{{room}}', room);
    body.replaceText('{{date}}', today);
    doc.saveAndClose();
  });
}

Every student with 80 or more gets their own named Doc in the folder. The template is never changed, so you can run it again next term. saveAndClose() makes sure the edits are written before the next copy.

Then make PDFs

Code.gs

// Turn a Doc into a PDF file in the same folder.
function docToPdf(docId) {
  const file = DriveApp.getFileById(docId);
  const pdf = file.getAs('application/pdf');
  const folder = file.getParents().next();
  return folder.createFile(pdf).setName(file.getName() + '.pdf');
}

Call docToPdf(doc.getId()) just after saveAndClose() to get a PDF next to each Doc, ready to email in the next chapter.

Next: practice →

Practice

1. Meeting agenda Easy

Create a Doc with a heading, today's date written out in full, and a numbered list of four agenda items.

Show a solution

Code.gs

function meetingAgenda() {
  const doc = DocumentApp.create('Meeting agenda');
  const body = doc.getBody();
  body.appendParagraph('Department meeting').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  body.appendParagraph(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'EEEE d MMMM yyyy'));
  ['Welcome', 'Exam timetable', 'Sports day', 'Any other business'].forEach((item) => {
    body.appendListItem(item).setGlyphType(DocumentApp.GlyphType.NUMBER);
  });
  console.log(doc.getUrl());
}

2. Exercise headings Medium

In a worksheet Doc, turn every paragraph that starts with "Exercise 1:", "Exercise 2:"… into Heading 2.

Show a solution

Code.gs

// Make every paragraph that starts with "Exercise 1:", "Exercise 2:"… a Heading 2.
function exerciseHeadings() {
  const body = DocumentApp.getActiveDocument().getBody();
  body.getParagraphs().forEach((p) => {
    if (/^Exercise\s+\d+:/i.test(p.getText())) {
      p.setHeading(DocumentApp.ParagraphHeading.HEADING2);
    }
  });
}

3. Contents list Medium

Insert "Contents" at the top of the Doc, followed by a bullet for every Heading 1 and Heading 2.

Show a solution

Code.gs

// Put a list of all headings at the top of the document.
function headingIndex() {
  const body = DocumentApp.getActiveDocument().getBody();
  const headings = body
    .getParagraphs()
    .filter((p) => p.getHeading() === DocumentApp.ParagraphHeading.HEADING1 ||
                   p.getHeading() === DocumentApp.ParagraphHeading.HEADING2)
    .map((p) => p.getText());

  body.insertParagraph(0, 'Contents').setHeading(DocumentApp.ParagraphHeading.HEADING1);
  headings.forEach((h, i) => body.insertListItem(1 + i, h));
}

4. Report cards Challenge

Adapt makeCertificates into report cards: add {{mark}} and {{grade}} to the template, use your THAI_GRADE function from B5, make one for every student, and save each as a PDF too.

Next: quiz →

Check yourself

1. Which object holds the paragraphs, lists and tables of a document?

2. What does appendParagraph return?

3. body.replaceText('a.c', 'X') — what does it replace?

4. To fill a template for 30 students you should…

5. Why use getDisplayValues() when copying a sheet into a Doc?

6. findText returns null. What does that mean?