Export PDF link sheet ID auto change

Question

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?

Answer 1

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

Answer 2

Changing GID based on a sheet name can be done like this:

=HYPERLINK("#gid="&VLOOKUP(SHEETNAME(), SHEETLIST(), 2, 0)&"range=B33:U33", "EXAMPLE 5")

After you add these two scripts to your sheet:

function SHEETLIST() {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  out[0] = [ "NAME" , "#GID" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = 
  [sheets[i-1].getName() , sheets[i-1].getSheetId() ];
  return out
}
catch( err ) {
  return "#ERROR!" 
}
}


function SHEETNAME() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}