Custom Formulas - Adding cell ranges as parameter

[Vince Lukkesen]

Hello,

I am trying to add cell ranges as a parameter in custom formulas, is there a way to do this? I can't seem to find anything about this in the documentation.

Example:

this.spreadsheetObj.addCustomFunction(
  (cellRange) => createIRRFunction(cellRange),
  'IRR'
);

The formula in the spreadsheet would be used like this "=IRR(A1:A5)". If I log the cellRange parameter in the "createIRRFunction(cellRange: any)" function, I just get the string "A1:A5", but how can I access these values??

[Vasanth Ravi]

Hi Vince,

We have checked your requirement and prepared a sample for your convienence. You can use the get the index value from the passed range through getRangeIndexes() method and using the index value you can iterate between the range and using getCell() method you can fetch the value and check it out. Please do refer to the sample provided.

CODE BLOCK:

 

public IRR(cellrange) {

        var index = getRangeIndexes(cellrange); // pass the address

        var sheet = this.spreadsheetObj.getActiveSheet();

        var cellValue = [];

        var cell;

        for (let i = index[0]; i <= index[2]; i++) {

          // for column iteration

          for (let j = index[1]; j <= index[3]; j++) {

            cell = getCell(i, j, sheet);

            if (cell) {

              cellValue.push(cell.value);

              console.log('Cell Value: ' + cell.value);

            }   }   }

     return 'success';

   }

    created() {

        this.spreadsheetObj.addCustomFunction(

            this.IRR.bind(this),

            'IRR'

        ); }

Stackblitz Sample: https://stackblitz.com/edit/angular-7shs9d?file=app.component.ts

Get back to us if you need further assistance regarding.

Regards,

Vasanth R