PDF Generation - Google Scripts help

[Darren_Murphy]

haha, don’t sweat. I’m a Certified Expert when it comes to putting my foot in my mouth, so you’ve got a ways to go yet

[Pratik_Shah]

Thank you for this.
Actually I was asking if Document Studio will work upon Glide Form submission.

[ehdubya]

Does for us. Document Studio just takes whatever column/data from your Google sheet that you need formatted and modifies it into PDF. Glide allows us to deposit the data, Document Studio allows us to create PDF’s out of the deposited data.

[Pratik_Shah]

Thank you for the response. That is good to know.
Does Glide Form submission triggers Document Studio automatically or you will have to do it manually in GSheet?
My requirement is, I want Document Studio to start its work as soon as Glide Form submission is done. As far as I know, it is not possible except for Time Trigger of 1 hour or 30 min?

Can you help me with this?

[ehdubya]

Yeah no problem!

To be honest, we haven’t tried it for immediate action-based triggers yet such as your use case. We use it to send out monthly reporting and invoices to clients, so I’m not sure I’d be any help (sorry!).

I know they have some extensive videos on how to use the add-ons, have you tried watching those?

[Pratik_Shah]

I am regular user of Autocrat (since last 3-4 years) but I guess, all such add-on either has time trigger or Gform submit trigger. Additionally time trigger has a time interval of minimum 1 hour or something which does not fulfill y requirements.

[Purvang_Joshi]

This thread helped me to achieve this. Thanks to all contributors.
This solution is without interfering with google sheets. Also, it’ll trigger by clicking a button(webhook action)

Document Merge using Google Apps Script, Webhook Action & Google Docs

Hello, Gliders, Many of you have use-cases like, → Sending certificates to your students → Sending Auto-Generated Invoices, Quotes & Many more → creating ID cards for the events → Mail merge custom PDFs Here I’ll show you how to implement it. Demo: https://milky-produce-8069.glideapp.io/ [It’s in Personal Plan, Only 80 Webhook Actions Left] Whenever the user fills and submits a button, it’ll trigger the script and send a pdf attached mail to the receiver. The flow of the System Gli…

[Lin_Altshuler]

Hi,
I opened a new topic and was referred here.
I am looking for a solution to export data from GS to a printable PDF template.
Some of the data contains URL images (Quickchart.il) and HTML+CSS Tables stored as template columns.
I know I will have to write the template scripts to a new row to capture the data.
I know that Document Studio give a solution for mapping columns in a word template but not sure it supports HTML or URLS.
Any Ideas?

[Eric_Penn]

Is all of the data you want in the PDF in GS or is some of it still in Glide?

[Lin_Altshuler]

some is in Glide but that’s not a problem, I guess I will “add row” to a new sheet that will write the data to GS before creating the PDF

[biha]

I jump into this thread just because i am finding the way to get the file link using the Folder name and file name in Google Drive and @Luther recommend this thread.

I understand the script doing:

1. Get File Name to be named
2. Generate PDF
3. Send the PDF File as an attachment

That’s all i currently using.

But another crucial part is how can i get that File Link to placed on google sheet so people can straightway open in Glideapps ?

I mean like which part of the script had return this part:

@Uzo

function emailSpreadsheetAsPDF() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
  // Add the link to your spreadsheet here 
  // or you can just replace the text in the link between "d/" and "/edit"
  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI/edit");

  // We are going to get the email address from the cell "B7" from the "Invoice" sheet
  // Change the reference of the cell or the name of the sheet if it is different
  const value = ss.getSheetByName("Invoice").getRange("B7").getValue();
  const email = value.toString();

  // Subject of the email message
  const subject = 'Your Invoice';

  // Email Text. You can add HTML code here - see ctrlq.org/html-mail
  const body = "Sent via Generate Invoice from Google Form and print/email it";

  // Again, the URL to your spreadsheet but now with "/export" at the end
  // Change it to the link of your spreadsheet, but leave the "/export"
  const url = 'https://docs.google.com/spreadsheets/d/17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=letter' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=false' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid=1030891993'; // the sheet's Id. Change it to your sheet ID.
  // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid number of the sheet is on the end of your link.
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  // Generate the PDF file
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  
  // Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: "Invoice" + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });

  // Save the PDF to Drive. The name of the PDF is going to be the name of the Company (cell B5)
  const nameFile = ss.getSheetByName("Invoice").getRange("B5").getValue().toString() +".pdf"
  DriveApp.createFile(response.setName(nameFile));
}

[Darren_Murphy]

@biha

I mean like which part of the script had return this part:

@Darren_Murphy

returning a URL link to the created PDF, which is written back to the sheet

var url = pdf.getUrl();

[biha]

The @Uzo 's script one?

No var url = pdf.getUrl();

[Uzo]

let me know if any questions… sorry for the late response… still hungover after the new year party LOL

//    (c) StructureArt DIGITAL

function savePdf() {
 
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  
  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  
  var pdfName = "PDF 1"; //replace with generated unique name

 
   var folder = DriveApp.getFolderById("FOLDER ID"); //replace with your public folder ID
 
  
  // Copy spreadsheet.
  
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  // Delete redundant sheets.
  
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }
  
  var destSheet = destSpreadsheet.getSheets()[0];

  // Repace cell values with text (to avoid broken references).
  
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  // Save to pdf.
  
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  // Delete the temporary sheet.
  
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
  
// Create file URL

   var filename = encodeURI(pdfName);
   var files = DriveApp.getFilesByName(pdfName);
   while (files.hasNext()) {
      var file = files.next();
      if (file) {
         var fileUrl = file.getUrl();
         
      };
   };

  Logger.log(fileUrl);
}

[Uzo]

Code BOOK - Desktop

[Uzo]

@biha simply set value…

sourceSheet.getRange(“A2”).setValue(fileUrl) // change A2 to your cell

[Kyle_Brabec]

Stuck on step 3 where I need a GCP project number. Does this cost extra because I need to make a google cloud account? I got it working with a google sheet earlier, but I’d like to use this more direct approach with only a google doc. I have yet to connect to glide.