How to call library function as custom function from spreadsheet formula?

Question

This might be the same root cause as in How to call a library function from a spreadsheet drop-down menu generated by the same library, but I still wanted to throw it out there in case there was something new or somehow the situation was different.

The thing is that I want to keep all my custom functions in a library, then add the library to any given spreadsheet, and be able to reference them from a cell formula. In the library:

/**
 * Returns the parameter
 * Eg: (16) to 16
 *
 * @param {object} theparameter the parameter.
 * @return {object} the same object.
 */
function ReturnParam(theparameter){
  return(theparameter);
}

In the spreadsheet script, add the library and give it an identifier: myLib

In the spreadsheet cell formula:

=myLib.ReturnParam(4)

or

=myLib.ReturnParam("hello")

and the final value in the cell be 4 or 'hello'.

However it always shows an Unknown function: 'myLib.ReturnParam' error.

I read about the solutions to add wrapper functions in the spreadsheet script, and as well dislike the inconveniences.

In https://issuetracker.google.com/issues/36755072#comment20, there is "a solution", but I fail to grasp the missing details. (Can someone elaborate?)

Any hope for this case?

Is there an alternative deploying as an add-on, even if not trying to do anything fancy with the add-on?

I couldn't find a google-library or -libraries tag.

Possible duplicate of Calling a Google App Script library from a Google spreadsheet cell — Rubén
Yes, a duplicate, but still no real solution, just a the described work-around. Looks like nothing has changed. Thanks anyway. — iatorresc
Once you gain enough reputation you could offer a bounty to that question. Another alternative is edit your question to explain why the solutions exposed on the answers aren't a "real solution". — Rubén

Rubén Rubén · Accepted Answer · 2018-04-02T20:06:16

The solution is to create a function on you Google Apps Script project that calls the library function

/**
 * Description of what your function does
 *  
 * @customfunction
 */
function ReturnParam(theParameter){
  return myLib.ReturnParam(theParameter);
}

Then you could call it from your spreadsheet as a custom function

=ReturnParam(4)

How to call library function as custom function from spreadsheet formula?

1 Answers