Export Individual Sheet to PDF

Question

EDIT: I FIXED MY ISSUE It turns out it was pulling the function from another .gs file I had as a backup, that I never commented out. Its always the simple things.

I had a Script that I modified and worked well found here but it didn't have any functionality to hide gridlines.

I spent a few hours searching and found this which works really well, but it exports the entire document and not just the individual sheet I need. What do I need to do to get it to export just the first sheet?

//global
        var SS = SpreadsheetApp.getActiveSpreadsheet();
        var Invoice_Sheet = SS.getSheetByName("Private Invoice");

function generatePdf() {
  // Get folder containing spreadsheet, for later export
  var parents = DriveApp.getFileById(SS.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
      // following parameters are optional...
      + '&size=A4'      // 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=0'; //first page
  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
    }
  }
  var Name = Invoice_Sheet.getRange("C12").getValue();
  var curDate = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy")
  var pdfName = Name + " - " + curDate + " - " + "Suds Invoice";
  var response = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/" + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');

  folder.createFile(blob);

}

Marios · Accepted Answer · 2020-11-23T14:03:06.257

Explanation:

You need to make the following two modifications in your code:

You need to specify the spreadsheet ID in the main url:

"https://docs.google.com/spreadsheets/d/"+ ssID

where: var ss = SpreadsheetApp.getActive(); & var ssID = ss.getId();
You need to specify the gid of the sheet you want to extract the pdf from. In your case, given that Invoice_Sheet is the desired sheet, you need to find its gid:

var shID = Invoice_Sheet.getSheetId();

and then add it to the url_ext variable.

In your code the step 2 is already included. But I think the issue is that gid=0 might not be the first sheet or Invoice_Sheet.

Solution:

//global
var SS = SpreadsheetApp.getActiveSpreadsheet();
var Invoice_Sheet = SS.getSheetByName("Private Invoice");

function generatePdf() {
// Get folder containing spreadsheet, for later export
  var parents = DriveApp.getFileById(SS.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  var ssID = SS.getId();
  var shID = Invoice_Sheet.getSheetId();

  //additional parameters for exporting the sheet as a pdf

  var url_ext = "/export?exportFormat=pdf&format=pdf"+
      // following parameters are optional...
      + '&size=A4'      // 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='+shID; // NEW CODE

  
    var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
    }
  }
  
  var Name = Invoice_Sheet.getRange("C12").getValue();
  var curDate = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy")
  var pdfName = Name + " - " + curDate + " - " + "Suds Invoice";
  var response = UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/"+ ssID + url_ext, options); // NEW CODE
  var blob = response.getBlob().setName(pdfName + '.pdf');

  folder.createFile(blob);

}

Note that:

If you don't want to get a pdf of Invoice_Sheet but a pdf of the first sheet, then use the same code and replace:

var shID = Invoice_Sheet.getSheetId();

with:

var shID = ss.getSheets()[0].getSheetId();

Apologies, I had a few variables as global. I've edited the post with the extra variables. I tried the edits you suggested but it is still generating a pdf with all the sheets. — liam gore, Nov 23 '20 at 13:57
@liamgore check my complete solution now. If this does not work, unfortunately I can't reproduce the issue because it works for me. Make sure you are looking at the right folder for the right document. There is a chance you are opening some old documents with all the sheets visible. Make sure you did see the newly generated pdf. — Marios, Nov 23 '20 at 14:03
Ok, it's still not working, I'll just get into the habit of hiding the extra sheets before I run it for now and give it another shot another time. thanks for your help! — liam gore, Nov 23 '20 at 14:16
@liamgore make sure you save the changes after you update a code in the script editor. If you are executing the function `generatePdf` manually then the script is saved automatically. But if you are using it via a custom button menu, then you need to save the changes. I know these are basic stuff but often we overlook some details that make our code behave differently. — Marios, Nov 23 '20 at 14:19

Export Individual Sheet to PDF

1 Answers1

Explanation:

Solution:

Linked