Javascript – TypeError: Cannot read properties of null (reading 'getformula')

Apparently the code is intended to be used as custom function in a Google Sheets formula.

In this cases, it’s a good practice to include /** @customformula */ immediately above the function as is suggested in https://developers.google.com/apps-script/guides/sheets/functions.

The error message is caused becuse SpreadsheetApp.getActiveRange() returned null. In the paste I avoided using this method because it allways returned null in my projects but I just did a quick test and it worked fine.

To learn more about the error in Google Apps Script, please see Reference : TypeError: Cannot read property [property name here] from undefined

Below is my quick test custom function. It returns the cell reference using A1 notation.

/** @customformula */
function myFunction(){
  return SpreadsheetApp.getActiveRange().getA1Notation();
}

To test this,

1. Create a new spreadsheet.
2. Click Extensions > Apps Script.
3. Set the Apps Script project name.
4. Resplace the code in Code.gs with the above code.
5. Save and authorize the Apps Script project.
6. Go back to the spreadsheet, on any cell write =myFunction() and press Enter.

The cell will display Loading... briefly, the it will display the cell reference.

Considering the above, in your case, the exact reason for got the error depends on how you executed the Google Apps Script function, but it might be worth to try again after fixing the other errors.

In the previous answer it was mentioned a typo on SpreasheetApp.Range.getFormula method (used f instead of F, JavaScript is case sensitive). There are errors in the regular expressions, maybe introduced by the Stack Overflow editor when pasting the code.

The below code works fine for me.

• =countColoredCells(C1:C7, B1).
• The value of B1 is #ffffff.

/** @customformula */
function countColoredCells(countRange, colorRef) {
    var activeRange = SpreadsheetApp.getActiveRange();
    var activeSheet = SpreadsheetApp.getActiveSheet();
    var activeformula = activeRange.getFormula();
    var countRangeAddress = activeformula.match(/((.*),/).pop().trim();
    var backGrounds = activeSheet.getRange(countRangeAddress).getBackgrounds();
    var colorRefAddress = activeformula.match(/,(.*))/).pop().trim();
    var BackGround = activeSheet.getRange(colorRefAddress).getBackground();
    var countColorCells = 0;

    for (var i = 0; i < backGrounds.length; i++)
        for (var k = 0; k < backGrounds[i].length; k++)
            if (backGrounds[i][k] == BackGround)
                countColorCells = countColorCells + 1;
    return countColorCells;
};