I saw this recent post about this same error and that post was closed because the error couldn't be reproduced. I received the same error last night and it's easily reproducable. Not sure how related the two are, but here's what happened to me.
I was trying to determine the furthest row that is reachable. For example, even if your sheet only has the default 1000 rows, you can write =ROW(OFFSET(A1,9999,)) to reach A10000. It'll return 10000. After some trial and error, I found that I could get the furthest using a nested OFFSET.
=row(offset(offset(offset($A$1,B1,),C1,),D1,))
The last reachable row turns out to be row 2,147,483,647. If I tried going to 2,147,483,649 or further, I got an OFFSET evaluates to an out of bounds range error. But right before that, I found a wormhole. That's right, I tried reaching row 2,147,483,648 using =row(offset(offset(offset($A$1,700000000,),700000000,),747483647,)) and what I got back was -2,147,483,648. You can see the results below (Col E holds the checkboxes used to turn onOff the formulas).
| B | C | D | E | F | G |
|---|---|---|---|---|---|
| 700000000 | 700000000 | 747483646 | TRUE | 2147483647 | =if(E1,row(offset(offset(offset($A$1,B1,),C1,),D1,)),) |
| 700000000 | 700000000 | 747483647 | TRUE | -2147483648 | =if(E2,row(offset(offset(offset($A$1,B2,),C2,),D2,)),) |
| 700000000 | 700000000 | 747483648 | TRUE | #REF! | =if(E3,row(offset(offset(offset($A$1,B3,),C3,),D3,)),) |
Like a wormhole, it goes to the otherside of the sheet universe. I could go left/right on that row, and even step back through to this side, but I wasn't able to move up/down any rows on that side. Strange, yes, but not detrimental and I do plan to report it.
However, later when I tried opening my personal addon, I got the following runtime error. Exception: Service Spreadsheets failed while accessing document with id ####. [line: 77, function: myGlobalConfig, file: core/lib/Config.
After struggling to identify the problem, I had the wild notion to check if the wormhole was the source. It was, too. I received the error when I activated that particular formula. The issue vanished when I disabled that formula.
I even created a basic function in the bound script to retrieve the name of the sheet and use Toast to display it. When that middle formula was activated, the identical error occurred, yielding the value of the negative row. The error disappears when it is turned off.
Is this connected to the previously mentioned other post? Does anyone know why the row is negative?
2,147,483,647 is 2^(31 - 1). Your number is represented on 32 bits, like
B31 B30 ... B0
and you can numerically represent them in the number-base of your preference (probably base 10) as:
2^31 + 2^30 + ... + 2^0
This number can be represented in an unsigned or a signed manner. When we consider it as an unsigned number, then B31 is considered to be a binary component of the number, actually 2^31. In this case you can represent integer numbers between [0, 2^32) = [0, 4294967296) = [0, 4294967295].
However, if the number is represented as signed, then the first bit of the number is the so-called sign bit, so B31 is not part of the number's absolute value, but shows its sign, 0 means positive and 1 means negative. This way you can represent integer numbers between [-2147483648, 2147483647].
Since 2147483647 is the last number to be represented, as a binary number it looks like this:
0111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111
Now, if you add a number to it, then you get
1000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000
which has the sign bit on 1, so it's a negative number, which equals to -2^(-31) = -2147483648.
So if you add one to the greatest positive number on a finite number of bits, then you increment a number represented with a leading 0 (the sign bit) and all 1s following, which overflows into a negative number that is being represented with a leading 1 (the sign bit) and all 0s afterwards. This is what happened in your sheet.