Why does Google Apps Script return a URL rather than a PDF file when I try to create PDF from a Spreadsheet?

Question

Unlike this recent issue [Not allow to access from one to other Google Spreadsheet ] (Q4-2014), I want to use UrlFetchApp.fetch() rather than [file.getAs(MimeType.PDF)] to convert a single sheet from Google Spreadsheet into a PDF in my Google Drive. UrlFetchApp.fetch() will allow me to choose landscape vs. portrait and several other output control parameters. file.getAs() lacks such formatting options.

My problem is that, using UrlFetchApp.fetch(), I get back a text file containing a URL for the PDF, rather than the PDF itself.

Example of the resulting file:
<HTML><HEAD><TITLE>Moved Temporarily</TITLE></HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Moved Temporarily</H1>">"The document has moved
<A "HREF="https://docs.google.com/spreadsheets/d/1QTXXHEsOd9pZTfXxxxxxxxxxxphO4gn/export?"">"exportFormat=pdf&amp;gid=1&amp;gridlines=false&amp;printtitle=false&amp;size=A4&amp;sheetnames=false&amp;fzr=true&amp;portrait=true&amp;fitw=true">here</A>.
</BODY></HTML>

Here is my code (almost entirely borrowed from Ben Oliver at https://gist.githubusercontent.com/benoliver999/9000157/raw/pdfmachine

function POtoDrive(){
var key = "1QTLGHEsOd9pZTfXM1xxxxxxxxxxxxxxxxxO4gntxPFTutYKQvE"; // Spreadsheet ID to create PDF from.
var pdf = spreadsheetToPDF(key);                          // Runs function below
var folder = DriveApp.getFoldersByName('newPdfs');        // A file does indeed get created in that folder 
folder = folder.next();
folder.createFile(pdf);                                   // Creates the PDF based on var below.
}

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 = {
"method": "GET",
"oAuthServiceName": "spreadsheets",
"oAuthUseToken": "always",
"muteHttpExceptions": true,
};

var doc = SpreadsheetApp.getActiveSpreadsheet(); 
var key = doc.getId();
var sheet = doc.getActiveSheet();
var name = "simplename.pdf";
// Makes PDF 
var pdf = UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+key+"&exportFormat=pdf&gid=1&gridlines=false&printtitle=false&size=A4&sheetnames=false&fzr=true&portrait=true&fitw=true", requestData).getBlob().setName(name); 
return pdf;
}

If I put that URL into my browser, I can download a PDF at that looks good. But, I want the script to actually drop a PDF file into my Google Drive. It's as though I'm getting back a pointer, or getting back a PDF-generating URL, rather than the PDF file itself. How do I get the actual PDF file to be created in my Google Drive?

This is my first question on Stackoverflow, so please be kind about any breaches of etiquette. Thanks. :)

Have a look at this post: stackoverflow.com/questions/21997924/… — Serge insas

Serge insas Serge insas · Accepted Answer · 2014-11-02T18:43:25

Your code does not use the right url for new spreadsheets, below is a working version :

(borrowed from this post)

function test(){
  var id = '16vApdixgE6_________96QXEi1qvD58CSFJw';
  var index = 0;
  var name = 'test.pdf';
  DriveApp.createFile(spreadsheetToPDF(id,index,name))
}


// Convert spreadsheet to PDF file.
function spreadsheetToPDF(id,index,name)
{
  SpreadsheetApp.flush();

  //define usefull vars
  var oauthConfig = UrlFetchApp.addOAuthService("google");
  var scope = "https://docs.google.com/feeds/";

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

  //get request
  var request = {
    "method": "GET",
    "oAuthServiceName": "google",
    "oAuthUseToken": "always",
    "muteHttpExceptions": true
  };

  //define the params URL to fetch
  var params = '?gid='+index+'&fitw=true&exportFormat=pdf&format=pdf&size=A4&portrait=true&sheetnames=false&printtitle=false&gridlines=false';

  //fetching file url
  var blob = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/"+id+"/export"+params, request);
  blob = blob.getBlob().setName(name);

  //return file
  return blob;
}

Why does Google Apps Script return a URL rather than a PDF file when I try to create PDF from a Spreadsheet?

1 Answers