Styjun

So I've been trying to build an AADD(range, range) function. It's not complete so don't rag me about that. What's bothering me at the moment is that what the Script Editor says in meaningful code and what Google Sheets will run appears to be different.

First of all here's the tester:

function test_AADD() 
 var sheet = SpreadsheetApp.getActiveSheet();
 var range1 = sheet.getRange("B2:E2");
 var range2 = sheet.getRange("B3:D3");
 var result = AADD(range1, range2);

The ranges are deliberately not matching in length because I'm trying to work out how to send back standard error messages like "#ERROR!" (some help there would help too.)

Here's the public AADD function. I hope one day to find out how to make functions private (another thing I need help with.)

/**
 * Adds two arrays
 *
 * @param Range range1 The first parameter
 * @param Range range2 The second paramter
 * @return Range
 * @customfunction
 */
function AADD(range1, range2) 
 return fnAADDrr(range1, range2);

The (ultimately) private function that implements the AADD code (trust me, there's meaning to the madness) is

function fnAADDrr(range1, range2) 
 var r1len = range1.getNumRows() * range1.getNumColumns();
 var r2len = range2.getNumRows() * range2.getNumColumns();
 if (r1len !== r2len) 
 return new Error("#ERROR!");
 

That's as far as the function goes at this point because I'm just trying to work out how to test for a Range not having as many items as another.

So now we get to the main problem: When I have the script, the script editor does not complain about the method calls on the passed-in Range variables range1 and range2. And when I run the test_AADD function in the script editor, I get a mostly sensible response (except that the "new Error" thing isn't actually returning -- something else I need help with.)

So then I move back to my Google Sheets file. In a cell I type

=AADD(B2:D2,B3:D3)

Now this isn't even going to fire the "new Error" bit because the Ranges are the same size. Even so, I get an "#ERROR!" which, if I hover over it with the mouse I get

TypeError: Cannot find function getNumRows in object 1,2,3 (line 73) 

where 1,2,3 is what's in B2:D2, and line 73 is the location in the script file.

So how come script editor and Google Sheets can't agree on what's allowed?