1

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?

2 Answers 2

2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.