IssueTracker

Thank you very much for reporting this issue, Jack. Looks like the issue is caused by somewhat unnecessary and contrived logic in apksigner.bat, which is special-casing -J* parameters. Unfortunately, this logic doesn't handle whitespace containing parameters, even those which aren't -J*.

I've uploaded a tentative fix https://android-review.googlesource.com/#/c/394238/ for review. The patched apksigner.bat is attached. This fixes the issue on my Windows 10 box.

Jack, please test the fix in your environment and let us know whether it works for you too.

Confirmed, that fix works. Thank you for the quick turn-around on this.

Project: platform/tools/apksig
Branch: master

commit b293b2087837853e32a1cff008d58644dbbd4cc2
Author: Alex Klyubin <klyubin@google.com>
Date:   Wed May 10 16:53:25 2017

    Handle whitespace when removing -J* parameters in .bat
   
    apksigner.bat contains special logic for -J* parameters. This logic
    was not designed to handle parameters containing whitespace. This
    commit fixes the issue. It is unclear whether this -J* logic is even
    needed. So, if it keeps breaking stuff, we should probably simply
    remove it.
   
    Test: apksigner sign --ks "nonexistent.jks" --ks-pass pass:123123 --ks-key-alias "bug apksigner test" nonexistent.apk
          Fails with FileNotFoundException instead of "a was unexpected at this time"
    Bug: 38132450
   
    Change-Id: Ice3294e9993b075533c77d94eb870cfd35a65bbc

M       etc/apksigner.bat

https://android-review.googlesource.com/394238
https://goto.google.com/android-sha1/b293b2087837853e32a1cff008d58644dbbd4cc2

Project: platform/tools/apksig
Branch: master

commit 0f88b97634034673f062a8ac6c3dab7d3d9befe3
Author: Alex Klyubin <klyubin@google.com>
Date:   Thu Jun 22 09:43:22 2017

    Bump apksigner version to 0.7
   
    Changes since 0.6:
    * Fixed a bug in whitespace handling in command-line parameters in
      apksigner.bat. https://issuetracker.google.com/issues/38132450
    * Fixed a bug in JAR signature verification when multiple digests
      are present for the same named entry in MANIFEST.MF.
      https://issuetracker.google.com/issues/38497270
    * Honor android:targetSandboxVersion (introduced in Android O) when
      verifying APKs. When android:targetSandboxVersion is set to 2 or
      higher, the APK is required to be signed with APK Signature Scheme
      v2.
    * When signing, reject APKs with CR, LF or NUL in ZIP entry names.
      Such names are not permitted by the JAR siging spec and are also
      rejected by Android Package Manager.
   
    Test: apksigner version
    Bug: 38132450
    Bug: 38497270
    Bug: 36426653
    Bug: 62211230
    Change-Id: Ifa120b0e43b458c99c3da6fde1136e0cbb92caee

M       src/apksigner/java/com/android/apksigner/ApkSignerTool.java

https://android-review.googlesource.com/420784
https://goto.google.com/android-sha1/0f88b97634034673f062a8ac6c3dab7d3d9befe3

The fix has been released in apksigner 0.7, released as part of Android SDK Build Tools 26.0.1.

Same issue although document is created in old version of sheets and used to work fine but following trying new sheets it has stopped working.

  var pdf = DocsList.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();

getAs('application/pdf') does nto work in new version.

You can use UrlFetchApp as a workaround.

=====================

function spreadsheetToPDF(doc) {
  SpreadsheetApp.flush();
  var url = "https://docs.google.com/spreadsheets/d/" + doc.getId() + "/export?format=pdf&size=7&fzr=false&portrait=true&fitw=false&gid=" + doc.getActiveSheet().getSheetId() + "&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false";
  var res = UrlFetchApp.fetch(url, googleOAuth_('docs', "https://docs.google.com/feeds/"));
  if (res.getResponseCode() !== 200) {
    return;
  }
  return res.getBlob().setName(doc.getName() + ".pdf")
}

function googleOAuth_(name, scope) {
  var oAuthConfig = UrlFetchApp.addOAuthService(name);
  oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oAuthConfig.setConsumerKey('anonymous');
  oAuthConfig.setConsumerSecret('anonymous');
  return {
    oAuthServiceName : name,
    oAuthUseToken    : "always"
  };
}

not nice :(

That code you wrote works beautifully.

worksheets are now created based on new sheets. Hope that Google is really watching this issue

I tried the code that matj...@gmail.com posted and it resulted in a 302 Redirect (Fine makes sense.).
In trying to work with that I now have the following code. However, the second fetch gives me an OAuth error.
I'm using 2 factor auth, but I've granted access to the information. I don't know what else I need to do.
If anyone has any ideas, that would be awesome. (BTW, I consistently get the 302 issue using several other people's suggested work arounds.)

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 = {
    "muteHttpExceptions" : true,
    "oAuthServiceName": "spreadsheets",
    "oAuthUseToken": "always",
  };
 
  var name = DocsList.getFileById(key).getName()+".pdf";
 
  var pdf = null;
  try {
    pdf = UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+key+"&exportFormat=pdf&gid=1&gridlines=0&printtitle=0&size=7&fzr=true&portrait=1&fitw=1", requestData).getBlob().setName(name);
    var c = pdf.getDataAsString();
    if(c.indexOf("Moved Temporarily")>0){
      var test1 = c.match("HREF(.+)>")[0].substring(6);
      var n = test1.indexOf(">")-1;
      var test2 = test1.substring(0,n);
      pdf = UrlFetchApp.fetch(test2, requestData);
    }
  } catch (e) {
    var error = e.message;
  }
  return pdf;
}

Google, this is a bug --- Please :-(

getBytes() can´t serialize the pdf blob. Confirmed.

Previous sheet is still working - no server error.

Please, Google, this is a very very bad bug
All our automated reports dont wor'k anymore.

I started having this issue only yesterday.
We updated to new sheets about a month ago and was working flawlessly until this moment.

We convert Spreadsheet Reports to Pdf via blob and then send them via email.

I will try to make it run in the old version.

Do you guys have any prediction when do you intend to solve this issue on your road map ?

Another workaround I use and which currently fits my needs :
create a spreadsheet in the old version from https://g.co/oldsheets, save the id in a global variable. And then use it as a temporary file instead of creating new spreadsheets.

I just made it work using the old version and as @jeritiana said:

One old spreadsheet that serves as cache file for report modifications and blob serialization.

Thank you and I hope Google fixes this soon.

We're still working on resolving this issue, and we thank you for your patience.

Similar to other workarounds that use UrlFetchApp, here is some code that uses the Advanced Drive service (http://goo.gl/NihrA8) to get the PDF export link for the spreadsheet and download its contents.

function workaround() {
  var spreadsheetId = 'SPREADSHEET ID HERE';
  var file = Drive.Files.get(spreadsheetId);
  var url = file.exportLinks['application/pdf'];
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });
  var pdf = response.getBlob();
}

relatively new to google scripts. I am trying to convert the active spreadsheet to pdf and then send it as an email. all day i have been getting this error 'We're sorry, no servers are currently available. Please wait a bit and try again. (line 20, file "Code")'

can someone tell me what is wrong with my script. no other scripts are being used in the spreadsheet except for this one:


function emailss() {
  var originalSpreadsheet = SpreadsheetApp.getActive();
  var message = "Please see attached";
 

  var subject = 'Weekly Status Sheet';
 
     

  var emailTo = 'padjo.osullivan@gmail.com';
 

  var newSpreadsheet = SpreadsheetApp.create("ss");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var projectname = SpreadsheetApp.getActiveSpreadsheet();
  sheet = originalSpreadsheet.getActiveSheet();
  sheet.copyTo(newSpreadsheet);
 
 
  var pdf = DocsList.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:'Weekly Status.pdf',content:pdf, mimeType:'application/pdf'};
 
  // Send the freshly constructed email
  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
 
  // Delete the wasted sheet we created, so our Drive stays tidy.
  DocsList.getFileById(newSpreadsheet.getId()).setTrashed(true);  
}

Is there a way to find updates on this issue, if google will fix it or supply another method, I have come to a complete stop because of this and the manual work around is time consuming as multiple users sending from one spreadsheet. I cant just put hacks in left right and center i need a workable solution.


function DREPORT() {
  var originalSpreadsheet = SpreadsheetApp.getActive();  
  {
    Browser.msgBox('Report Sending.Click OK To Finish');

  var message = "Please see attached";  
  // warok area
  var projectname = originalSpreadsheet.getRange("A1:A2").getValues();
  // managers name
  var period = originalSpreadsheet.getRange("B2:C2").getValues();
  // subject
  var subject = projectname + " - Health Check - " + period;
 
  // email grouped
  var contacts = originalSpreadsheet.getSheetByName("Contacts");
  var numRows = contacts.getLastRow();
  var emailTo = contacts.getRange('D1:D10').getValues();

  // temp spreadsheet
  var newSpreadsheet = SpreadsheetApp.create("Spreadsheet to export");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var projectname = SpreadsheetApp.getActiveSpreadsheet();
  sheet = originalSpreadsheet.getActiveSheet();
  sheet.copyTo(newSpreadsheet);

  // delete temp sheet
  newSpreadsheet.getSheetByName('Sheet1').activate();
  newSpreadsheet.deleteActiveSheet();

  // Make PDF called "Check"
   /////// THIS LINE BELOW IS THE ISSUE AS I THINK OTHERS HAVE STATED
  var pdf = DocsList.getFileById(newSpreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:'heathcheck.pdf',content:pdf, mimeType:'application/pdf'};

  // send email
  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
  // Delete the wasted sheet created
  DocsList.getFileById(newSpreadsheet.getId()).setTrashed(true);  
}
}

Google this is not a me too, its a plea for support or updates.

Thanks

USER DLR

I've had great success combining suggestions from #7 and #22 by building the URL 'manually' (so the advanced Drive API doesn't have to be enabled thru the dev console) and having ScriptApp provide the token.

This method doesn't use the second authorization pop-up from .fetch, meaning that this can be run from within a Library function and/or called from a custom Spreadsheet UI menu without running into authorization errors.

See below ...

*************************************************

function savePDFs() {
  SpreadsheetApp.flush();
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
 
  var url = ss.getUrl();
 
  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/,'');
 
  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
  //below 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
  '&gid=' + sheet.getSheetId(); //the sheet's Id
 
  var token = ScriptApp.getOAuthToken();

  var response = UrlFetchApp.fetch(url + url_ext, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
   
  var blob = response.getBlob().setName(sheet.getName() + '.pdf');

  //from here you should be able to use and manipulate the blob to send and email or create a file per usual.
  //In this example, I save the pdf to drive
 
  DocsList.createFile(blob);
  //OR DriveApp.createFile(blob);
}

*************************************************

I would definitely recommend setting fzr=false and gid=yoursheetID in the URL because, in my experience, ether printing out the whole spreadsheet or repeating row headers on each page have caused formatting errors if my spreadsheets have certain configurations of merged cells.

Again, the neat things about this method are:
- No scary second authorization pop-up that just says ANONYMOUS caused by the oathConfig method
- No (so far) authorization errors caused by the oathConfig method when changing Service names or when contained within a library function
- No enabling the Drive API from the dev console

[Comment deleted]

Thanks ianshedd...@gmail.com your code has worked very well but I cannot get it to run if it is from a programatically added time driven trigger.

There is a comment on this page
https://developers.google.com/apps-script/execution_time_triggers
which says "Note: Time-driven triggers cannot be used in add-ons."

Does that mean I cannot use your code or have I made some other mistake?

When using the workaround ekoleda+devrel@google.com and ianshedd...@gmail.com provided;

 var token = ScriptApp.getOAuthToken();
    var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

I get the following error as of today (last week it worked perfectly);

Failure: Exception: Request for https://docs.google.com/spreadsheets/d/xxx failed. Errorcode: 404.

UrlFetchApp is trying to fetch the non-authorized page. The link, however, is correct. When opening the generated PDF link manually, I can download the PDF. Probably something in the Authorization part changed?

Any news about this bug? Is this a intermittent bug?

I was coding and testing a script to send Docs as PDF along today and is was working fine, but now I getting this error.

Not only SpreadSheets can't be attached as pdf, but I'm also getting this bug trying to attach a Document as a PDF.

I test my workaround code again, and it's still working. Are you still getting 404 errors?

I included the script (ianshedd...@gmail.com) in a library. In one account where I included the script, it is working. However, In another account (all the settings are the same), the script is fetching a 404 page (Drive file doesn't exist). In both accounts, the script is fetching exactly the same URL. What could explain this strange behaviour?

If you use Erics workaround make sure you enable the Advanced Drive service (http://goo.gl/NihrA8) first.

I should work without the Advanced Drive service, but even when I enabled it (also in the console), it still doesn't work.

#31 anal...@oogstonline.nl,
I also have the script in a library and it works fine for me using multiple accounts. Do both accounts have editor-level access to the spreadsheet?

Maybe try copying the url the script makes by showing it in an alert or from a log and triple-check the url generated is the same for both users, then paste the built url into the address bar as each user and seeing if that works on both accounts.

I have not been able to replicate a 404 error using the workaround unless there's a typo in the URL, or the spreadsheet's Id or sheet's ID are invalid...

The URL is exactly the same. When I open the URL that gives the 404 in a new window, it downloads the PDF. I modified the script, so that it isn't running from a library, but directly. Created two new files. In the first file, the script is working, in the second the script gives a 404. Very strange, could it be something with the authentication?

Keep in mind that solution only works if Drive or DriveApp appears somewhere in the script, so that the Drive OAuth scopes are requested and can be passed along. Check to ensure this is true for all scripts.

Nice catch!

That was it! Thanks!

@ekoleda:

Hi. I tried your workaorund from #22.

But the function "exportLinks" doesn't seem to exist in the class File.

Error: Property "application/pdf" can't be read from undefined

heres the according code:

 var file = DriveApp.getFileById(newSpreadsheet.getId());
     
  var url = file.exportLinks['application/pdf'];
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });
  var pdf = response.getBlob().getBytes();
  attach.push({fileName:PDF_NAME,content:pdf, mimeType:'application/pdf'});

@dr.netdoktor, please read my code more closely. The exportLinks field is only present when using the Advanced Drive service. In your code you are using DriveApp, which doesn't have that field.

@dr.netdoktor, I had the same issue. This should help:

https://developers.google.com/apps-script/guides/services/advanced#enabling_advanced_services

I moved an old app to the new sheets and was able to get it working using Eric's workaround.

Being able to create a report and email it to someone is a very useful technique, we use it a lot. It is a big problem that this has suddenly stopped working (when new documents became NEW documents). It is all very well to come up with a work-a-round but the real solution is to encourage Google to fix the bug (which they have known of for at least 4 months.

In the interim I would prefer if I could create new documents in the OLD format as this will be far easier to patch in. I certainly could not consider using Google Docs for anything really serious when working production systems can be broken by upgrades outside my control. Users are not interested in excuses they just complain when things are broken and they in turn are getting blamed by their 'customers'.

In my work around (#18) I had this link: https://g.co/oldsheets to create new spreadsheets in the OLD format. Does a kind person here have an idea how to get the generated url programmatically? That would be a nice solution to this issue too.

#25 ianshedd...@gmail.com, this looks exactly like what I've been looking for but it doesn't quite work. It works from the address bar but not from the Script editor or a button on a sheet, its final destination.

The contents of the PDF is evidently docs.google.com/index.html. Why?

And once I get it to save correctly from the button, I would like it to download the file rather than save it if possible.

My purpose for this is to supply export parameters, bypassing the print icon, so the exportLinks workaround won't do.

#25 ianshedd...@gmail.com
Sorry it's not working right for you.

Can you provide a code example? The code I provided works for me as-is in new sheets...

Also, I don't know how (or if it's possible) to generate a download dialog prompt programmatically.

#46 ianshedd...@gmail.com

Success! Using the response and blob vars unmodified as I had it made it work. I had introduced two silly defects. Thanks!

And the closest I've found to programmatically downloading a generated PDF are with a 1. hyperlink, and 2. createAnchor in a UiApp.

Hyperlinks can't be called from a button and its target can't be changed (Issue 36754539). But createAnchor can be called from a button, the target can be changed (to _self), although a blank tab is temporarily opened, but unfortunately, the PDF apparently can't be renamed when downloading, which would be nice.

#47 burstein...@gmail.com
Nice!

re: "the PDF apparently can't be renamed when downloading, which would be nice."
What browser are you using? For example (in my experience), in Firefox it downloads automatically, but in Chrome the standard 'save as' dialog prompt displays first, which would let you rename and pick the download destination.

can we have  an update on this issue asap, we are blocked in production !

We apologize for the delay. This issue should now be corrected.

Not fixed for me unfortunately. I think it may depend on the content of the file because it works if I make a simple document but not with a more complex one.

function getPDFofFile2(fileID) {
  //
  // Google say they have fixed it
  // Let us try with a doc I made public
  var theFile = DocsList.getFileById('1fAJ_Vdod-3bDcuGTZdfS4aoBmijsb-S9rqEiPQhcauQ');
  // No - This fails
  //
  // If we use this file created 1st April 2014 it works but I cannot make this public
  // var theFile = DocsList.getFileById('1bpgvIRrKhhQzdiDtZk46Qj8KBt7NNyzvkMHYQAsogvk');
  var thePDF = theFile.getAs("application/pdf").getBytes();
  return thePDF;
 
}

gordon@, this issue deals specifically with conversion of Sheets to PDF.  You example is a Doc.

There is a related Doc to PDF conversion issue being tracked here:

https://code.google.com/p/google-apps-script-issues/issues/detail?id=3495

and a temporary workaround solution here:

https://code.google.com/p/google-apps-script-issues/issues/detail?id=3495#c45

Basically, the bug there is that empty table objects in the Doc can foul the PDF conversion.  The workaround solution above includes a cleaner function to remove empty tables from the doc -- once you run that, you'll be able to convert the Doc to PDF

Hi, Not fixed for me. Could please test below and advise. Thanks

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 Options = "&gridlines=0&printtitle=false&size=A4&fzr=false&portrait=true&fitw=true";
  var pdf = UrlFetchApp.fetch(scope + "/download/spreadsheets/Export?key=" + key + "&exportFormat=pdf" + Options, requestData).getBlob().setName(name);
  return pdf;
}

Thanks ryanr...@google.com for your #52 answer. You are spot on, the empty table element (I assume only when it is in a New form Doc) is causing my failure. I have run the fix to remove the empty table from my template document and so all the documents generated from it are now fine, I have not had to make any changes to production software to resume working which is very good.

I think it is useful to have a cross link to the other issue, they are all faults in the DocList.getAs method and 3495 did not come out when I searched and that could also happen to others.

Well, it took some time but it works fine now... thank you Google guys :-)

Jaime

ianshedd...@gmail.com,

Your workaround #25 worked beautifully for me for the last few months. I've now started to convert to the New Sheets and had to modify slightly using #22 ekoleda+devrel@google.com workaround, but now I cannot use custom options?? This is very important that I specify &portrait=false as it reduces the page count drastically.

Please tell me you have a solution!!!!


Sean.

sean,
My solution works in new sheets as well so give that a shot if you still want to have control over the way the pdf looks.

As far as I know there isn't a way to customize the export options when using file.getAs() methods.

[Comment deleted]

I suppose you could try replacing this line:

    'exportFormat=pdf&format=pdf' +

with this:

    'exportFormat=xlsx&format=xlsx' +

and then add '.xlsx' to the end of the filename instead of .pdf

Exporting that way seems to work but I don't know what it will look like when opening it in excel.

[Comment deleted]

Thanks! That actually worked out pretty well!

How would I export "All Sheets" to .pdf in one attachment? Here's what I
have been working with:


function submit() {
  SpreadsheetApp.flush();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //var sheet = ss.getSheetByName('Test'); //Use this if you want to
specify a certain sheet
  var url = ss.getUrl();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/,'');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
  //below parameters are optional...
  '&size=letter' + //paper size
  '&portrait=false' + //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' + //false = hide gridlines
  '&fzr=false' + //do not repeat row headers (frozen rows) on each page
  '&gid=' + sheet.getSheetId(); //the sheet's Id

  var token = ScriptApp.getOAuthToken();

  var response = UrlFetchApp.fetch(url + url_ext, {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

  var blob = response.getBlob().setName(sheet.getName() + '.pdf');
  var CopyDate = Utilities.formatDate(new Date(),
"GMT-3","M"+"/"+"d"+"/"+"yyyy"); // Funtion Date + Format
  var user = Session.getActiveUser().getEmail();



My spreadsheet has about 7 sheets on it, but this is only grabbing the
active sheet, where instead I need them all. I've tried

var sheet = ss.getSheets();

but this returns an error when trying to get the "SheetId" for all sheets.
I've also tried getting the sheets by name, but I can only call 1 sheet
name.

The only progress I've been able to make on this is to grab 2 separate
blobs and attach them both, but then I get 2 attachments instead of
combining them into 1.

You're a true wizard and thanks for the dedication to helping us all.

Sean.

ss.getSheets returns an array of sheets, so your best bet is to use a 'for' loop, calling .fetch on each sheet in your spreadsheet and storing the blobs in an array.

So we end up with something like this:

******************************************
function submit() {
  //DriveApp; authorization for drive app needed, works even when commented out like this.
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
 
 
  //this is an array, get individual sheets by using sheets[index]
  var sheets = ss.getSheets();
 
  var url = ss.getUrl();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/,'');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
  //below parameters are optional...
  '&size=letter' + //paper size
  '&portrait=false' + //orientation, false for landscape
  '&fitw=true' + //fit to width, false for actual size
  '&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
  '&gridlines=false' + //false = hide gridlines
  '&fzr=false' + //do not repeat row headers (frozen rows) on each page
  '&gid='; //leave ID empty for now, this will be populated in the FOR loop
 
  var token = ScriptApp.getOAuthToken();
 
  //make an empty array to hold your fetched blobs  
  var blobs = [];
 
  //.fetch is called for each sheet, the response is stored in var blobs[]
  for(var i = 0; i < sheets.length; i++) {
    //grab the blob for the sheet
    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
   
    //convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');
  }
 
  //create new blob that is a zip file containing our blob array
  var zipBlob = Utilities.zip(blobs).setName(ss.getName() + '.zip'); //zipBlob is now a blob that you can email, save as a file to drive, etc..
 
  //optional: save the file to root level of Drive
  DriveApp.createFile(zipBlob);
}

******************************************

So the main differences are:
* do NOT include the sheet.getSheetId() at the end of url_ext, but still have the '&gid='. The id is added inside the for loop
* UrlFetchApp.fetch is now in a for loop. In the for loop, we .fetch using ( url + url_ext + sheets[i].getSheetId ) as the url for each sheet
* store the response from .fetch into an array of blobs
* Utilities.zip(blobs) converts an array of blobs into a single blob zip file

Let me know if that works!

Actually, if there are sheets that you DON'T want zipped up use this for loop instead:

  for(var i = 0; i < sheets.length; i++) {
 
    var sheetname = sheets[i].getName();
 
    //if the sheet is one that you don't want to process,
    //continue' tells the for loop to skip this iteration of the loop
    if(sheetname == "Name of sheet I don't want" ||
        sheetname == "Name of another sheet I don't want")
      continue;
 
    //grab the blob for the sheet
    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
   
    //convert the response to a blob and store in our array
    blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));
  }

**

using blobs.push won't give you problems later when zipping if you decide to skip over sheets

Ha, that indeed worked like a charm!! Thank you very much!

I'm hoping that sometime very soon Google will allow the export customization when using:

var blob = allsheets.getAs('application/pdf');

It's convenient to get an email with all of your sheets in 1 single .pdf, but also my sheets need to be in landscape layout.

You're zip script will certainly do the trick for now though!

Sean.  

You're welcome! And I hope it gets simpler someday too, but at least for now there are ways to make it work!
Cheers,
Ian

#25 ianshedd...@gmail.com FTW! Thank you!!!

I'm using the ianshedd script with the for loop and the function sheetname to avoid some sheets it's not working, all the sheets are processes in the zip file and all the files inside the zip are .html instead of .pdf, can anyone tell me why.

Thanks.

Using the ianshedd script above, is there a way to bypass the zip file and only get the .pdf that is inside? Or a script to automatically unzip it? Also how would one specify a folder to save said file in?

@texas,

Keep in mind the result of both .getBlob() and Utilities.zip(array_of_blobs) are still a blob. You can use DriveApp.createFile(any_blob) to create a file in drive using a blob.

So if you don't want a zip, then just don't do that part. In my example from #63, you can add a bit at the end of the inside of the last for loop:

*************************************
//convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');

//this is new
    DriveApp.createFile(blobs[i]);

*************************************

If you want to specify a specific folder, you have a few options. If you know the folder's id (which I *think* is the last part of the folder's url when opened in Drive), you can use something like:

*************************************
var specified_folder = DriveApp.getFolderById(folder's_id) //stores the folder in a variable

forloop from the example... {
    ...stuff stuff stuff...

//convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');

//this is new
    specified_folder.createFile(blobs[i]);
}
*************************************

If you don't know the folder's id, there are a few methods in DriveApp that let you create new folders, find folders by name, or iterate through your drive's folders. Check those out in the documentation here: https://developers.google.com/apps-script/reference/drive/drive-app#getFolderById(String)

Thanks for the help! Although I seem to be getting an error along the lines of "Cannot find method createFile((class)).".

Here's what my code looks like now:

// Create custom menu within Spreadsheet
function onOpen() {
  var submenu = [{name:"Save PDF", functionName:"submit2"}];  
  SpreadsheetApp.getActiveSpreadsheet().addMenu('My Functions', submenu);
}




//This Saves as a Zip

function submit2() {
  //DriveApp; authorization for drive app needed, works even when commented out like this.
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
 
 
 
 
  //this is an array, get individual sheets by using sheets[index]
  var sheets = ss.getSheets();
 
  var url = ss.getUrl();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/,'');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
  //below parameters are optional...
  '&size=legal' + //paper size
  '&portrait=1' + //orientation, false for landscape
  '&fitw=true' + //fit to width, false for actual size
  '&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
  '&gridlines=false' + //false = hide gridlines
  '&fzr=false' + //do not repeat row headers (frozen rows) on each page
  '&gid='; //leave ID empty for now, this will be populated in the FOR loop
 
  var token = ScriptApp.getOAuthToken();
 
  //make an empty array to hold your fetched blobs  
  var blobs = [];
 
  //.fetch is called for each sheet, the response is stored in var blobs[]
  for(var i = 0; i < sheets.length; i++) {
   // grab the blob for the sheet
    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
   
    //convert the response to a blob and store in our array
    blobs[i] = response.getBlob().setName(sheets[i].getName() + '.pdf');
  }
 
 
  // alternate for Loop for No 'Completed' sheet
  //for(var i = 0; i < sheets.length; i++) {
  //  var sheetname = sheets[i].getName();
    //if the sheet is one that you don't want to process,
    //continue' tells the for loop to skip this iteration of the loop
  //  if(sheetname == "Completed")
    //  continue;
    //grab the blob for the sheet
  //  var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
  //    headers: {
  //      'Authorization': 'Bearer ' +  token
   //   }
  //  });
    //convert the response to a blob and store in our array
   // blobs[i] = blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));
 //


}
 
 
 
 
 
  //Specify Website Documents Folder in Drive

  var specified_folder = DriveApp.getFolderById("FOLDERID");

  //optional: save the PDF to Drive
  specified_folder.createFile(blobs[i]);

}





Also would I still be able to use this with your alternate for loop (the one that only saves a specifically named sheet)? It looks like you're just using Push there and not assigning the blob to anything.

Basically, I want to save the first sheet of a spreadsheet (using print parameters) to a specified folder in the drive. I've been searching for this for awhile and can't seem to find a simple fix, and its not a crazy feature to ask for haha!

Thanks again.

Alright I seemed to figure it out, this code does exactly what I am looking for, hope it helps someone:

// Create custom menu within Spreadsheet
function onOpen() {
  var submenu = [{name:"Save PDF", functionName:"submit"}];  
  SpreadsheetApp.getActiveSpreadsheet().addMenu('My Functions', submenu);
}

//This saves as a PDF

function submit() {
  //DriveApp; authorization for drive app needed, works even when commented out like this.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
 
 
 
 
  //this is an array, get individual sheets by using sheets[index]
  var sheets = ss.getSheets();
 
  var url = ss.getUrl();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/,'');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
  //below parameters are optional...
  '&size=legal' + //
  '&portrait=1' + //orientation, false for landscape
  '&fitw=true' + //fit to width, false for actual size
  '&sheetnames=false&printtitle=false&pagenumbers=false' + //
  '&gridlines=true' + //
  '&fzr=false' + //do not repeat row headers (frozen rows) on each page
  '&gid='; //leave ID empty for now, this will be populated in the FOR loop
 
  var token = ScriptApp.getOAuthToken();
 
  //make an empty array to hold your fetched blobs  
  var blobs = [];
 
  //.fetch is called for each sheet, the response is stored in var blobs[]
  for(var i = 0; i < sheets.length; i++) {
    var sheetname = sheets[i].getName();
    //if the sheet is one that you don't want to process,
    //continue' tells the for loop to skip this iteration of the loop
   if(sheetname == "Completed")
      continue;
   //grab the blob for the sheet
    var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });
    //convert the response to a blob and store in our array
    blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));
    var array_blob = response.getBlob().setName(sheets[i].getName() + '.pdf');
  }
 
 
  //Specify Website Documents Folder in Drive
  var specified_folder = DriveApp.getFolderById("FOLDERID");
 
  //optional: save the PDF to Drive
  specified_folder.createFile(array_blob);
 
 
}

If you only want to do the first sheet, you don't need a for loop at all, since the purpose of the for loop is to loop thru each sheet and perform the pdf conversion. "sheets[0]" is the first sheet in your spreadsheet.

Your commented-out alternate code doesn't work because of it's last line:
blobs[i] = blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));

should just be:
blobs.push(response.getBlob().setName(sheets[i].getName() + '.pdf'));

blobs.push(element) adds the element to end of the array;

Lastly, you can check to make sure that:
var specified_folder = DriveApp.getFolderById("FOLDERID");
is actually getting a folder by logging specified_folder.getName() and making sure the folder's name shows up in the log.

Looks like you figured it out. Never mind!

I'm getting a "Request Error" and in the details it's hanging on the additional params code

var response = UrlFetchApp.fetch(url + url_ext + sheets[i].getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }


Any thoughts? Am I missing something?

Sean.

seansug,

I think one of the devs mentioned it somewhere in the thread, but you probably don't have DriveApp somewhere in your code. Even adding it in a comment, like "//DriveApp" will trigger additional authorizations needed to make it work.

Give that a try!

I still keep getting the error?? I have Drive.app listed in the code, but still not working.

Errrr, frustrating.

seansug, make sure you have DriveApp (not Drive.app).  There is no dot (.) and it is case sensitive (App not app).

I'm trying to implement the code from #25, as it does what I need (control the format of the PDF: landscape, no grid lines, etc.)

However, it  fails at the UrlFetchApp because it doesn't like the URL which is constructed. I don't understand how url_ext starts with "export" without any special character. When I look at it, the "export" gets appended to the fileid, and google can't find the file.

What is the full URL supposed to look like?

fhsmith

The url from the spreadsheet's getUrl() ends in [Spreadsheet ID]/edit (maybe this is just for users that can edit the sheet?). So after removing the 'edit', the proceeding slash should still remain.

So after that, the url should end up like:
https://docs.google.com/spreadsheets/d/[Spreadsheet ID]/

Combined with the export parameters, the url should end up like:
https://docs.google.com/spreadsheets/d/[SpreadsheetID]/export?exportFormat=pdf&format=pdf&size=letter&fitw=false&portrait=true&sheetnames=false&printtitle=false&gridlines=false&fzr=false&pagenumbers=false

And if you replace {SpreadsheetID] in that last link above with your spreadsheet's ID, you should be able to paste that URL into your browser and get a 'save-as' dialog box.

Thanks for your quick response, Ian, but still no joy.

My getUrl comes back with:
https://docs.google.com/spreadsheet/ccc?key=[File ID]

Note that "spreadsheet" is singular, the sub-folder(?) is "ccc", and the FileId is a parameter, not another sub-folder.

I have experimented with putting the "export?exportFormat ..." in various places, but can't get anything to work.

Any ideas?

Ah, it looks like your spreadsheet may still be the old-style spreadsheet (no green checkmark at the bottom-right?). That may be causing your problems. See this link regarding moving your spreadsheet to the new-style spreadsheets if that's the case: https://support.google.com/docs/answer/3544847?hl=en

Thanks for the response. At least I now know the problem. I've checked into manually converting before, but my file has over 50 sheets in it, so would take me a while and I'd run the risk of not getting all the formulas right.

I'll leave it until Google gets around to converting it.

Thanks again.

I've followed the different approaches suggested in here ... and I DO get a pdf file out of a sheets document. The problem is, that my PDF file is empty (white page with no tekst). I can see that the page count fits with the need.
I have also created my script, so that the "final" concatenated URL is written into a google sheet before the UrlFetchApp is trying to fetch the file. So .... the "UrlFetchAPP" version of the PDF file is "blank pages" but the URL copied into my browser returns a PDF file with content as expected!!!!

HELP!!!! (Yes I AM working on "version 2" of the sheets).

How are you programmatically creating the file in Drive? Are you converting the fetched response to a blob? Using DriveApp.createFile(blob) or DriveApp.createFile(name, content)? Or another way?

Just wanted to add a comment and let folks know that I used the code from #25 with success.

https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579#c25

Without knowing there were any issues with this technique i have successfully implemented a mechanism to create and send a summary spreadsheet report. I created a summary sheet in the Spreadsheet (worked it so it would fill one page) I set up an event timer to generate the pdf and email the page to selected users on a daily basis.  It has worked wonderfully so far.  

I am not seeking to modify it a bit and limit the print area (simulating the 'print selection' choice in the print dialog).  I added to the url ahead of the "&gid=" + sheetNo entry.
        "&r1=" + r1 + "&c1=" + c1 + "&r2=" + r2 + "&c2=" + c2;
where the variables r1...c2 are the edges of the selection rectangle from the active range.  Command works but it doesn't print the selection, only the whole page.  The URL is constructed correctly but the addition is ignored.  There is probably an additional switch required in the url  With the new sheets I can't see the URL generated from the print request.  How to I trap this from a manual print request so I can see how it might be done?

The following works for me to print a specific RANGE from a a sheet. I could not find out if there are any parameters you can add to the UrlFetchApp spreadsheet export to pick out a specific block of cells so I gave up and made this work-a-round. You get a PDF Blob back which you can for example email like this

var aDocPDF = makePDFofRange(ss,sourceRangeName,targetSheetName,portraitInd);
emailParms.attachments = {fileName:attachmentFileName, content:aPDF,
                             mimeType:"application/pdf"};



function makePDFofRange(ss,sourceRangeName,targetSheetName,portraitInd) {
  //
  // This returns a PDF of a Range named in sourceRangeName
  // The report is formatted up onto a scratch sheet named
  // in targetSheetName
  // portraitInd is text true/false
  //
  //
  var sourceRange = ss.getRangeByName(sourceRangeName);
  var targetSheet = copyRangeToSheet(sourceRange,targetSheetName);
  //
  // get the PDF
  //
  return makePDF(targetSheet,sourceRangeName,portraitInd);
}

function copyRangeToSheet(sourceRange,targetSheetName) {
  //
  // This copies the contents of a Range to a Sheet named
  // by targetSheetName
  //
  var sourceSheet = sourceRange.getSheet();
  var ss = sourceSheet.getParent();
  var targetSheet = ss.getSheetByName(targetSheetName);
  // If the target sheet does not exist create it
  // if it does exist clean it up
  if (targetSheet == null) {
    targetSheet = ss.insertSheet(targetSheetName);
  }
  else {
    // clean up an existing sheet
    // before we clear anything there unmerge any cells
    targetSheet.getDataRange().breakApart();
    targetSheet.clear();
    //
    // Make sure there are enough columns
    // maybe later, not sure if we need this
  }
  // copy the range content to the sheet
  //
  var targetRange = targetSheet.getRange(1,1,sourceRange.getNumRows(),sourceRange.getNumColumns());
  //
  sourceRange.copyTo(targetRange, {formatOnly:true});
  sourceRange.copyTo(targetRange, {contentsOnly:true});
  //
  // The formatting does not include the column widths
  // so we need to set them
  var sourceColOffset = sourceRange.getColumn() - 1;
  var numCols = sourceRange.getNumColumns();
  for (var i = 1; i<numCols+1; ++i) {
    var width = sourceSheet.getColumnWidth(i + sourceColOffset);
    targetSheet.setColumnWidth(i, width);
  }
  return targetSheet;
 
}


function makePDF(printSheet,docName,portraitInd) {
  //
  // returns a PDF of the given sheet
  //
  //
  var oauthConfig = UrlFetchApp.addOAuthService("spreadsheets");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  var scope = "https://spreadsheets.google.com/feeds";
  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 docKey = printSheet.getParent().getId();
  var printSheetIndex = printSheet.getSheetId();
  //
  // Trim the sheet to length
  //
  var lastRow = printSheet.getLastRow();
  var maxRow = printSheet.getMaxRows();
  var maxCol = printSheet.getMaxColumns();
  var lastCol = printSheet.getLastColumn();
 
  if (maxCol > lastCol)
    printSheet.deleteColumns(lastCol + 1, maxCol - lastCol);
  if (maxRow > lastRow)
    printSheet.deleteRows(lastRow + 1, maxRow - lastRow);
 
 
  var pdfBlob = UrlFetchApp.fetch("https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="
               +docKey
               +"&exportFormat=pdf&gid="
               +printSheetIndex
               +"&gridlines=true&printtitle=false&size=A4&sheetnames=false&fzr=true&portrait="
               +portraitInd
               +"&fitw=true"
               , requestData).getBlob().setName(docName);
 
  return pdfBlob.getAs("application/pdf").getBytes();
}

Copying to another sheet is certainly an option.  I think I found what I was looking for:
Playing with an old sheet I see that the first command is "&fmcmd=12".  I don't know what it does, exactly, but when I added this to the front of the command I got what I wanted and just the range I asked for printed out.

To recap:  use all the headers already discussed but add a line that spells out what the range you want to print and prefix (as with most REST commands where it is doesn't matter) it with "&fmcmd=12".  I wrote a short little routine to spit out the print range:

/** return, in r1c1, notation the range in the sheet provided
* @param {Range} range is the range to return the text r1c1 notation required for web access
* @return {String} the string ready to be appended to the web url
*/
function getSelection( range ) {
  var ss = range.getSheet();
  ss.setActiveRange( range);
  var c1 = range.getColumn()-1;
  var r1 = range.getRow()-1;
  var c2 = range.getLastColumn()-1;
  var r2 = range.getLastRow()-1;
   
  return "&r1=" + r1 + "&c1=" + c1 + "&r2=" + r2 + "&c2=" + c2;
}
This routine assumes you have already set the active selection in the current sheet you want to print.

Discussion on this thread has veered on to other topics. I'm closing it to prevent further comments. Please refer to our support page for additional information on how to get your questions answered:

https://developers.google.com/apps-script/support

Hello,
It seems that the error got back. Please find below my code that generated such errors. It works for some of my array and then suddenly happen :
for (i=0;i<arrayOfIndexNumbers.length;i+=1) {
    thisID = arrayFileIDs[arrayOfIndexNumbers[i]];
    var file = DriveApp.getFileById(thisID);
    var size = file.getBlob().getBytes().length;
    count = Number(count)+Number(size);
  };
The goal is to mesure size of documents even google one (whereas we can't get google documents size, app script return 0)
May I ask you if you have a solution ?

I have the same error trying to parse a CSV:

var file = DriveApp.getFilesByName(fileName).next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString(),";");

I have the problem with googleDocs

I have a DocsDocument named copyId
When I convert it to PDF it works, but the script brake when I try to save it on a folder:


var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");       ---   Still work
var folderToStore = DriveApp.getFolderById("xxxxxxx");
var file = DriveApp.createFile(pdf).moveTo(folderToStore);              ---   Brake here