Update an ArrayFormula from code

Update an ArrayFormula from code

Posted by: ismail00b on 25 February 2020, 3:13 pm EST

• 

  ismail00b

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 25 February 2020, 3:13 pm EST

  Hello,
  
  Is there a way to setArrayFormula on an existing ArrayFormula range, without getting the error : Uncaught Cannot change part of an array.

  Cordially

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 26 February 2020, 3:19 am EST

  Hi,

  To be able to update the array formula, the range for the new array formula should be the same as the existing range or you need to clear the existing array formula first. Please refer to the following code snippet which demonstrates how we could clear the existing array formula:

  let sheet = spread.getActiveSheet();
  
    let formulaInfo = sheet.getFormulaInformation(6, 4);
    if (formulaInfo.isArrayFormula) {
      // clear formula
      sheet.setArrayFormula(
        formulaInfo.baseRange.row,
        formulaInfo.baseRange.col,
        formulaInfo.baseRange.rowCount,
        formulaInfo.baseRange.colCount,
        null
      );
    }
  
    // new formula
    sheet.setArrayFormula(6, 4, 2, 2, "=B3:B4*B3:C3");
  

  Regards
  
  Sharad

• 

  ismail00b

  • Post Options:
  • Link
    Copy

  Posted 26 February 2020, 4:30 am EST

  Great, it worked.