0

I have developed a script which allows me to export a spreadsheet to a pdf file with a click of a button. I have assigned the script to a macro so that it is triggered by clicking on an image.

Script is doing what I want it to do except that there is one issue. The name of the file that it is being saved as. The script is written so that the saved pdf file is being saved as the name of the tab. I was wondering if anyone can help me so that the name of the saved pdf file refers to a specific cell within the spreadsheet, to a cell 'D7'.

function onOpen() {
  var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);
}

function generatePdf() {
  var sourceSpreadsheet = SpreadsheetApp.getActive();

  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);

  var pdfName = sheetName;

  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

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

  var destSheet = destSpreadsheet.getSheets()[0];

  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);

  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

I was wondering if anyone can help me write the script so that saved filename refers to a particular cell of the spreadsheet.

0

You could change

var pdfName = sheetName;

to whatever you want, as pdfName is used later to set the PDF filename, more specfically on the following line:

var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);

It could be very whorthy for you to spend sometime learning the basics of JavaScript.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.