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

}

Answer 1

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();