Google Sheets filename in cell via formula instead of a script?

6
votes

Is there a FORMULA that will display the name of the file in a cell?

I've found scripts that will do it, formulas that will display the sheet name, but no luck finding a formula that will show the filename.

If I have to resort to the script, so be it. But I'd like to use formula if possible.

If this has been asked before, please point me to the post and I will delete this one.

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

1 Answers

5
votes

there is no such formula. current limitations of Google Sheets does not support getting sheet names by internal formulae only, so you will need to resort to a script. here are a few variations:

getting sheet name of the sheet this formula is used on:

function SHEETNAME() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

0

listing all sheet names and gids:

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

0

or sheet names based on order (eg. from left to right the leftmost being 1):

function SHEET(input) {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ;
  if( (input>0) && (input <= sheets.length)) return sheets[(input-1)].getName() ;
  else return "invalid sheet #" ;
}
catch( err ) {
  return "#ERROR!" 
}
}

0

for getting spreadsheet name and more:

function SNAME(option) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()
  var thisSheet = sheet.getName(); 
  if(option === 0){                            // ACTIVE SHEET NAME =SNAME(0)
    return thisSheet;
  }else if(option === 1){                      // ALL SHEET NAMES =SNAME(1)
    var sheetList = [];
    ss.getSheets().forEach(function(val){
       sheetList.push(val.getName())
    });
    return sheetList;
  }else if(option === 2){                      // SPREADSHEET NAME =SNAME(2)
    return ss.getName();    
  }else{
    return "#N/A";                             // ERROR MESSAGE
  };
};

0