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

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

Solution

try non-active referencing:

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

Convert an array of object into 2-dimensional array to group the elements with same id in typescript
Local array variables in Bash 3.0
How to sum php array that have same key but different sensitivity
case-insensitive array_unique
Check if object with property = value exists in array of objects
Snowflake ARRAY column as input to Snowpark modeling.decomposition
How to swap the positions of Min and Max in an array?
Access the key of a flat array and parse the delimited key string into an array
Can i convert array in string to array?
Set the highest priced variation as default in WooCommerce variable products
Parse HTML content into an associative array?
Parse a delimited string containing key-value pairs resembling PHP's array-syntax into an associative array
Combine Multilines in one element of array
Is there a way to create dynamic ranges given an array of values, and counting how many belong to each range
Finding min and max in array Javascript
Python: slicing a multi-dimensional array
How to count all objects in an array that match a condition?
Why `[1, 2, 3]` is not showing as an `Array(3)` in Console?
Cannot memory-free strings from arbitrary-length array of strings
Explode delimited string into a 2d array with a specified key in each single-element row
Why there is no F# immutable arrays?
How to convert two longs to a byte array = how to convert UUID to byte array?
C++ constexpr operations on each element of a constexpr array
is there any array reverse indexing in c like python's a[-1]?
Displaying binary images on JSP page
Linear search in a sorted array - Java
Convert flat array of delimited key path strings to a multidimensional array with each path terminating at an empty array
How to iterate through an array of pointer in c
Given an array of N integers, return the max sum of 3 NON-adjacent elements
Parse array of strings composed of dot.separated.keys into a multidimensional array