Google-sheets – Export PDF link sheet ID auto change

formulasgoogle sheetsgoogle-apps-scriptlinkspdf

I have the link for export as PDF a specific range on a sheet

=HYPERLINK("*****export?exportFormat=pdf&format=pdf&gid=962914045&range=A1:H68&pagenumbers=false&gridlines=false&portrait=true&scale=4&horizontal_alignment=CENTER&top_margin=0.20&bottom_margin=0.20&left_margin=0.00&right_margin=0.00","PRINT")

But this sheet is duplicated every day and filled with the new data. But obviously sheet id (gid=962914045) stays the same.

Is there a way (script?) to put the ID of the current sheet in the formula to keep it always for the active sheet?
maybe I can replace the formula with a script and assign it to a button?

Best Answer

ok, I solved the task with the following script

function openTab() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sprsheetid = ss.getId();
  var sheetid = ss.getSheetId();
  var selection = "https://docs.google.com/spreadsheets/d/"+sprsheetid+"/export?exportFormat=pdf&format=pdf&gid="+sheetid+"&range=A1:H68&pagenumbers=false&gridlines=false&portrait=true&scale=4&horizontal_alignment=CENTER&top_margin=0.20&bottom_margin=0.20&left_margin=0.00&right_margin=0.00"
  var html = "<script>window.open('" + selection + "');google.script.host.close();</script>";
  var userInterface = HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModalDialog(userInterface, 'Open Tab');
}
Related Topic