Reading a cell in a Google script fonction and uses it in a fonction #inception

Question

need help, i try to read a cell in this fonction. I've tried 20 differents type of thing, i always have this error message when i run the focntion on my spreadsheet :

Message details :

Cannot convert function getData() { Sheet = SS.getSheetByName("Technic"); Range = Sheet.getRange(4, 2); Data = Range.getValue(); } to (class).

What wrong ?

function hideMyColumns()
{
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  var vl = sh.getRange('A30').getValue();
  var cols1 = [ function getData() {
          Sheet = SS.getSheetByName('Technic');
          Range = Sheet.getRange(4, 2); 
          Data = Range.getValue() }];
  var cols2 = [4, 5, 7, 8, 10, 11,]
  switch (vl)
  {
    case 1:
      for (var i = 0; i < cols1.length; i++)
      {
        sh.hideColumns(cols1[i]);
      }
      break;
    case 2:
      for (var i = 0; i < cols2.length; i++)
      {
        sh.hideColumns(cols2[i]);
      }
  }
}

évolution of the situation :

ok, now the fonction is like this :

function hideMyColumns()
{
var ss=SpreadsheetApp.getActive();
var sh=ss.getActiveSheet();
var vl=sh.getRange('A30').getValue();
var cols1=[ function() { 
Sheet = ss.getSheetByName('Technic');
Range = Sheet.getRange(2,4);
Data = Range.getValue();
return Data;
}()]; 
var cols2=[4,5,7,8,10,11,12,]
switch(vl)
{
case 1:
  for(var i=0;i<cols1.length;i++)
  {
    sh.hideColumns(cols1[i]);
  }
  break;
  case 2:
  for(var i=0;i<cols2.length;i++)
  {
    sh.hideColumns(cols2[i]);
  }
}

}

the context :

the spreadsheet have 2 sheet : "Main" and "Technic"

"Main" can have some columns hide by this fonction. In the sheet "Technic" you find a cell that contains a formula that returns:"2,3,4..." i try to use this formule like a variable in the fonction "hideMyColumns".

Under this i have another fonction more simple : unhideCols() of course. (work well!)

Answer 1

You want to give a value to cols1 using the function expressions. If my understanding is correct, how about this modification?

Modification points :

• In your situation, when var cols1 = [function getData(){...}] is used as the function expressions, please modify to var cols1 = [function getData(){...}()] or var cols1 = [function(){...}()].
• SS of SS.getSheetByName('Technic') is not declared.
  • When getData() is run, the error occurs here.
  • At the modified script, SS was modified to ss.
• There are no returned values in getData().
  • By this, cols1 always becomes [].

Modified script :

var cols1 = [ function() { // Modified
  Sheet = ss.getSheetByName('Technic'); // Modified
  Range = Sheet.getRange(4, 2);
  Data = Range.getValue();
  return Data; // Added
}()]; // Modified

Note :

• In this modified script, only var cols1 was modified.
• If you want to use SS, please declare it.
• If you want to return other values, please modify it.
• From your script, it seems that cols1 has only one element. So you can modify from sh.hideColumns(cols1[i]); to sh.hideColumns(cols1[0]); without for loop. Or when it is declared as var cols1 = function(){...}(), you can modify to sh.hideColumns(cols1); without for loop.

References :

• Why do you need to invoke an anonymous function on the same line?
• Location of parenthesis for auto-executing anonymous JavaScript functions?

If I misunderstand your question, I'm sorry.

Edit :

If you want to use the values of column D as ranges, please try this modified script.

function hideMyColumns()
{
var ss=SpreadsheetApp.getActive();
var sh=ss.getActiveSheet();
var vl=sh.getRange('A30').getValue();
var cols1=function() { // Modified
Sheet = ss.getSheetByName('Technic');
Range = Sheet.getRange(3,4); // getRange(3,4) is D3, Modified
Data = Range.getValue();
return Data.split(","); // Modified
}();
var cols2=[4,5,7,8,10,11,12,]
switch(vl)
{
case 1:
  for(var i=0;i<cols1.length;i++)
  {
    sh.hideColumns(cols1[i]);
  }
  break;
  case 2:
  for(var i=0;i<cols2.length;i++)
  {
    sh.hideColumns(cols2[i]);
  }
}