Google Sheets – Use Cell Value in PDF File Name When Exporting

google sheetsgoogle-apps-script

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

Best Answer

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

Formula

=QUERY(B3:C8; "SELECT C ORDER BY B DESC LIMIT 1")

Explained

The QUERY will take both columns as dataset, but it will only show column C. Furthermore, it will sort column B descendingly (highest first) and the limit option will limit the result dataset to one row.

In this case, there's no need to calculate the highest date first.

Example

I've copied your file and added my solution: FRISBEE

Google-sheets – Apply formula when other is modified

I do not know how to solve this problem without scripts, but there is a simple work around. When you update prices, you can drag them elsewhere in your sheet (e.g. below, in the form of a price record). The values on the 'Sales' sheet will stay linked to the original price, and the formula you enter for sales will be the same.

If you choose this work around, I would recommend creating a script that automatically moves your prices so that you don't have to manually drag things around and so that the newest prices appear closer to the top. There are many ways to make that type of script, but here's one not so elegant, but simple way:

var ss = SpreadsheetApp.getActiver();

// Many of these values depend on the layout of your document.
// I have indicated where you will need to change values if
// you change the layout.

function onOpen(){
  // This will make a menu appear that allows you to run the scripts
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Make space for a new price', functionName: 'newprice'},
    {name: 'New sales day', functionName: 'newsales'},
  ];
  ss.addMenu('New Entry', items);
};

function newprice(){
  // This will simply insert a row under your prices.
  // You just have to drag the on you want to change into the new row
  // Your info on the other sheets will stay linked to the correct number
  var sheet = ss.getSheets()[1]; // This number may need editing
  sheet.insertRowBefore(7); // This number may also need attention
};

function newsales(){
  var sheet = ss.getSheets()[0];
  var where = 7;  // Where to add the new lines?
  var how_many = 5;  // How many products to add?
  for (var ind = 0; ind < how_many; ind ++){
    sheet.insertRowBefore(where);
  };
  // Copy the values
  var source = sheet.getRange("A3:G7"); // This would need attention
  var destination = sheet.getRange("A8:G12");  // And this...
  source.copyValuesToRange(destination,1,1,1,1);
};

Using a script may be more trouble than it's worth for this type of problem.

If for some reason (I can't imagine why) you want to have one cell that adds old prices and new prices, then you need a more advanced script. It would be easier to make one cell associated with each price, and then add them in the final cell.

Best Answer

Related Solutions

Google Sheets – Get Cell Value Based on Another Cell

Formula

Explained

Example

Google-sheets – Apply formula when other is modified

Related Topic