0

I’ve trying to make a script to export a single Google sheet tab to a PDF into the same Google drive folder but keep getting the error “Cannot read property ‘get range’ of undefined”.

I can’t work it out? If someone is able to point me in the right direction I would be very thankful.

Aaron.

// Simple function to add a menu option to the spreadsheet "Export", for saving a PDF of the spreadsheet directly to Google Drive.
// The exported file will be named: SheetName and saved in the same folder as the spreadsheet.
// To change the filename, just set pdfName inside generatePdf() to something else.

// Running this, sends the currently open sheet, as a PDF attachment
function onOpen() {
  var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);  
}

function generatePdf() {
  // Get active spreadsheet.
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  
  // Get active sheet.
  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  
  // Set the output filename as SheetName.
  var pdfName = sheetName;

  // Get folder containing spreadsheet to save pdf in.
  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }
  
  // Copy whole spreadsheet.
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  // Repace cell values with text (to avoid broken references).
  var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  // Delete redundant sheets.
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
      destSpreadsheet.deleteSheet(sheets[i]);
    }
  }

  // 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);
}
Aaron Prudence
  • 1

1 Answers1

1

This might be closer to what you want:

function generatePdf() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet()
  var pdfName = sh.getName();
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  } else {
    folder = DriveApp.getRootFolder();
  }
  var dss = SpreadsheetApp.open(DriveApp.getFileById(ss.getId()).makeCopy("tmp_convert_to_pdf", folder))
  var rg = sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns());
  var vs = rg.getValues();
  var dsh = dss.getSheetByName(sh.getName());//assume destination sheet has the same name as the active sheet
  var drg = dsh.getRange(1, 1, dsh.getMaxRows(), dsh.getMaxColumns());
  drg.setValues(vs);//not sure why you do this...it's a potentially and hell of a lot of cells
  dss.getSheets().forEach(sh => { if (sh.getName() != pdfName) { sh.hideSheet(); } });
  var theBlob = dss.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks Cooper for your help. I’m very new to this and is very much appreciated. I did not know you can just hide all the sheets? How would I go about that? Is that an easier way to achieve what I want? I tried your code and I’m know getting a ReferenceError: sheetName is not defined. – Aaron Prudence Jan 08 '22 at 09:33
  • I've found this that nearly does what I want using the hide sheet method you suggested. Would you be able to help me modify @Cooper 1. Save the PDF in the same folder as the main spreadsheet rather than the root folder. 2. Use the Active sheet rather than a defined sheet. Thanks – Aaron Prudence Jan 08 '22 at 10:24
  • [link] https://stackoverflow.com/questions/68392810/google-sheets-how-to-save-a-single-sheet-in-a-spreadsheet-file-as-pdf-where-th?noredirect=1&lq=1 – Aaron Prudence Jan 08 '22 at 10:26