Convert a multi-sheet Google Spreadsheet into a PDF, with option to email it.

convertSpreadsheetToPdf.gs

/*
 * 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)  
}

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('[email protected]',
'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);
}

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

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

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...

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.

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!

Hi ,

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

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;
}