title: Generate PDF through Google App Script
body: <p>I found a <a href="https://gist.github.com/andrewroberts/8985d5d0a50c905ed43b639a97c2035f" rel="nofollow noreferrer">script</a> that takes the fields from the active row in the active Google Spreadsheet and, using a Google Doc template, creates a PDF with these fields replacing the keys in the template. The keys are identified by having a <code>%</code> either side, e.g. <code>%Name%</code>.</p>
  
  <p>The linked code adds a menu entry to the UI, so the user only needs to click in one cell and then select the 'Create PDF' menu option. I want to make some changes, in order to meet my needs. My context is:</p>
  
  <ol>
  <li><p>User chooses one option in each in-cell drop-down list, respectively found in C3, C5, C7 and C9. These choices trigger filters in my sheet;</p></li>
  <li><p>User clicks a button to generate a PDF report based on the sheet state;</p></li>
  <li><p>The script displays a message if an invalid option was set or if the report was generated successfully;</p></li>
  </ol>
  
  <p>My current code:</p>
  
  <pre><code>var TEMPLATE_ID = ''
  var PDF_FILE_NAME = ''
  
  function checkEntries(){
    var ss = SpreadsheetApp.getActiveSheet(),
        sheet = ss.getSheets()[0],
        project = ss.getRange('C3').getValue(),
        month = ss.getRange('C5').getValue(),
        year = ss.getRange('C7').getValue();
  
    if(project === 'all' &amp;&amp; month === 'all' &amp;&amp; year === 'all'){
      SpreadsheetApp.getUi().alert('The report is always specific to a project in a specific year and month')
      return;
    }
  }
  
  function createPdf() {
    var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
        copyId = copyFile.getId(),
        copyDoc = DocumentApp.openById(copyId),
        copyBody = copyDoc.getActiveSection();
  
    var ss = SpreadsheetApp.getActiveSheet(),
        sheet = ss.getSheets()[0],
        project = ss.getRange('C3').getValue(),
        total_cost = ss.getRange('C14').getValue(),
        month = ss.getRange('C7').getValue(),
        year = ss.getRange('C9').getValue();
  
    var replace_values = [];
    replace_values.push(total_cost, year, month)
  
    for (var i = 0; i &lt; replace_values.length; i++) {
      copyBody.replaceText('%' + replace_values[i] + '%', 
                           replace_values[i])          
    }
    copyDoc.saveAndClose()
  
    var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'))  
    if (PDF_FILE_NAME !== '') {
      newFile.setName(PDF_FILE_NAME)
    } 
  
    copyFile.setTrashed(true)
  
    SpreadsheetApp.getUi().alert('report generated successfully')
  } 
  </code></pre>
  
  <p>I link that script to a button, but it does not get cell values and does not generate the PDF. The expected behavior is generate a PDF doc, based on a Doc template, replacing placeholders with <code>year</code>, <code>month</code>, and <code>total_cost</code> variables. What I am doing wrong and I how can I achieve the expected behavior?</p>