Only send first sheet

Question

I have the following which I got from a forum, which works really but I only want the first sheet created in PDF and Emailed, currently, the whole file is been sent in the .pdf form.

I have tried a few options, but as I am sure you can see, I am no coder. Any help would be really appreciated.

function sendSheetToPdfwithA1MailAdress() { // this is the function to call

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh = ss.getSheets()[0]; // it will send sheet 0 which is the first sheet in the spreadsheet.

  // if you change the number, change it also in the parameters below

  var shName = sh.getName()

  sendSpreadsheetToPdf(0, shName, sh.getRange('C12').getValue(), "Shoalhaven     Hotwater & Elements", "Hi, Please see attached Purchase Order - Shoalhaven     Hotwater & Elements");

}

function sendSpreadsheetToPdf(sheetNumber, pdfName, email, subject, htmlbody) {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var spreadsheetId = spreadsheet.getId()

  var sheetId = sheetNumber ? spreadsheet.getSheets()["Purchase Order     Form"].getSheetId() : null;

  var url_base = spreadsheet.getUrl().replace(/edit$/, '');


  var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf

    +
    (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))

Possible duplicate of [Generate PDF of only one sheet of my spreadsheet](https://stackoverflow.com/questions/49197358/generate-pdf-of-only-one-sheet-of-my-spreadsheet) — tehhowch, Mar 05 '19 at 00:15

score 0 · Answer 1 · answered Mar 04 '19 at 23:45

You need to hide every other sheet before you email it. I do this to create a pdf and then attach the pdf by using the fileID I passed back.:

function toPDF(folderlocation) {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  ss.getSheets()[0].hideSheet();
  ss.getSheets()[1].hideSheet();
  ss.getSheets()[2].hideSheet();    
  var blob = DriveApp.getFileById(ss.getId()).getAs("application/pdf");
  var prettyTime= Utilities.formatDate(new Date(),'America/Los_Angeles', "YYYY-MM-dd" );  
  folder = DriveApp.getFolderById(folderlocation);
  var theBlob = ss.getBlob().getAs('application/pdf').setName('SOME TITLE  '+prettyTime);
  var newFile = folder.createFile(theBlob);    
  ss.getSheets()[0].showSheet();
  ss.getSheets()[1].showSheet();
  ss.getSheets()[2].showSheet();
  return newFile;
}

This Q is the third post of OP with very similar content. Maybe you would like to check out the the possible duplicate of link included as comment to the question. — Rubén, Mar 05 '19 at 03:08

score 0 · Answer 2 · answered Mar 05 '19 at 09:16

Try this:

You just need to create a folder for your pdfs and enter the id for that folder into the code. This function will trash the old pdfs before each one is created. It also hides all the sheets except for the first one. And after creating and saving the file it shows the sheets again.

function savePDF() {
  var ss=SpreadsheetApp.getActive(); 
  var allshts=ss.getSheets();
  for(var i=1;i<allshts.length;i++) {//hide unwanted sheets
    allshts[i].hideSheet();
  }
  var ssBlob=ss.getBlob().getAs('application/pdf').setName('MyPDF');
  var pdfFolder=DriveApp.getFolderById('pdfFolderId');//pdfFolderId
  var files=pdfFolder.getFilesByName('MyPDF');
  while(files.hasNext()){
    files.next().isTrashed(true);//trash oldfiles
  }
  var pdfFile=pdfFolder.createFile(ssBlob);    
  for(var i=1;i<allshts.length;i++) {//show unwanted sheets
    allshts[i].showColumns(i);
  }
  return pdfFile;
}

Only send first sheet

2 Answers2