How can we reference the custom functions in a Google Apps Script?

[jeffrmanley]

How can we use the custom functions in a Google Apps Script? We have some very long formulas and looking for an easier way to manage them, using a script would make more sense for us.

Thanks,
Jeff

[ljay79]

Im not aware of any way, where you can use the add-ons functions within your own script as if they would be defined inside yours.

What does work is, setting cell values with such custom functions from within your own script, ie:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  sheet.getActiveCell().setValue('=JST_EPICLABEL("TP-5")');
}

May i ask, what you try to achive - maybe there is a easier way.

[jeffrmanley]

Hi @ljay79,

We have a dashboard in gSheets that pulls data from Jira for many projects we're working on. There are two dimensions (workstream and acquisition). We currently have 13 workstreams and 32 acquisitions (which will grow at a rate of 1 per week). We calculate the %-age completed and if an an issue exists we add a Jira link that gets displayed with the status and %-age. The formula for each cell in the matrix is this one:

=if("NA"=vlookup($A3,AuxProgress!$A$1:$Z,1+match(G$1,AuxProgress!$B$1:$Z$1,0),false),"NA",if(1=vlookup($A3,AuxProgress!$A$1:$Z,1+match(G$1,AuxProgress!$B$1:$Z$1,0),false), "Completed (100%)", if(JST_getTotalForSearchResult("project=IMPFAILURES AND Workstream='"&G$1&"' and Priority=Showstopper AND Acquisition ='"&$A3&"' and status not in ('Closed', 'Implemented', 'Cancelled')")>0 ,hyperlink("https://jira.servername.com/issues/?jql=project%3DIMPFAILURES%20AND%20Workstream%3D%27"&G$1&"%27%20and%20Priority%3DShowstopper%20AND%20Acquisition%20%3D%27"&$A3&"%27%20and%20status%20not%20in%20(%27Closed%27%2C%20%27Cancelled%27%2C%20%27Implemented%27)","Blocker" & " (" & Text(vlookup($A3,AuxProgress!$A$1:$Z,1+match(G$1,AuxProgress!$B$1:$Z$1,0),false),"0%") &")"),if(JST_getTotalForSearchResult("project=IMPFAILURES AND Workstream='"&G$1&"' and Priority=High AND Acquisition ='"&$A3&"' and status not in ('Closed', 'Implemented' , 'Cancelled')")>0,hyperlink("https://jira.servername.com/issues/?jql=project%3DIMPFAILURES%20AND%20Workstream%3D%27"&G$1&"%27%20and%20Priority%3DHigh%20AND%20Acquisition%20%3D%27"&$A3&"%27%20and%20status%20not%20in%20(%27Closed%27%2C%20%27Cancelled%27%2C%20%27Implemented%27)","Issue" & " (" & Text(vlookup($A3,AuxProgress!$A$1:$Z,1+match(G$1,AuxProgress!$B$1:$Z$1,0),false),"0%") &")"), "On Track (" & Text(vlookup($A3,AuxProgress!$A$1:$Z,1+match(G$1,AuxProgress!$B$1:$Z$1,0),false),"0%") &")")) ))

Any suggestions?

[ljay79]

Hmm looks quite complicated. Honestly, im not sure if i entirely understand the use case.

What i notice it, you use the add-ons custom functions directly in some of your formulas.
Better approach would be to use a seperate sheet (within your spreadsheet doc) where you only aggregate and fetch all raw data, then in your other sheet you simply use references to cell values of the data sheet.

This way, you have better separation of logic, code and data which also make the formulas much more readable.

In case you are concerned about updated results from the custom functions.
Ie: Whenever a spreadsheet is opened/re-opened, it will already recalculate all custom functions and therefore the formulas will use updated data as well.

[jeffrmanley]

We'd like to trigger a periodic refresh of the custom functions and the issue tables. Are we able to add your libraries into our Google Apps Script to call your functions like RefreshIssueTable?

[ljay79]

Sry, but the add-on is not available for libraries import.
Refreshing all custom functions you can however do.
Except the tables inserted with "List issues from filter".

To refresh any regular custom function in a sheet you can call recalcCustomFunctions
or use own code like:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  sheet.insertRowBefore(1).deleteRow(1);

If you can describe what kind of custom results you would require to work with your usecase, i can take a look if i might be able to support such in the add-on.

[ljay79]

Btw: This add-on is open source, if you really want to start creating custom scripts for you usecase, there is the the option to adopt/use the code available here in the repository to create you own add-on/script.