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.