Generating PDF's from Spreasheet Template

[Ops OCS]

Utilizing GoogleAppsScript I'm creating a PDF file created from a Spreadsheet, however, everytime I generate a new PDF, it creates a PDF of the template File, not the temp File I generated

I'm creating unique PDF's for my company to generate new Bills of Lading. I'd like to add data to a form and have Apps script generate a PDF from that data.

I have a template file in the form of a google sheet. Its formatted to how I like it and works fine if I manually generate a pdf from there. 

The apps script I have currently takes a set of dummy data, creates a copy of a spreadsheet from a template, replaces the data, and generates a PDF from that new Spreadsheet.

The new Spreadsheet will have successfully have its data replaced, but its corresponding PDF will have the original template data instead!

Can anyone help me with this? 

function createPDF(){

  const info = {

    date : new Date(),

    origin: "Company",

    originAddress: "Address",

    originCityStateZip: "City STATE Zip",

    instructions: "Hello World", 

    packSlip: [[250,"Hello Kitty"]]

  };

  const pdfFolder = DriveApp.getFolderById(/*HIDDEN ID*/);

  const tempFolder = DriveApp.getFolderById(/*HIDDEN ID*/);

  const templateDoc = DriveApp.getFileById(/*HIDDEN ID*/);

  

  const tempFile = templateDoc.makeCopy(tempFolder);

  const openDoc = SpreadsheetApp.openById(tempFile.getId());

  const ss = openDoc.getSheetByName("BOL");

  ss.getRange(1, 2).setValue(info.date);

  ss.getRange(3, 2).setValue(info.origin);

  ss.getRange(4, 2).setValue(info.originAddress);

  ss.getRange(5, 2).setValue(info.originCityStateZip);

  ss.getRange(15, 1).setValue(info.instructions);

  ss.getRange(20, 1).setValue(info.packSlip[0][0]);

  ss.getRange(20, 2).setValue(info.packSlip[0][1]);

    Logger.log(tempFile.getUrl());

  //const pdfContentBlob = tempFile.getAs(MimeType.PDF);

  //const newPDF = pdfFolder.createFile(pdfContentBlob).setName(new Date);

    //var ui = SpreadsheetApp.getUi();

    //ui.alert(newPDF.getUrl());

    var pdf = DriveApp.getFileById(tempFile.getId()).getAs('application/pdf');

    var savCopy = pdfFolder.createFile(pdf).setName(new Date());

    

}

[Jean-Luc Vanhulst]

Whatever you are doing in ss.. never touches tempFile.

I don't see any code that moves something from the ss spreadsheet to the copy of the file you made.

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c9b63a35-63f7-494b-9a42-81a75107dd2eo%40googlegroups.com.

[o...@ogdencustomsolutions.com]

By creating a new file, opening it and calling that openDoc, and setting the active Spreadsheet to ss that doesn't connect back to Tempfile? 

When i open the tempFile, it has all the data changed as expected, its only the pdf that isn't right. 

[Jean-Luc Vanhulst]

there might be a script inside tempFile that does that. But then that script should also execute that code to generate the copy probably. 

This script doesn't do anything with the data from ss.range...

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e80fe1c8-49f7-4234-b3c3-af51cc423b19n%40googlegroups.com.

[o...@ogdencustomsolutions.com]

How would you recommend fixing this?

[Jean-Luc Vanhulst]

Look in the template code. That's probably where you want to add that copy that you have here now. You say the template is filled correctly, so at the end of that code add then copy file /folder logic that you have in your example above. But hard to tell of course with the code from the template 

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/681baed2-8ac6-46a3-bec3-c59940be36fcn%40googlegroups.com.

[Clark Lind]

I've been using a script to create pdfs and email them and save a copy to drive daily for the last few years (thanks Amit [I think]!  [https://www.labnol.org/  ]). It still works today. This uses the Sheets "export as pdf" capability to create the pdf as a blob, then do whatever you want with the blob, save it, email it, whatever.

I merged the key parts into your code which I think should work. I didn't test this, but should give you the base code to play with:

function createPDF(){

  const info = {

    date : new Date(),

    origin: "Company",

    originAddress: "Address",

    originCityStateZip: "City STATE Zip",

    instructions: "Hello World", 

    packSlip: [[250,"Hello Kitty"]]

  };

  const pdfFolder = DriveApp.getFolderById(/*HIDDEN ID*/);

  const tempFolder = DriveApp.getFolderById(/*HIDDEN ID*/);

  const templateDoc = DriveApp.getFileById(/*HIDDEN ID*/);

  

  const tempFile = templateDoc.makeCopy(tempFolder);

  const ssID = tempFile.getId();       //grab the Spreadsheet ID

  const openDoc = SpreadsheetApp.openById(ssID);  //since we have it, may as well use it

  const ss = openDoc.getSheetByName("BOL");

  const gID = ss.getSheetId();  //get the GID portion of the URL

 //Make the date prettier in whatever format you want

  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd - HH:mm'); 

  ss.getRange(1, 2).setValue(info.date);

  ss.getRange(3, 2).setValue(info.origin);

  ss.getRange(4, 2).setValue(info.originAddress);

  ss.getRange(5, 2).setValue(info.originCityStateZip);

  ss.getRange(15, 1).setValue(info.instructions);

  ss.getRange(20, 1).setValue(info.packSlip[0][0]);

  ss.getRange(20, 2).setValue(info.packSlip[0][1]);

 //   Logger.log(tempFile.getUrl());

  //const pdfContentBlob = tempFile.getAs(MimeType.PDF);

  //const newPDF = pdfFolder.createFile(pdfContentBlob).setName(new Date);

    //var ui = SpreadsheetApp.getUi();

    //ui.alert(newPDF.getUrl());

//set the URL for exporting the blob as pdf

const url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ ssID +  "&gid="+ gID + "&printtitle=true&fzr=true&portrait=false&fitw=true" + "&exportFormat=pdf";

//(more info on these pdf export options here; e.g., portrait vs landscape, title in header, footer, page numbers, etc etc)

//this may not be needed anymore, but I still use it

//get an access token (oath type stuff)

const token = ScriptApp.getOAuthToken();

//fetch the blob using the oath token

const response = UrlFetchApp.fetch(url, {

    headers: {

      'Authorization': 'Bearer ' +  token

    }

  });

//set the name of the file

const blob = response.getBlob().setName(today + '.pdf');

//save the file to drive

 pdfFolder.createFile(blob);

//    var pdf = DriveApp.getFileById(tempFile.getId()).getAs('application/pdf');

//    var savCopy = pdfFolder.createFile(pdf).setName(new Date());

    

}

[Clark Lind]

Looks like the url wrapped.. it should all be one line:

const url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + ssID +  "&gid="+ gID + "&printtitle=true&fzr=true&portrait=false&fitw=true" + "&exportFormat=pdf";  

[Riël Notermans]

Bevore calling 

  var pdf = DriveApp.getFileById(tempFile.getId()).getAs('application/pdf');

Do

spreadsheetApp.flush()

to make sure all pending operations are done. 

Met vriendelijke groet,

Riël Notermans Technisch Directeur

040 711 41 94 ri...@zzapps.nl www.zzapps.nl

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/b69d618e-dbe6-40b7-aad1-35683796ad67n%40googlegroups.com.