Skip to content

Instantly share code, notes, and snippets.

@andrewroberts
Last active September 15, 2022 17:21
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save andrewroberts/26d460212874cdd3f645b55993942455 to your computer and use it in GitHub Desktop.
Save andrewroberts/26d460212874cdd3f645b55993942455 to your computer and use it in GitHub Desktop.
Convert a multi-sheet Google Spreadsheet into a PDF, with option to email it.
/*
* Save spreadsheet as a PDF
*
* Based on Dr.Queso's answer in http://stackoverflow.com/questions/30367547/convert-all-sheets-to-pdf-with-google-apps-script/30492812#30492812
*
* @param {String} email Where to send the PDF [OPTIONAL]
* @param {String} spreadsheetId Or the active spreadsheet[OPTIONAL]
* @param {String} sheetName The tab to output [OPTIONAL]
* @param {String} PdfName [OPTIONAL]
*/
function convertSpreadsheetToPdf(email, spreadsheetId, sheetName, pdfName) {
var spreadsheet = spreadsheetId ? SpreadsheetApp.openById(spreadsheetId) : SpreadsheetApp.getActiveSpreadsheet();
spreadsheetId = spreadsheetId ? spreadsheetId : spreadsheet.getId()
var sheetId = sheetName ? spreadsheet.getSheetByName(sheetName).getSheetId() : null;
var pdfName = pdfName ? pdfName : spreadsheet.getName();
var parents = DriveApp.getFileById(spreadsheetId).getParents();
var folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();
var url_base = "https://docs.google.com/spreadsheets/d/" + spreadsheet.getId() + "/"
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
// Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))
// following parameters are optional...
+ '&size=letter' // paper size
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=false&printtitle=false&pagenumbers=false' //hide optional headers and footers
+ '&gridlines=false' // hide gridlines
+ '&fzr=false'; // do not repeat row headers (frozen rows) on each page
var options = {
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
}
}
var response = UrlFetchApp.fetch(url_base + url_ext, options);
var blob = response.getBlob().setName(pdfName + '.pdf');
folder.createFile(blob);
if (email) {
var mailOptions = {
attachments:blob
}
MailApp.sendEmail(
email,
"Here is a file named " + pdfName,
"Please let me know if you have any questions or comments.",
mailOptions);
}
} // convertSpreadsheetToPdf()
function onOpen() {
SpreadsheetApp.getUi().createMenu('Create PDF').addItem('Create PDF', 'testCreateOnePdfOneSheet').addToUi()
}
var TEST_EMAIL = '' // !!!! Complete this if you want the PDF to be emails !!!!!!!!!!!
var GSHEET_ID = '1BhHmQ9QCEcV9ZNCuOo4-IZ1o-_b9_TpF5vowo0mkFBU' // "Convert multi-sheet Google Sheet to PDF"
var TAB_NAME = 'Sheet2'
var PDF_NAME = 'PDF 3'
function testVarious() {
// Create a PDF containing all the tabs in the active spreadsheet, name it
// after the spreadsheet, and email it
convertSpreadsheetToPdf(TEST_EMAIL)
// Create a PDF containing all the tabs in the spreadsheet specified, name it
// after the spreadsheet, and email it
convertSpreadsheetToPdf(TEST_EMAIL, GSHEET_ID)
// Create a PDF just containing the tab 'Sheet2' in the active spreadsheet, specify a name, and email it
convertSpreadsheetToPdf(TEST_EMAIL, null, 'Sheet2', 'PDF 3')
}
function testCreateOnePdfOneSheet() {
// Create a PDF just containing the tab 'Sheet2' in the active spreadsheet, specify a name, and email it
convertSpreadsheetToPdf(TEST_EMAIL, null, TAB_NAME, PDF_NAME)
}
@andrewroberts
Copy link
Author

There has been a change to the format of the URL so the line

var url_base = spreadsheet.getUrl().replace(/edit$/,'');

has to change. I can't remember what the update is off the top of my head - I'll try and track it down...

@andrewroberts
Copy link
Author

andrewroberts commented Oct 9, 2020

Found it:

Google have changed the format of what is returned by getUrl(). See the answer in this SO question: .

I've updated the script for this fix.

@PDowd2012
Copy link

Hi Andrew,

This works great and I was able to modify it to fit our needs. However, even though it's declared false, the gridlines are still appearing in the .pdf.

Any idea how to fix this?

Thanks!

@bihaequipinc
Copy link

Hi ,

can you explain for line 25? what it is for?

@bihaequipinc
Copy link

bihaequipinc commented Jul 9, 2021

Hi all,

I really new in apps script.
I have a apps script below that convert from Google sheet to PDF. This is for only one sheet/tab.

My question is , how to convert multiple tabs/sheets of google sheet?


   var changedFlag = false;
var TEMPLATESHEET='M-Email Me';

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/1Zgs1jzjIeaBpd5Ms7emQgxhVJBMtlEOlDNDfxlhSRiY/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("M-Generate").getRange("F3").getValue();
  const email = value.toString();

  // Subject of the email message
  const subject = ss.getSheetByName("M-Generate").getRange("B3").getValue();

   // Email Text. You can add HTML code here - see ctrlq.org/html-mail
  const body = "Automated Quotation - Sent via Auto Generate from Glideapps";

  // 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/1Zgs1jzjIeaBpd5Ms7emQgxhVJBMtlEOlDNDfxlhSRiY/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=A4' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=true' + // 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=true' + // do not repeat row headers (frozen rows) on each page
    '&gid=101637384'; // the sheet's Id. Change it to your sheet ID.


  const exportOptionss =
    'exportFormat=xlsx' +
    '&gid=557878560';// export as pdf
  
  // 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();
  var responsee = UrlFetchApp.fetch(url+exportOptionss, params).getBlob();
  
  // Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: ss.getSheetByName("M-Generate").getRange("B3").getValue().toString() +".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        },{
            fileName: ss.getSheetByName("M-Generate").getRange("B3").getValue().toString() +".xlsx",
            content: responsee.getBytes(),
            mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        }]
    });

  // Save the PDF to Drive. (in the folder) The name of the PDF is going to be the name of the Company (cell B5)
  const nameFile = ss.getSheetByName("M-Generate").getRange("B3").getValue().toString() +".pdf"
  const folderID = "1SHKAXCyXmNMwv1QKPYW0QCwey-yzpJih";
  DriveApp.getFolderById(folderID).createFile(response).setName(nameFile);
}


function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  var sheet = event.source.getActiveSheet();
 
  if (sheetname == 'M-Email Me') {
    emailSpreadsheetAsPDF() ;
  } else return;
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment