How to create a Custom Function with an loops inside of javascript on google apps script and implent it to spreadsheet of "C2:C" of range?

Question

I use a google apps script to make a function which when I call it into spreadsheet cells as a custom function but it's seems looks like won't to looping one of them or anything. Which is when I call the custom function like this "=setSheetIdhere()" the output come just give me something or came out with something result like of the single cell data of the result only that the value is "0".

function setSheetIdhere() {
  for (var i=0; i < sss.length; i++) {
    return [[sss[i].getSheetId()]];
  }
}

Here I attached a pic to describbing more my Explaination. The Problem is How to generate or collecting or set all Ids of the All Sheets ???

Answer 1

I just ran this on a newly created spreadsheet:

function spreadsheetIds() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets().map(function(sh){return sh.getSheetId();});
  Logger.log(shts);
  //output - [0, 1550400053, 206842422, 247142844, 124067339]

}

function testsetSheetsId() {
  Logger.log(setSheetsId());
  //always returns zero
}

This is essentially what your function must be I think. If you run testsetSheetsId() you'll find that it always returns zero.

function setSheetsId() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  for (var i=0; i <shts.length ; i++) {
    return shts[i].getSheetId();
  }
}

I think you just need to run a function like this one time and you're done.

function generateSheetIdsIntoAColumn(startrow,startcolumn) {
  var startcolumn=startcolumn||3;
  var startrow=startrow||2;
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var shts=ss.getSheets();
  var sA=[];
  shts.forEach(function(s){sA.push([s.getSheetId()]);});  
  var rg=sh.getRange(startrow,startcolumn,sA.length,sA[0].length).setValues(sA);
}

But this is not a cell function.

I played around with this a bit and I found that you can run it with an installable onChange() trigger and it will update the list every time you add another sheet.

This is the code:

//This is modified to run with the event object instead of parameters for controlling where the column starts
function generateSheetIdsIntoAColumn(e) {
  Logger.log(JSON.stringify(e));
  if(e.changeType=='INSERT_GRID') {    
    var startcolumn=3;
    var startrow=2;
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getSheetByName("Sheet1");
    var shts=ss.getSheets();
    var sA=[];
    shts.forEach(function(s){sA.push([s.getSheetId()]);});  
    sh.getRange(startrow,startcolumn,sA.length,sA[0].length).setValues(sA);
  }
}

//This installs the onChange event trigger
function installonChange() {
  var ss=SpreadsheetApp.getActive();
  if(!isTrigger('generateSheetIdsIntoAColumn')) {
    ScriptApp.newTrigger('generateSheetIdsIntoAColumn').forSpreadsheet(ss).onChange().create();
  }
}

//this functions helps you to keep from creating more than one trigger
function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Unfortunately, onChange() trigger doesn't happen when a script inserts a sheet. It only works when user adds a sheet. Sorry, I guess I can't help you much here to get it done automatically without polling with a time based trigger.