CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
axis9011
axis9011

Reputation: 33

How to automatically update a list of all sheets names in a spreadsheet when a Sheet is added

I'm working with a spreadsheet where i have a sheet called "sheetsNames". In this sheet i'm trying to make a list of all sheets names which are currently in the spreadsheet.

I've found the following code which i'm using and it works fine:

function sheetnames() {
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
  return out 
}

In the sheet "sheetsNames" in cell A1 I manually set the value to "=sheetnames()" to run the code above.

My problem is that everytime I add a new sheet to the spreadsheet the list isn't adding the new sheet name automatically.

Can anybody help?

Upvotes: 0

Views: 1088

Answers (1)

Century Tuna
Century Tuna

Reputation: 1762

Unfortunately, custom functions cannot automatically update the list of sheets on your spreadsheet since the code doesn't check for any updates on objects or events happening on the sheet for it to be executed.

Suggestion:

You can change the code to this format, and create a simple onEdit or onOpen trigger instead. You can refer to this documentation for simple triggers. Like so:

enter image description here In my case, I used onOpen so that the function runs every time the sheet is opened.

function sheetnames() {
    var out = new Array()
    var sheets = SpreadsheetApp.getActiveSpreadsheet();
    var getsheet = sheets.getSheets();
    for (var i=0 ; i < getsheet.length ; i++) out.push([getsheet[i].getName()])
    Logger.log(out);
    sheets.getRange('C1').setValues(out); //set value of the array on your preferred cell.
    
    }

Upvotes: 1

Related Questions