Survey Google Docs integration - Response custom PDF generator

This guide shows you how to generate a custom Google Docs file for every received response and send an email notification with a link to that document and a generated PDF.

You need a Responsly account and a Google account to complete this integration.

Part 1 – Integrate with Google Sheets

1. Create your Responsly form with all the questions whose answers you want to include in the document.
2. Go to Connect → Integrations and activate the Google Sheets integration (see the full Google Sheets integration article if needed).
3. Submit a few test responses and confirm that every new response creates a new row in the connected Google Sheet.

Part 2 – Create a Google Docs template

1. In Google Docs, create a new document that will serve as your template.
2. Wherever you want to insert data from the spreadsheet, add placeholders like {{COLUMN1}}, {{COLUMN2}}, etc.
3. The number (1, 2, 3, …) should match the column position in the sheet (first column → {{COLUMN1}}, second → {{COLUMN2}}, and so on).

When the script runs, these placeholders will be replaced with actual values from each response row.

Part 3 – Generate Google Docs based on responses and email a PDF

In your connected Google Sheet:

1. Go to Extensions → Apps Script.
2. Delete the default function and paste the script below.
3. This script will monitor the sheet for new rows, create a Google Docs document from the row data, turn it into a PDF and send it by email.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Start', 'startTrigger')
      .addItem('Stop', 'stopTrigger')
      .addToUi();
}

function startTrigger() {
  ScriptApp.newTrigger('checkNewRow')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onChange()
    .create();
  SpreadsheetApp.getUi().alert('Trigger has been started!');
  
  // Initialize the row count
  PropertiesService.getScriptProperties().setProperty('ROW_COUNT', SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getLastRow());
}

function stopTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  for (const trigger of triggers) {
    ScriptApp.deleteTrigger(trigger);
  }
  SpreadsheetApp.getUi().alert('Trigger has been stopped!');
}

function checkNewRow(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var previousRowCount = parseInt(PropertiesService.getScriptProperties().getProperty('ROW_COUNT'), 10);
  var currentRowCount = sheet.getLastRow();

  // Check if a new row has been added
  if (currentRowCount > previousRowCount) {
    // Get data of the new row
    var newRowData = sheet.getRange(currentRowCount, 1, 1, sheet.getLastColumn()).getValues()[0];
    
    // Copy the template document
    var templateId = 'TEMPLATE_DOC_ID'; // Replace with your template document ID - you can get it from URL https://docs.google.com/document/d/TEMPLATE_DOC_ID/edit
    var docId = DriveApp.getFileById(templateId).makeCopy('Generated Doc from Row ' + currentRowCount).getId();
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();
    
    // Replace placeholders with actual data
    for (var i = 0; i < newRowData.length; i++) {
      body.replaceText('{{COLUMN' + (i + 1) + '}}', newRowData[i]);
    }
    
    doc.saveAndClose();
    
    var docUrl = doc.getUrl();
    
    // Email the document
    var emailAddress = 'recipient@email.com'; // Change this to the recipient's email
    var subject = 'New Google Doc from Row ' + currentRowCount;
    var message = 'A new Google Docs document has been created from the data in row ' + currentRowCount + ' of the Google Sheet. Doc url ' + docUrl;
    
    MailApp.sendEmail({
      to: emailAddress,
      subject: subject,
      body: message,
      attachments: [doc.getAs(MimeType.PDF)],
      name: 'Custom Sender Name'  // Set the sender's name here
    });

    // Update the row count
    PropertiesService.getScriptProperties().setProperty('ROW_COUNT', currentRowCount);
  }
}

1. Customize the script – important values to change:

   • Template document ID: in the line var templateId = 'TEMPLATE_DOC_ID'; replace 'TEMPLATE_DOC_ID' with the ID of your own Google Docs template. You can copy the ID from the document URL, e.g. https://docs.google.com/document/d/THIS_IS_THE_ID/edit.
   • Recipient email address: in the line var emailAddress = 'recipient@email.com'; set the email address that should receive the document and PDF.
   • Optional – names and subject: you can adjust the generated document name in makeCopy('Generated Doc from Row ' + currentRowCount), the email subject in var subject = 'New Google Doc from Row ' + currentRowCount; and the sender name in name: 'Custom Sender Name'.

2. Save and authorize:

   • Save the script file.
   • Click the disk icon or press Ctrl+S / Cmd+S to save.
   • Authorize the script to access your Google Sheets, Docs and Gmail account when prompted.

3. Test the script:

   • Manually add a new row to your Google Sheet and see if a Google Docs document is generated and emailed.

4. Optional – use the custom menu for trigger management:

   • The script includes functions to start and stop the trigger via a custom menu in Google Sheets. To use this, reload your Google Sheet, go to the new Custom Menu and click Start to enable the trigger, or Stop to disable it.

This script sets up a trigger to run the checkNewRow function whenever the form gets an answer and it is added to the Google Sheet, generating a Google Docs document from the row data and sending it via email.