Sheets Scripts- Save As PDF- Pull File Name from Cell-CodePudding

Objective: Automatically insert file name for Download as PDF.

I have put together a script that opens a print preview screen that allows me to print the selected cells. It also has a download button that allows me to download the preview as a PDF. I would like to be able to set the file name from the combination of 3 cells. Name ('C6:J6') Date('L54:N54') Time ('S54:U54'). These cells are all on the same sheet that is being printed. I am at a loss as to how to do this.

Below is my script and a screenshot of my print preview screen. Circled is the file name that I would like to replace and the download button that opens the standard download file screen. Thank you in advance!

//https://stackoverflow.com/questions/58627501/how-to-print-sheet-range-using-gs-script-in-google-sheets
//https://xfanatical.com/blog/print-google-sheet-as-pdf-using-apps-script/

function printCQF() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:AN59').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('AN59'));
  printSelectedRange();
};

var PRINT_OPTIONS = {
  'size': 0,              // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B
  'fzr': false,           // repeat row headers
  'portrait': true,       // false=landscape
  'scale': 3,             //1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
  'top_margin':0.25,      //All four margins must be set!
  'bottom_margin':0.25,   //All four margins must be set!
  'left_margin':0.2,      //All four margins must be set!
  'right_margin':0.2,     //All four margins must be set!
  'gridlines': true,      // show gridlines
  'printnotes':false,     //true/false'
  'printtitle': false,
  'sheetnames': false,
  'pagenum': 'UNDEFINED', // CENTER = show page numbers / UNDEFINED = do not show
  'attachment': false
}

var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);

/*  
// Creates a custom menu.  
function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Print...').addItem('Print selected range', 'printCQF').addToUi();
}
*/

function printSelectedRange() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();
 

  var gid = sheet.getSheetId();
  var printRange = objectToQueryString({
    'c1': range.getColumn() - 1,
    'r1': range.getRow() - 1,
    'c2': range.getColumn()   range.getWidth() - 1,
    'r2': range.getRow()   range.getHeight() - 1
  });
  var url = ss.getUrl().replace(/edit$/, '')   'export?format=pdf'   PDF_OPTS   printRange   "&gid="   gid;

  var htmlTemplate = HtmlService.createTemplateFromFile('js');
  htmlTemplate.url = url;
  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');
}

function objectToQueryString(obj) {
  return Object.keys(obj).map(function(key) {
    return Utilities.formatString('&%s=%s', key, obj[key]);
  }).join('');

}

CodePudding user response：

You need to download the file using UrlFetchApp.fetch(), create a file based on the response for the fetch() using DriveApp.createFile(), rename it, get the URL of newly created file and set it as value of htmlTemplate.url.

Replace the printSelectedRange() in your code with this:

function printSelectedRange() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();
 

  var gid = sheet.getSheetId();
  var printRange = objectToQueryString({
    'c1': range.getColumn() - 1,
    'r1': range.getRow() - 1,
    'c2': range.getColumn()   range.getWidth() - 1,
    'r2': range.getRow()   range.getHeight() - 1
  });
  var url = ss.getUrl().replace(/edit$/, '')   'export?format=pdf'   PDF_OPTS   printRange   "&gid="   gid;

  var params = {method:"GET",headers:{"authorization":"Bearer "  ScriptApp.getOAuthToken()}};
  
  var response = UrlFetchApp.fetch(url, params).getBlob();

  var name = sheet.getRange('C6:J6').getDisplayValue();
  var date = sheet.getRange('L54:N54').getDisplayValue();
  var time = sheet.getRange('S54:U54').getDisplayValue();
  var filename = name   "-"    date   "-"   time;

  //create new file on drive
  var newFile = DriveApp.createFile(response).setName(filename);
  
  var htmlTemplate = HtmlService.createTemplateFromFile('js');
  htmlTemplate.url = newFile.getUrl();
  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');
}

Example Output:

Note: If you want to remove the generated file after you print it, you can add newFile.setTrashed(true); at the end of printSelectedRange() function. This will move the files to trash. Files in trash will be automatically deleted after 30 days.

References: