Google Sheet to PDF Get Only Active Sheet/Tab

user_14526623 0
Viewed 175

This script converts pdf in inside the folder which is fine.

enter image description here

There are three tabs on this sheet when the function run.
Merge all the tab in pdf and save in the drive folder with active sheet tab name

var pdfName = "Invoice " + sheetName.getRange("d6").getValue();

What I want

When I run the script only active tab/sheet convert in pdf. and not marge all tab in pdf.

function PDF() {
  var sheetName = SpreadsheetApp.getActiveSpreadsheet();
  var folderID = "1_gA4D7ybJ60dfgsdfgdsfInqVoo9D769cqmAnJI7g7";
  
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1jBl_82etnHsdfgsdfgsdfgsdgcjaCpcTG6VvhKYNNrQQXJMUUOQkFGfORY/edit#gid=1167579479');
  
  var pdfName = "Invoice " + sheetName.getRange("d6").getValue(); 
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID);
  //Copy whole spreadsheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
  //delete redundant sheets
  var sheets = destSpreadsheet.getSheets();
  
  //save to pdf
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);
  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
  
}
google-apps-script google-sheets javascript
1 Answers

Explanation:

  • Your current solution is not the proper way to create a pdf of a sheet. You need to use getOAuthToken() and Class UrlFetchApp.

  • Also I improved your script in many different ways. You are defining variables you are not using anywhere. And also you don't need to create a copy of the spreadsheet file and then delete it. You can simply create a pdf file of your source spreadsheet directly.

  • The following script will create a pdf file of the active sheet. Keep in mind that the active sheet is the sheet selected in the UI.

Solution:

function PDF(){
  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sss.getActiveSheet();
  var ssID = sss.getId();
  var shID = sheet.getSheetId().toString();
  var folderID = "1_gA4D7ybJ60dfgsdfgdsfInqVoo9D769cqmAnJI7g7";
  var folder = DriveApp.getFolderById(folderID);
  var pdfName = "Invoice " + sheet.getRange("d6").getValue(); 
  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};  
  var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=pdf&id="+ssID+"&gid="+shID;
  var result = UrlFetchApp.fetch(url , requestData);  
  var newFile = folder.createFile(result.getBlob()).setName(pdfName)
}
user_112252910
Related