Include sheet names when exporting Google Spreadsheets to PDF

Question

I am exporting a Google Spreadsheet to PDF format with Google Apps Script, and would like to 'include sheet names' on the PDF pages. Is this possible to do this from the code?

var spreadsheetFile = DocsList.getFileById(spreadsheet_id);
var blob = spreadsheetFile.getAs('application/pdf'); 
blob.setName(spreadsheetFile.getName());
DocsList.getFolder(file_destination).createFile(blob); 

In the Spreadsheet app it is supported in the UI, so I am wondering whether Apps Scripts supports this too?

Carruthers

Answer 1

The .getAs() method does not allow for parameters but you can use the spreadsheet api where you can choose all the parameters available from the "normal" Ui. See this post answer to see how to use it, and follow the github link

Here is the demo code as there were a few inconsistencies in the code in ref. (just to illustrate with an example exporting sheet1 with grid and title)

note this will ask for 2 distinct authorizations.

function test(){
  var key = "0AnqSFd3iikE3dFd1WEVhMFhYczM5VWpuNDZHQ3AwZEE";
  var pdf = spreadsheetToPDF(key);
  DocsList.createFile(pdf);
}

function spreadsheetToPDF(key) {

  var oauthConfig = UrlFetchApp.addOAuthService("spreadsheets");
  var scope = "https://spreadsheets.google.com/feeds"

  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");    
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");  

  var requestData = {
    "oAuthServiceName": "spreadsheets",
    "oAuthUseToken": "always",
  };

  var name = DocsList.getFileById(key).getName()+".pdf";

  var pdf = UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+key+
                              "&exportFormat=pdf&gid=0&gridlines=true&printtitle=true&size=A4&sheetnames=true&fzr=true&portrait=true&fitw=true", requestData).getBlob().setName(name);

  return pdf;
}

/*
fmcmd=12
size=legal/A4
fzr=true/false
portrait=false/true
fitw=true/false
gid=0/1/2  
gridlines=false/true
printtitle=false/true
sheetnames=false/true
pagenum=UNDEFINED
attachment=false/true  
*/

Hennessey

This information is now outdated, there are no such methods anymore. What are the ways of customizing the pdf when creating it from Sheets now? Appreciate if someone updates. – Fachini