Formula references in Sheet 1 change after updates to Sheet 2 with Sheets API-CodePudding

Pretty new to the Sheets API, but something is not clicking...

I have a formula in Sheet1 that looks like...

AVERAGE('Sheet2'!A1:A5)

The result of this formula shows correctly when I create it the first time if there is data in Sheet 2. Then, I append and update data to Sheet2 through the Sheets API. The data is inserted properly, the values in Sheet2!A1:A5 remain the same after the updates are done...

But the cell references in the formula have changed, they still refer to the A colum of Sheet2 but the rows are no longer 1 to 5...

What could be wrong here...? Seems counter intuitive

CodePudding user response：

try non-active referencing:

=AVERAGE(INDIRECT("Sheet2!A1:A5"))