Syncing Google Forms open letter signatories to a Google Docs document

I’ve contributed to open letter campaigns enough times that I finally looked into automating the transfer of signatories from the sheet that backs a Google Forms form to a Google Docs document. The thought of people continually transferring new signatories every time they came in was saddening, it’s what computers are made for!

Here are step-by-step instructions on setting this up from scratch in the Google Docs Editors universe. It should be replicable without scripting skills if you need exactly what I set up and adaptable with light Javascript skills.

The form

Create your form. Here’s a simple example open letter:

We need to add a script to a backing Google Sheets spreadsheet so view the Responses tab and choose Link to Sheets:

I chose “Create a new spreadsheet” but presumably this would work with either option? The name doesn’t matter, I chose the autogenerated one.

The spreadsheet

We need to add a column to manually approve signatories that will be synced to the open letter. This lets us reject fake signatures from the enemy. In the first blank column, add a label, click the column header (“D” for instance), and unselect the first/label cell (⌘-click on macOS), then choose Insert → Checkbox.

Checking this box will mark a form submission as one we want to sync to the open letter document.

The open letter

Create a Google Docs document with the content of the open letter. The script needs a marker for where it should sync the signatures to, I chose a horizontal rule for expediency despite its slight hideousness, inspired parties could improve by searching for “Signed,” or the like.

We need the identifier for this document for the script, you can find it after the /document/d/ part of the URL, for the example I created it’s 1282d7p51FknBk3QRC8hD7GqSG-ay3mD7W5T8_HTM6rc.

The script

Back in the spreadsheet, choose Extensions → Apps Script. It’ll start you with a placeholder myFunction, you can select the entire file and replace it with this but make sure you replace the part between quotes in the first line (the OPEN_LETTER_DOCUMENT_ID variable) with your own document identifier from the URL, as described above.

const OPEN_LETTER_DOCUMENT_ID = "1282d7p51FknBk3QRC8hD7GqSG-ay3mD7W5T8_HTM6rc";

function updateGoogleDoc(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  var doc = DocumentApp.openById(OPEN_LETTER_DOCUMENT_ID);
  var body = doc.getBody();
  
  // Find the position of the horizontal rule
  var hrIndex = -1;
  var elements = body.getNumChildren();
  
  for (var i = 0; i < elements; i++) {
    if (body.getChild(i).getType() === DocumentApp.ElementType.PARAGRAPH) {
      var para = body.getChild(i);
      for (var j = 0; j < para.getNumChildren(); j++) {
        if (para.getChild(j).getType() === DocumentApp.ElementType.HORIZONTAL_RULE) {
          hrIndex = i;
          break;
        }
      }
    }
    if (hrIndex !== -1) break;
  }

  // If no horizontal rule is found, append one
  if (hrIndex === -1) {
    body.appendHorizontalRule();
    hrIndex = body.getNumChildren() - 1;
  }
  
  // Clear everything after the horizontal rule, but keep the last paragraph
  while (body.getNumChildren() > hrIndex + 2) {
    body.removeChild(body.getChild(hrIndex + 1));
  }
  
  // Clear the content of the last paragraph
  if (body.getNumChildren() > hrIndex + 1) {
    body.getChild(hrIndex + 1).asParagraph().clear();
  }
  
  // Add data from the Sheet after the horizontal rule if checkbox in column D (index 3) is checked
  for (var i = 0; i < data.length; i++) {
    if (data[i][3] === true) {
      body.appendParagraph(extractSignature(data[i]));
      body.appendParagraph("");
    }
  }
}

function extractSignature(row) {
  let name = row[1].trim();
  let affiliation = `${row[2]}`.trim();
  let affiliationSuffix = affiliation.length ? `, ${affiliation}` : '';

  return `${name}${affiliationSuffix}`;
}

Make sure to Save project with the anachronistic floppy disk icon.

Wire it up

We need to set up a way to run the script from the spreadsheet. Choose Insert → Drawing and create a text box “Sync” with a background colour, or any button-like thing that will make clear what it does. Then use the contextual menu to “Assign script” and type the name of the function in the script:

updateGoogleDoc

(This would be a great place for UX improvement, Google! Why not have a popup of available functions?)

The first time you try to run it you’ll need to grant permissions, click “Continue” when asked.

It’ll produce another prompt with a scary warning but we can accept the “risk” because we are the developer. Click “Advanced” and then “Go to Untitled project (unsafe)” (or whatever you named the Apps Script project, that’s the default).

The next screen lists the permissions being granted, you can Allow.

You’ll receive an email about this permissions change, safely ignored.

That first click of the button didn’t actually execute, so click it again. It’ll run briefly and should complete.

Success

If all went according to plan the approved signatures will be listed after the horizontal rule! 🥳

Refinement

If your form has more columns your checkbox will be in a different column, so the script needs to be updated to properly see whether a signature is approved. You can change line 46 as needed, the 3 means the 4th column (as counting starts from 0):

if (data[i][3] === true) {

You can also tweak what columns are included in the signature by updating the extractSignature function:

function extractSignature(row) {
  let name = row[1].trim();
  let affiliation = `${row[2]}`.trim();
  let affiliationSuffix = affiliation.length ? `, ${affiliation}` : '';

  return `${name}${affiliationSuffix}`;
}

The high-level description for non-programmers is that the value for column B is extracted and trimmed (stripped of leading and trailing spaces) and stored as name. The same happens for column C as affiliation. affiliationSuffix is defined as , [insert affiliation here] if affiliation has been filled in and an empty string otherwise. This prevents unsightly signature lines like “Name, ” if someone doesn’t provide an affiliation.

The final result is constructed in the return statement.

If you know me and this is all Too Much™ I can probably provide tips or a replacement script for your situation 🤓

Let me know if you try this! 💞