Hyperlink to a cell/row in the leftmost sheet of Google spreadsheet, even if sheets are moved?

Question

I have a Google Sheets spreadsheet where I keep notes on monthly tasks, so each sheet represents one month. I always move the current month's sheet so that it's first in the spreadsheet. I want to be able to send my coworkers a hyperlink that always links to a cell or row the first/leftmost sheet, no matter what sheet that is.

For example, if I have a cell labeled "Customer 3," then I want the hyperlink to go to the "Customer 3" cell on the first/leftmost sheet in my spreadsheet regardless of whether it's the January, February, or March sheet. And when I create a new sheet - April - and move it to the leftmost position, I want the link to go to the "Customer 3" cell/row on the new April sheet.

I know how to link to a specific cell, to a specific sheet, and to a specific cell within a specific sheet. But I don't want to have to recreate the hyperlinks every month. Is there a way to accomplish this, either natively or with a Google Apps script?

Thanks.

Answer 1

If you want to make reference to the first sheet, then use .getSheets[0] It will always reference the first sheet, regardless of its name.

function firstSheet (){
  var firstSheet = SpreadsheetApp.getActive().getSheets()[0];
  return firstSheet;
}

EDIT: you can get the ID of the first sheet dynamically by:

var firstSheet = SpreadsheetApp.getActive().getSheets()[0].getSheetId()

Answer 2

The hyperlink is composed of several 'elements' so to call them. You get the spreadsheet ID, and then you get the sheet specifics as an "edit#gid=______"

I've made some tests, and I believe that as long as you only use the spreadsheet ID, it will always by default link to the first sheet.

You can do that directly through the hyperlink, only writing the link until just before the "edit#gid=_____" part, but then you would need google apps script to find the cell automatically, so I would suggest doing it all through the script to make it more simple and organized.

When you write the script and link to the spreadsheet, it will always give you the first spreadsheet. Anyways, maybe it would be helpful to see some of your script to help you out more directly.