Javascript - Dire Need of Help

[tomoki]

Hey Everyone,

 

I've been using an addon for my Google Sheets called AutoCrat and it basically takes Google Form submissions from Google Sheets and creates a PDF document which is then sent to the respective email address within the Google spreadsheet. The problem is that the addon doesn't work anymore. The time triggers are broken and I'm in need of something that works. I've tried looking into another addon called Yet Another Mail Merge just to try to see if it works but nope. I've also tried recreating my spreadsheet from scratch and have pasted in my formulas. That didn't seem to fix the issue. 

So it comes down to using Javascript.

 

1st problem: I have 1 column of data which is that if the condition is equal to the respective row, it would generate an automated invoice and be sent out. I know there's a lot of code with a simple Google search but none of these seem to have it.

 

2nd problem: I have 2 templates but the code I've added to the bottom of this post only allows for 1 template. 

 

This is Autocrat's script from a few years ago: https://github.com/NewVisionsForPublicSchools/autoCrat-SCRIPT but it's too complex so I looked for some other code I could possibly work with and I found this: http://www.andrewroberts.net/2016/01/google-apps-script-to-create-and-email-a-pdf/

/*
PDF Create - with rename and email
==================================
When you click "Create PDF>Create PDF" this script uses the data from 
the active row to construct a PDF in your GDrive. The value in the 
"File Name" column is used to name the file and - if there is a 
value - it is emailed to the recipient in the "Email" column.
*/

// Config
// ------

// 1. Create a GDoc template and put the ID here
var TEMPLATE_ID = '---- UPDATE ME -----'

// var TEMPLATE_ID = '1wtGEp27HNEVwImeh2as7bRNw-tO4HkwPGcAsTrSNTPc' // Demo template
// Demo script - http://bit.ly/createPDF

// 2. You can specify a name for the new PDF file here, or leave empty to use the 
// name of the template or specify the file name in the sheet
var PDF_FILE_NAME = ''

// 3. If an email address is specified you can email the PDF
var EMAIL_SUBJECT = 'The email subject ---- UPDATE ME -----'
var EMAIL_BODY = 'The email body ------ UPDATE ME ---------'

// Constants
// ---------

// You can pull out specific columns values 
var FILE_NAME_COLUMN_NAME = 'File Name'
var EMAIL_COLUMN_NAME = 'Email'

/**
 * Eventhandler for spreadsheet opening - add a menu.
 */

function onOpen() {

  SpreadsheetApp
    .getUi()
    .createMenu('Create PDF')
    .addItem('Create PDF', 'createPdf')
    .addToUi()

} // onOpen()

/**  
 * Take the fields from the active row in the active sheet
 * and, using a Google Doc template, create a PDF doc with these
 * fields replacing the keys in the template. The keys are identified
 * by having a % either side, e.g. %Name%.
 *
 * @return {Object} the completed PDF file
 */

function createPdf() {

  var ui = SpreadsheetApp.getUi()

  if (TEMPLATE_ID === '') {
    
    ui.alert('TEMPLATE_ID needs to be defined in code.gs')
    return
  }

  // Set up the docs and the spreadsheet access
  
  var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
      copyId = copyFile.getId(),
      copyDoc = DocumentApp.openById(copyId),
      copyBody = copyDoc.getActiveSection(),
      activeSheet = SpreadsheetApp.getActiveSheet(),
      numberOfColumns = activeSheet.getLastColumn(),
      activeRowIndex = activeSheet.getActiveRange().getRowIndex(),
      activeRow = activeSheet.getRange(activeRowIndex, 1, 1, numberOfColumns).getValues(),
      headerRow = activeSheet.getRange(1, 1, 1, numberOfColumns).getValues(),
      columnIndex = 0,
      headerValue,
      activeCell,
      ID = null,
      recipient = null
 
  // Replace the keys with the spreadsheet values and look for a couple
  // of specific values
 
  for (;columnIndex < headerRow[0].length; columnIndex++) {
    
    headerValue = headerRow[0][columnIndex]
    activeCell = activeRow[0][columnIndex]
    
    copyBody.replaceText('%' + headerValue + '%', activeCell)
    
    if (headerValue === FILE_NAME_COLUMN_NAME) {
    
      ID = activeCell
      
    } else if (headerValue === EMAIL_COLUMN_NAME) {
    
      recipient = activeCell
    }
  }
  
  // Create the PDF file, rename it if required, delete the doc copy
  // and email it
    
  copyDoc.saveAndClose()

  var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))  

  if (PDF_FILE_NAME !== '') {
  
    newFile.setName(PDF_FILE_NAME)
    
  } else if (ID !== null){

    newFile.setName(ID)
  }
  
  copyFile.setTrashed(true)
  
  if (recipient !== null) {
  
    MailApp.sendEmail(
      recipient, 
      EMAIL_SUBJECT, 
      EMAIL_BODY,
      {attachments: [newFile]})
  }
  
  ui.alert('New PDF file created in the root of your Google Drive ' + 
        'and emailed to ' + recipient)
  
} // createPdf()

[LtStaffel]

Would it be easier to write a script to completely replace Google Forms? Or are your forms changing and too dynamic? Having it all in a JS generated form to begin with might make it easier to format the user's inputs since they'd already be stored in JS variables and such, and therefore easier to manipulate.