save a sheet from spreadsheet as pdf in same google drive folder

Question

I am trying to make a code that gets all files from a google drive folder, then converts all files to excel and save in same folder and also make pdf files from second sheet 'V2' of each spreadsheet and save in same folder.

I am successful in creating excel folder but pdf part is not working. I have copied code from many different sources.

function Generator(){
  //Factors to change every month
  var folderID = '14Bz9LLAiv2BgsT4bowxUtMfG2AsTxk91';
  
  
  // Loop through all the files and save as excel and pdf.
  var reportsFolder = DriveApp.getFolderById(folderID);
  var files = reportsFolder.getFiles();
  var TAB_NAME = 'V2' //For pdf
  while(files.hasNext()) {
    var file = files.next();
    var bnkFile = SpreadsheetApp.getActiveSpreadsheet();
    var name = file.getName();
    //Creating Excel files
    var url = "https://docs.google.com/spreadsheets/d/" + file.getId() + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
    var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); // Modified
    reportsFolder.createFile(blob);
    //Creating PDF files
    var VName =  name.replace("REPORT", "V2");
    var V2Sheet = bnkFile.getSheetByName(TAB_NAME);
    var url2 = "https://docs.google.com/spreadsheets/d/" + V2Sheet.getId() + "/export?exportFormat=pdf&format=pdf" + ScriptApp.getOAuthToken();
    var blob2 = UrlFetchApp.fetch(url2).getBlob().setName(VName + ".pdf"); // Modified
    reportsFolder.createFile(blob2);
    
  }
}

Any simple solution for generating PDFs?

Answer 1

Explanation:

You need to specify both the id of the spreadsheet file but also the gid of the sheet itself.

Replace

var url2 = "https://docs.google.com/spreadsheets/d/" + V2Sheet.getId() + "/export?exportFormat=pdf&format=pdf" + ScriptApp.getOAuthToken();

with

var url2 = "https://docs.google.com/spreadsheets/d/"+ file.getId() + "/export?format=pdf&id="+file.getId()+"&gid="+V2Sheet.getSheetId();

---

Replace

var bnkFile = SpreadsheetApp.getActiveSpreadsheet();

with

var bnkFile = SpreadsheetApp.openById(file.getId());

to get the spreadsheet object of the file. Your current code gets the active spreadsheet which is the spreadsheet that this script is attached to.

Also use the requestData object as I define it in my solution.

---

Solution:

function Generator(){
  //Factors to change every month
  var folderID = '14Bz9LLAiv2BgsT4bowxUtMfG2AsTxk91';
  
  // Loop through all the files and save as excel and pdf.
  var reportsFolder = DriveApp.getFolderById(folderID);
  var files = reportsFolder.getFiles();
  var TAB_NAME = 'V2' //For pdf
  while(files.hasNext()) {
    var file = files.next();
    
    if(file.getMimeType()=='application/vnd.google-apps.spreadsheet'){
    
    var bnkFile = SpreadsheetApp.openById(file.getId());
    var name = file.getName();
    //Creating Excel files
    var url = "https://docs.google.com/spreadsheets/d/" + file.getId() + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
    var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); // Modified
    reportsFolder.createFile(blob);
    //Creating PDF files
    var VName =  name.replace("REPORT", "V2");
    var V2Sheet = bnkFile.getSheetByName(TAB_NAME);
    var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
    var url2 = "https://docs.google.com/spreadsheets/d/"+ file.getId() + "/export?format=pdf&id="+file.getId()+"&gid="+V2Sheet.getSheetId();
    var blob2 = UrlFetchApp.fetch(url2,requestData).getBlob().setName(VName + ".pdf"); // Modified
    var pdfFile = reportsFolder.createFile(blob2);
    var pdfUrl = pdfFile.getUrl(); // <- pdfUrl will give you the url of the pdf file.

    }
  }
}