Passign parameters form google sheet to google script

Question

I am new to google scripts and I am having trouble with passing parameters from my google spreadsheet to my google script.

I have created a function in my script:

function functionname(ref) {
  Logger.log(ref);
  var value = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(ref).getValue();
  //do stuff with the value read from the sheet
  return modified_value;
}

and I am calling it in my google spreadsheet like:

=functionname(A4)

(the function is called from an empty A5 cell, with cell A4 filled with text).

I have initially tried with a static reference in the function, like:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("H9").getValue() 

In this situation, it worked perfectly, but I do not want to replicate the function for each cell I need to operate, given I need to deliver this to non-programmers and the list of cells will increase.

Initially, I read that passing the parameters from the cell will be sufficient to receive the value and I had tried also this solution. Still, no matter what I try, the value of ref is always "undefined".

Can anyone please help me understand what I am missing?

Thanks

Answer 1

No need for the SpreadSheetApps methods for custom function to pull the value from the SpreadSheet because it already gets the cell value that you are referencing. Just remove the var value = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(ref).getValue();

function functionname(ref) {
  Logger.log(ref);
  //do stuff with the value read from the sheet
  return modified_value;
}

You can call this function directly on your spreadsheet. Don't forget to store the new value on a variable and return it, or return the computed value directly.