Google-sheets – Retrieve data from another sheet with its position and not its name

google sheets

I would like to retrieve data from a specific sheet in my document, but I don't want to use the name of the sheet as reference, but its position.

Google help says this:

https://support.google.com/docs/answer/75943?hl=en

=Sheet1!A1

Where Sheet1 is the name of the sheet.

Is there a way to use the sheet position instead?

E.g.:

=<sheetInPosition2>!A1

Best Answer

Short answer

Use the getSheets() method from the Spreadsheet object of Google Apps Script

Explanation

Google Sheets doesn't have a built-in function to get the sheet name by its position but it's possible to extended it by using Google Apps Script. The following is an example of a custom function that returns the sheet name given a valid position, otherwise return a custom error message.

function getSheetNameByPosition(position){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // var sheets = ss.getSheets();
  var result = '';
  try {
    return ss.getSheets()[position-1].getName();
  } catch(e) {
    throw new Error('The position value is not valid');
  }
}

References

Extending Google Sheets