Create PDF from Google Sheet Template

Question

I am fairly new to code and App Script, but I've managed to come up with this from research. Form submitted, Sheet populated, take entry data, copy and append new file, save as pdf, email pdf

I've created examples of what I've been trying to do

Link to form - https://docs.google.com/forms/d/e/1FAIpQLSfjkSBkn3eQ1PbPoq0lmVbm-Dk2u2TP_F_U5lb45SddsTsgsA/viewform?usp=sf_link

link to spreadsheet - https://docs.google.com/spreadsheets/d/1kWQCbNuisZsgWLk3rh6_Iq107HoK7g-qG2Gln5pmYTE/edit?resourcekey#gid=1468928415

link to template - https://docs.google.com/spreadsheets/d/1Ye7DyJQOjA3J_EUOQteWcuASBCfqlA-_lzyNw0REjY8/edit?usp=sharing

However I receive the following error - Exception: Document is missing (perhaps it was deleted, or you don't have read access?) at Create_PDF(Code:32:34) at After_Submit(Code:13:21)

App Script Code as follows - If I use a google Doc as a template it works. However I would like to use a spreadsheet as a template, and have the result pdf content fit to page. Please let me know if you need any additional information for this to work.

function After_Submit(e, ){
    var range = e.range;
    var row = range.getRow(); //get the row of newly added form data
    var sheet = range.getSheet(); //get the Sheet
    var headers = sheet.getRange(1, 1, 1,5).getValues().flat(); //get the header names from A-O
    var data = sheet.getRange(row, 1, 1, headers.length).getValues(); //get the values of newly added form data + formulated values
    var values = {}; // create an object 
    for( var i = 0; i < headers.length; i++ ){
      values[headers[i]] = data[0][i]; //add elements to values object and use headers as key
    }
  
    Logger.log(values);
    const pdfFile = Create_PDF(values);
    sendEmail(e.namedValues['Your Email'][0],pdfFile);
  }
  
  function sendEmail(email,pdfFile,){
    
    GmailApp.sendEmail(email, "Subject", "Message", {
      attachments: [pdfFile], 
      name: "From Someone"
  
    });
   
  }
  function Create_PDF(values,) {
    const PDF_folder = DriveApp.getFolderById("1t_BYHO8CqmKxVIucap_LlE0MhslpT7BO");
    const TEMP_FOLDER = DriveApp.getFolderById("1TNeI1HaSwsloOI4KnIfybbWR4u753vVd");
    const PDF_Template = DriveApp.getFileById('1Ye7DyJQOjA3J_EUOQteWcuASBCfqlA-_lzyNw0REjY8');
    
    const newTempFile = PDF_Template.makeCopy(TEMP_FOLDER);
    const  OpenDoc = DocumentApp.openById(newTempFile.getId());
    const body = OpenDoc.getBody();
  
    for (const key in values) {
   body.replaceText("{{"+key+"}}", values[key]);
}
  
    OpenDoc.saveAndClose();
  
    const BLOBPDF = newTempFile.getAs(MimeType.PDF);
    const pdfFile =  PDF_folder.createFile(BLOBPDF);
    console.log("The file has been created ");
    return pdfFile;
}

Giselle Valladares · Answer

You get the error message with Google Sheets because you are using a Google Doc class to create the PDF, which is not compatible with Google Sheets.

DocumentApp can only be used with Google Docs. I will advise you to change

const  OpenDoc = DocumentApp.openById(newTempFile.getId());

for

const  openDoc = SpreadsheetApp.openById(newTempFile.getId());
const newOpenDoc = openDoc.getSheetByName("Sheet1");

And depending on the Google Sheet where the "Body" of the information is located. Replace:

const body = OpenDoc.getBody();

for an equivalent like getRange() or any Range class that helps you target the information you need. For example:

// This example is assuming that the information is on the cel A1. 
const body = newOpenDoc.getRange(1,1).getValue();

The template for the PDF should be something like this:

Create PDF from Google Sheet Template

1 Answers