Is there a custom function or script that returns gid of a specific sheet in Google Sheets?

by

script:

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!" }}

formula:

=SHEETLIST()

turn sheet names into active hyperlinks:

=ARRAYFORMULA(HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")))

and if you want to bind it to manual input you can use VLOOKUP like

=ARRAYFORMULA(IFNA(VLOOKUP(A1, HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")); 1; 0)))

where A1 is your “search cell”

More Related Contents:

  1. Apps Script: how to get hyperlink from a cell where there is no formula
  2. Selecting the last value of a column
  3. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  4. Is there a way to evaluate a formula that is stored in a cell?
  5. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  6. ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?
  7. No permission to call msgBox in Google Apps Scripting
  8. Periodically refresh IMPORTXML() spreadsheet function
  9. Using built-in spreadsheet functions in a script
  10. Google Sheet use Importxml error could not fetch url [duplicate]
  11. TypeError: Cannot read property ‘getRange’ of null (line 6, file “Code”) in google sheet script [duplicate]
  12. .setFormula() and received error missing ) argument list
  13. How do you create a “reverse pivot” in Google Sheets?
  14. Create Google Calendar Events from Spreadsheet but prevent duplicates
  15. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  16. Retrieving Google Sheets cell border style programmatically
  17. Determining the last row in a single column
  18. Cannot find method getRange(number,number)?
  19. You do not have permission to call openById
  20. Single Google Form for multiple Sheets [duplicate]
  21. Adding Buttons To Google Sheets and Set value to Cells on clicking
  22. How to replace text in Google Spreadsheet using App Scripts?
  23. How to Autofill last row with formula, when data is received from IFTTT on the last row?
  24. Custom function throws a “You do not have the permission required to setValue” error
  25. Margins parameters for Google spreadsheet export as PDF
  26. Script to convert .XLSX to Google Sheet and move converted file
  27. Hidden field in a Google Form
  28. How to evaluate a spreadsheet formula within a custom function?
  29. Adding a property to an Html template gives error “Object does not allow properties to be added or changed”
  30. Google spreadsheet “Cannot call method “getRange” of null”

Leave a Comment