Survey Google Docs integration - Response custom PDF generator

This instruction allow you to generate custom Google Docs for every received response and send e-mail notification with link to that document and a generated PDF.

You need Responsly and Google account to complete this integration.

  1. Create your Responsly Form with relevant questions.
  2. Go to Connect → Integrations and activate Google Sheets integration.
  3. Test integration - new row should be added for every new response.
  4. Create a Google Docs Template place {{COLUMN1}}, {{COLUMN2}}, etc. when the data from the response should go.
  5. Create Google Apps Script - To achieve this in Google Workspace, you can use Google Apps Script to automate the process. Here's a step-by-step guide to creating a script that does this:
    1. Open Google Sheets and Create a New Script:
      • Open your Google Sheet.
      • Go to Extensions  > Apps Script .
    2. Write the Apps Script:
      • Replace the default code with the following script. This script will create a Google Docs document from a row of data and send it via 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:
      • Change var dataColumn = 1;  to the column number where new data will be added.
      • Update var emailAddress = '';  with the recipient's email address.
    2. Save and Authorize:
      • Save the script file.
      • Click the disk icon or press Ctrl+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 - Add 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.
        • Use the Stop  option to disable the trigger.

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

Need help or have more questions?

Responsly Employee Experience platform helps us to manage employee satisfaction and communication within our organization.

Alicja Zborowska, Administration Specialist

Red bull
Bayer

We automated the product experience managment process.

KraftHeinz

Managing customer experience is made easy with Responsly.

Danone

Our suppliers are surveyed quickly and efficiently.

Get answers today

It's really easy and we'll help you when needed
Sign up for free
  • No credit card required
  • No time limit on Free plan
Talk to us!