Google Sheets trigger writing to cell

Question

I have a trigger or function for a sheet but I want it to run Onchange and populate cells. I already have the trigger to run Onchange but haven't been able to learn how to properly get it to fill cells with information. This works if I make a cell =sheetnames(), but it won't update if I add or delete a sheet, I have to clear the cell and put in the formula again.

I've only really tried reading over and seeing how people solved similar problems and trying to adapt it. I don't have much experience with code though.

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 
}

Well, the code runs as expected, but what I desire is that onchange it will populate a column with the names of all the sheets currently in the... sheet. I have 2 sheets currently "Primary" as the first, then "SheetNames" as the second.

Cooper Cooper · Accepted Answer · 2019-07-27T04:29:38

Try this:

Don't use this as a cell function it will populate the first column of the sheet named SheetNames. Make sure that you don't have two functions with the same name. So get rid of the old one or change the names some how.

function sheetnames() {
  var ss=SpreadsheetApp.getActive();
  var osh=ss.getSheetByName('SheetNames');
  var out = [];
  var sheets = SpreadsheetApp.getActive().getSheets();
  for (var i=0;i<sheets.length;i++) {
    out.push([sheets[i].getName()] )
  }
  osh.getRange(1,1,out.length,1).setValues(out);
}

Google Sheets trigger writing to cell

1 Answers