1

How can I get and use a value of a cell to the PDF file name, when exporting/saving using that script. I would like to use a string written in a cell, I mean, put that information in a variable and automatic use it as the pdf file name. At this moment, when I execute the script, a window is opened and in the file name field there are something like this: "Spreedsheet name" - "sheet name".pdf. And I have to rewrite to the name that I already have in a cell.

function ExportPDF() {
  SpreadsheetApp.flush();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getId();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getSheetId();
  var pagina = 'https://docs.google.com/spreadsheets/d/' + spreadsheet + '/export?exportFormat=pdf&format=pdf&gid=' + sheet + '&portrait=false&scale=4';
  var html = "<script>window.open('" + pagina + "');google.script.host.close();</script>";
  var userInterface = HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');
};
  • What do you mean by "value of a cell in the PDF file name"? – Rubén May 22 at 22:59
  • 1
    Is this thread useful for your situation? stackoverflow.com/q/56215898/7108653 – Tanaike May 23 at 0:17
  • @Rubén Hi, I would like to use a string written in a cell, I mean, put that information in a variable and automatic use it as the pdf file name. At this moment, when I execute the script, a window is opened and in the file name field there are something like this: "Spreedsheet name" - "sheet name".pdf. And I have to rewrite to the name that I already have in a cell. Thanks! – Leandro Amorim May 23 at 18:09
  • 1
    @Tanaike thank you very much for your suggestion. After just a few adjusts that worked very well. – Leandro Amorim May 23 at 20:05
  • 1
    Thank you for replying. I'm glad your issue was resolved. – Tanaike May 23 at 22:07
1

I used the script below, located in https://stackoverflow.com/q/56215898/7108653, like suggested for @Tanaike, and adjusted with just a few things (lines 3, 4 and 5).

function downloadSheetAsPDF2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetId = ss.getSheetByName("RECEITA PARA IMPRESSÃO").getSheetId();
  var valor = SpreadsheetApp.getActiveSheet().getRange('M3').getValue();  
  var filename = valor;


  // Creat PDF file as a temporary file and create URL for downloading.
  var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + ss.getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&portrait=false&scale=4&access_token=" + ScriptApp.getOAuthToken();
  var blob = UrlFetchApp.fetch(url).getBlob().setName(filename);
  var file = DriveApp.createFile(blob);
  var dlUrl = "https://drive.google.com/uc?export=download&id=" + file.getId();

  // Open a dialog and run Javascript for downloading the file.
  var str = '<script>window.location.href="' + dlUrl + '"</script>';
  var html = HtmlService.createHtmlOutput(str);
  SpreadsheetApp.getUi().showModalDialog(html, "sample");
  file.setTrashed(true);

  // This is used for closing the dialog.
  Utilities.sleep(3000);
  var closeHtml = HtmlService.createHtmlOutput("<script>google.script.host.close()</script>");
  SpreadsheetApp.getUi().showModalDialog(closeHtml, "sample");
}
  • Don't forget to accept your answer :) – Rubén May 25 at 15:44

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.