Google Sheets – Use External Add-on Function in Custom Function

google sheetsgoogle-apps-script

I installed an Add-on for Google Sheets which has a function myAddOnFunction that I can call in any cell and it works great and shows the value I expect.

I am writing my own custom function in the script editor and want to call myAddOnFunction() there for some calculations, but when I try to call it I get a ReferenceError.

How can I access myAddOnFunction() inside script editor?

Best Answer

In short, you cannot access myAddOnFunction() programmatically.

However, what you could try is simply inserting the necessary text into your target cell. For example:

var cell = yourRange.getCell(1, 17);
cell.setValue('=myAddOnFunction(yourParameters)');

If the add-on is designed to execute functions when present in cells, inputting that string into a cell would trigger the add-on.

Related Solutions

Google-sheets – How to use Google’s functions in a spreadsheet’s script

In addition to the remark made by Barry, here's a link that explains the usage of XML in combination with Google Apps Script (GAS): XML with GAS

You can use the importXML formula in GAS, but you have to address it as a formula:

function myXML() {
  var cell = SpreadsheetApp.getActiveSpreadsheet()
    .getActiveSheet().getActiveCell();
  cell.setFormula('=importXml("http://www.google.com", "//a/@href")');  
}

Reference: setFormula

Google Sheets – Non-Deterministic Parameters to Custom Function

I've made some research and it seems like an old problem, even from old MS Excel versions.

I think the best/easiest way is to create your own randomization function. This way random value won't be regenerated. You use it the same way as before =plusOne(myRandom())

function plusOne(i) {
  return i + 1;
}

function myRandom() {
  # Some function
  return Math.random();
}

You could also shorten it =myRandom()

function myRandom() {
  # Some function
  return Math.random() + 1;
}

Best Answer

Related Solutions

Google-sheets – How to use Google’s functions in a spreadsheet’s script

Google Sheets – Non-Deterministic Parameters to Custom Function

Related Topic