I’m working on a project where I need to build an inventory tracking system in Google Sheets using Google Apps Script. I’ve run into a weird problem with structured table references.
When I use a formula like myTable[columnHeader] in the bottom row of my table through the script, that row somehow gets kicked out of the table entirely. At first I thought my code was broken, but it turns out this happens even when you do it manually.
Has anyone else seen this behavior? Is this how it’s supposed to work?
How to reproduce this issue
Create a new Google Sheet and add headers “Field A” and “Field B” in row 1
Highlight cells A1 through C4 and go to Format > Convert to Table
Give your table the name “inventory_table”
Put some test data in the “Field A” column
In each “Field B” cell, manually type =inventory_table[Field A] (don’t use the autocomplete)
What should happen
The “Field B” column should show the values from “Field A” in each row.
What actually happens
The bottom row gets removed from the table and shows a #VALUE! error instead.
Before adding the formula to the last row, everything looks normal and the row is part of the table. But as soon as you enter that formula, the row disappears from the table range and you get this error message:
The default output of this reference is a single cell in the same row but a matching value could not be found. To get the values for the entire range use the ARRAYFORMULA function.
Yes, this behavior is typical for Google Sheets when dealing with table references. When you input inventory_table[Field A] in a table cell, Google Sheets interprets that as an instruction to reference the entire column, which can create a circular reference situation, leading to the exclusion of the current row and resulting in a #VALUE! error. To resolve this, consider using direct cell references such as =A2, placing your formula outside the table, or utilizing =INDEX(inventory_table[Field A], ROW()-1) to effectively refer to the current row without triggering the error. I’ve encountered similar issues while setting up tracking sheets, and it’s a common pitfall with structured references within tables.
This happens because Google Sheets treats structured references like array formulas, which creates conflicts when you’re working inside the same table. It’s trying to reference the whole column while being in that column - so it excludes the current row to avoid infinite loops. I hit this exact problem building a procurement dashboard last year. Two workarounds that actually work: wrap =INDIRECT("inventory_table[Field A]") in an INDEX function, or just move your calculated columns outside the main table entirely. You can also ditch structured references and use regular cell references like =A2 when you need row-level calculations inside the table.
yup, i ran into this issue too, it’s frustrating! sheets treats the table reference like an array formula, kicking the row out to avoid loops. a simple fix is to use standard references like =A2 instead of structured ones while inside the table.