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 1 · 2022-09-13T19:58:53.183

1

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:

edited Sep 13 '22 at 19:58

answered Sep 13 '22 at 18:50

Giselle Valladares

2,075
1
4
13

This was just an example sheet. The actual project has hundreds of cells to replace. hence the "for (const key in values) { body.replaceText("{{"+key+"}}", values[key]);" Could i use getSheet instead of getRange?? – Zeekane Sep 13 '22 at 19:00
Also, the template spreadsheet is separate. I forgot to add a link in the original post - https://docs.google.com/spreadsheets/d/1Ye7DyJQOjA3J_EUOQteWcuASBCfqlA-_lzyNw0REjY8/edit?usp=sharing – Zeekane Sep 13 '22 at 19:08
Depending on what you want to import as `Body`. You can create the same template for all the rows in a specific cell, for example `A1`, and refer to it in the code. Add variables that will replace the information in rows in the loop in the template like {Name}. Lastly, you can use call the cell and use [replace()](https://stackoverflow.com/questions/19877554/how-to-replace-text-in-google-spreadsheet-using-app-scripts) with the information pulled from the sheet. – Giselle Valladares Sep 13 '22 at 19:09
This gives - Exception: The parameters (String) don't match the method signature for SpreadsheetApp.Spreadsheet.getSheetId. at Create_PDF(Code:32:60) at After_Submit(Code:13:21) – Zeekane Sep 13 '22 at 19:16
I edited the answer. It mas my mistake. To open a Sheet by ID you need to use `SpreadsheetApp.openById(newTempFile.getId());` not `SpreadsheetApp.getActiveSpreadsheet().getSheetId(newTempFile.getId());` – Giselle Valladares Sep 13 '22 at 19:37
Thank you, but now I'm getting - TypeError: body.replaceText is not a function... I may have to settle for using a google Doc as a template and starting the project over – Zeekane Sep 13 '22 at 19:44
The error is base on how replace() works in Google Sheets. I found [this](https://developers.google.com/apps-script/reference/spreadsheet/sheet#createtextfinderfindtext), on how you will use it or you can review [this documentation](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace) of JavaScript. – Giselle Valladares Sep 13 '22 at 19:55
Also, I edited an example on how to create a template. – Giselle Valladares Sep 13 '22 at 19:57
the body.replace("{{"+key+"}}", values[key]); worked with a google doc. – Zeekane Sep 13 '22 at 20:01
i changed it to body.toString().replace and now I get - TypeError: openDoc.saveAndClose is not a function. Sheets seem to be much harder than docs to work with. but there has to be a way to do this.. Also the template will have 50 different cells than need replaced. Not just one – Zeekane Sep 13 '22 at 20:08
[saveAndClose()](https://developers.google.com/apps-script/reference/document/document#saveandclose) is part of `DocumentApp`, and it doesn't work with Google Sheets. You can use `SpreadsheetApp.flush();`. You can read more information about flush [here](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#flush) – Giselle Valladares Sep 13 '22 at 20:15
Much closer. it can now save and create the pdf. It's just not replacing any of the values in the sheet. I'll continue to research how to do this to multiple cells at once – Zeekane Sep 13 '22 at 20:22
Maybe `body.replace("{{Question 1}}", data[1][i]).replace("{{Question 2}}",data[2][i]);` – Giselle Valladares Sep 13 '22 at 20:31
This is what - body.replace("{{"+key+"}}", values[key]); - Should do from the input values of that form submission. however, even when I do Logger.log(body); at the end. Nothing has been replaced ? – Zeekane Sep 13 '22 at 20:47
Not sure if replace will work with an object. – Giselle Valladares Sep 13 '22 at 20:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/248026/discussion-between-jml9548-and-giselle-valladares). – Zeekane Sep 13 '22 at 21:02

Create PDF from Google Sheet Template

1 Answers1