1

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
2

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

  • I commented a line of the code as it's not necessary (you could remove it). This will improve performance. – Rubén Oct 10 '16 at 11:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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