Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
}
@Scorpion229

This comment has been minimized.

Copy link

@Scorpion229 Scorpion229 commented Dec 28, 2018

Hi Andrew,

I just came across your profile. Firstly, thank you for sharing.

I'm new to Appscript however my issue is -

  1. I'm Unable to copy format with background colour when converting to pdf
  2. Would appreciate if you could help me with below code. Need help to covert selected rows & columns to pdf which should be saved in a folder (In Google Drive) and should be able to send out separate emails to individuals with PDF attached.

//PDF Generator for selected range and sending out email

//You can enter range here

RANGE="B2:AC36";
SHEET_NAME="Sheet1";

//Types available : pdf,csv or xlsx
EXPORT_TYPE="pdf";

function EmailRange() {
//Assign The Spreadsheet,Sheet,Range to variables
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=ss.getSheetByName(SHEET_NAME);
var range=sheet.getRange(RANGE);

//Range values to export
var values=range.getValues();

//Create temporary sheet
var sheetName=Utilities.formatDate(new Date(), "GMT", "MM-dd-YYYY hh:mm:ss");
var tempSheet=ss.insertSheet(sheetName);

//Copy range onto that sheet
tempSheet.getRange(1, 1, values.length, values[0].length).setValues(values);

//Save active sheets (Unhidden)
var unhidden=[];
for(var i in ss.getSheets()){
if(ss.getSheets()[i].getName()==sheetName) continue;
if(ss.getSheets()[i].isSheetHidden()) continue;
unhidden.push(ss.getSheets()[i].getName());
ss.getSheets()[i].hideSheet();
}

//Authentification
var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

var url="https://docs.google.com/spreadsheets/d/"+ss.getId()+"/export?format="+EXPORT_TYPE;

//Fetch URL of active spreadsheet
var fetch=UrlFetchApp.fetch(url,params);

//Get content as blob
var blob=fetch.getBlob();

var mimetype;
if(EXPORT_TYPE=="pdf"){
mimetype="application/pdf";
}else if(EXPORT_TYPE=="csv"){
mimetype="text/csv";
}else if(EXPORT_TYPE=="xlsx"){
mimetype="application/xlsx";
}else{
return;
}

//Send Email
GmailApp.sendEmail('raghu@blabla.com',
'Subject',
'Hello, Please find PDF attached' ,

                 {
                     attachments: [{
                     fileName: "File Name" + "."+EXPORT_TYPE,
                     content: blob.getBytes(),
                     mimeType: mimetype
                  }]
});

//Reshow the sheets
for(var i in unhidden){
ss.getSheetByName(unhidden[i]).showSheet();
}

//Delete the temporary sheet
ss.deleteSheet(tempSheet);
}

@AMuquite

This comment has been minimized.

Copy link

@AMuquite AMuquite commented Sep 8, 2020

Hi This script was working fine till last week, but form this month pdf is not readable.

@cbh35711

This comment has been minimized.

Copy link

@cbh35711 cbh35711 commented Oct 8, 2020

AMuquite, for me too. This was working on August 12th, and no longer on the 13th.

@andrewroberts

This comment has been minimized.

Copy link
Owner Author

@andrewroberts andrewroberts commented Oct 8, 2020

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

This comment has been minimized.

Copy link
Owner Author

@andrewroberts 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

This comment has been minimized.

Copy link

@PDowd2012 PDowd2012 commented Feb 19, 2021

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

This comment has been minimized.

Copy link

@bihaequipinc bihaequipinc commented Jul 8, 2021

Hi ,

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

@bihaequipinc

This comment has been minimized.

Copy link

@bihaequipinc 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