Generate PDF through Google App Script

Question

I found a script 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 % either side, e.g. %Name%.

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:

1. 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;

2. User clicks a button to generate a PDF report based on the sheet state;

3. The script displays a message if an invalid option was set or if the report was generated successfully;

My current 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' && month === 'all' && 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 < 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')
} 

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 year, month, and total_cost variables. What I am doing wrong and I how can I achieve the expected behavior?

Answer 1

How about this modification? I think that there are several answers for your situation. So please think of this as one of them.

Modification points :

• You can use copyDoc like copyDoc.replaceText('%' + replace_values[i] + '%', replace_values[i]).
• After var ss = SpreadsheetApp.getActiveSheet(), is run, an error occurs at sheet = ss.getSheets()[0],.
  • If you want to use the sheet with the index of "0", ss of var ss = SpreadsheetApp.getActiveSpreadsheet(), can be used as it.
  • In this modification, I thought that you might want to also use other index. So I used sheet = ss.getSheets()[0];.
• project of project = ss.getRange('C3').getValue(), is not used in createPdf().
• var replace_values = []; replace_values.push(total_cost, year, month) is the same to var replace_values = [total_cost, year, month];.
  • You can also use the destructuring assignment like [total_cost, month, year] = [sheet.getRange('C14').getValue(), sheet.getRange('C7').getValue(), sheet.getRange('C9').getValue()]; instead of total_cost = sheet.getRange('C14').getValue(),month = sheet.getRange('C7').getValue(),year = sheet.getRange('C9').getValue();

When these are reflected to the modified script, please modify as follows.

From :

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 < replace_values.length; i++) {
  copyBody.replaceText('%' + replace_values[i] + '%', replace_values[i])
}

To :

var copyFile = DriveApp.getFileById(TEMPLATE_ID).makeCopy(),
    copyId = copyFile.getId(),
    copyDoc = DocumentApp.openById(copyId);
var ss = SpreadsheetApp.getActiveSpreadsheet(), // Modified
    sheet = ss.getSheets()[0],
    total_cost = sheet.getRange('C14').getValue(), // Modified
    month = sheet.getRange('C7').getValue(), // Modified
    year = sheet.getRange('C9').getValue(); // Modified
var replace_values = [total_cost, year, month]; // Modified
for (var i = 0; i < replace_values.length; i++) {
  copyDoc.replaceText('%' + replace_values[i] + '%', replace_values[i]); // Modified
}

Note :

• You can also confirm the errors in your script using Execution transcript.

References :

• getActiveSpreadsheet()
• Execution transcript

If this was not what you want, please tell me. I would like to modify it.