CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Excelsson
Excelsson

Reputation: 195

Google Appscript Exporting all Sheets as PDF and name it from a specific Cell Value - Save in a specific Folder

I'm trying to export all sheets as PDF, get the filename from cell D5 and save them to a specific folder in Google drive, however, I'm getting a bunch of duplicates with the same name as the first sheet. Any suggestions? Also changed DriveApp.getRootFolder(); for getFolderbyName, seems that is not recognizing the actual folder, everything else in the code is working flawlessly.


function savePDFs( optSSId, optSheetId ) {

  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  

  var url = ss.getUrl().replace(/edit$/,'');

 
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getFolderbyName('Invoices');
  }
  

  var sheets = ss.getSheets();
  

  for (var i=0; i<sheets.length; i++) {
    var sheet = sheets[i];
    

    if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 
    

    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
        + '&gid=' + sheet.getSheetId()   //the sheet's Id
        // following parameters are optional...
        + '&size=letter'      // 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

    var options = {
      headers: {
        'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
      }
    }

    var response = UrlFetchApp.fetch(url + url_ext, options);
    
    var ss2= SpreadsheetApp.getActiveSpreadsheet();
    
var valor = SpreadsheetApp.getActiveSheet().getRange('D5').getValue()
    
    var blob = response.getBlob().setName(valor + '.pdf');


    folder.createFile(blob);
  }
}

Upvotes: 1

Views: 1186

Answers (1)

Tanaike
Tanaike

Reputation: 201513

I believe your goal as follows.

  • You want to export the sheet of optSheetId as a PDF file.
  • You want to retrieve the value from the cell "D5" of each sheet and use it to the filename of PDF file.
  • You want to put the PDF file to the folder of Invoices. When the folder is not existing, the same folder of the Spreadsheet is used.

For this, how about this answer?

Modification points:

  • For DriveApp.getFolderbyName('Invoices'), please modify getFolderbyName to getFoldersByName.
  • In order to retrieve the value from the cell "D5" from each sheet, please modify var valor = SpreadsheetApp.getActiveSheet().getRange('D5').getValue() to var valor = sheet.getRange('D5').getValue();.

When above points are reflected to your script, it becomes as follows.

Modified script:

function savePDFs( optSSId, optSheetId ) {
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace(/edit$/,'');
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  
  var folders = DriveApp.getFoldersByName('Invoices');  // Modified
  var folder = folders.hasNext() ? folders.next() : parents.next();  // Modified

  var sheets = ss.getSheets();
  for (var i=0; i<sheets.length; i++) {
    var sheet = sheets[i];
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue; 
    var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf
      + '&gid=' + sheet.getSheetId()   //the sheet's Id
      // following parameters are optional...
      + '&size=letter'      // 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
    var options = {headers: {'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()}}
    var response = UrlFetchApp.fetch(url + url_ext, options);
    var valor = sheet.getRange('D5').getValue();  // Modified
    var blob = response.getBlob().setName(valor + '.pdf');
    folder.createFile(blob);
  }
}

References:

Upvotes: 1

Related Questions