Fix Missing Headers in Google Sheets Array LET Formulas
Iwan Efendi2 min
Fix missing table headers in Google Sheets array formulas. Learn how to exclude the first row from REGEXMATCH filters using SEQUENCE and ROWS.
Baca dalam IDID
I found a frustrating bug in my procurement monitoring spreadsheet: the "ID GR" column header (column 27 in the query output) suddenly disappeared. The formula hadn't changed, and the source sheet data was clean, which left me scratching my head for a few minutes before I realized what went wrong.
The root cause lies in how I defined the
Because
Here is the complete corrected
Debugging silent bugs like this is always a bit tricky. Everything looks fine on the surface, but the underlying logic is breaking in the background without throwing a formal error.
Whenever you apply cleanup filters or
Let me know if you've encountered similar header headaches in Google Sheets, or if you have a different way of handling them. Hopefully, this save-the-header pattern saves you some troubleshooting time on your next spreadsheet project!
_c27 variable inside the LET formula to filter the GR numbers. I was using REGEXMATCH to extract only numeric values:
_c27; ARRAYFORMULA(IFERROR(
IF(REGEXMATCH(INDEX(_data;;27)&"";"^\d+$"); INDEX(_data;;27); "");
""
));
REGEXMATCH only returns TRUE if the entire cell is numeric, it worked perfectly for the data rows. But the first row is the header text: "ID GR". Since "ID GR" contains letters, the regex returned FALSE, and the formula silently emptied the header cell.
In spreadsheet arrays, headers are just the first row of your data, making them easy to accidentally filter out when applying column-wide rules.
To fix this, we need to explicitly exclude the first row of the array from the regex check. I did this by adding a helper sequence: SEQUENCE(ROWS(_data))=1. This generates a boolean array where only the first row is TRUE (e.g., {TRUE; FALSE; FALSE; ...}). By adding this condition using the + operator (which acts as a logical OR in array formulas), we can preserve the header regardless of its content.
1
Locate the Filter Variable
Find the variable in yourLET function that applies the regex or condition. In my case, it was _c27.2
Apply the Sequence Bypass
Update theIF condition to use (SEQUENCE(ROWS(_data))=1) + [your_condition]. The updated variable assignment should look like this:_c27; ARRAYFORMULA(IFERROR(
IF(
(SEQUENCE(ROWS(_data))=1) + REGEXMATCH(INDEX(_data;;27)&"";"^\d+$");
INDEX(_data;;27);
""
);
""
));
3
Verify the Header Recovery
Check your spreadsheet output. The header"ID GR" should reappear in the first row, while the data rows below continue to filter out non-numeric entries.LET formula in action:
=LET(
_id; "1c1GqBT1FeI6wFtTfXRQZElXcjRWHWCcocYYxCBJ7gNU";
_r1; IMPORTRANGE(_id; "Konstruksi!B1:AP5000");
_r2; IMPORTRANGE(_id; "Close!B3:AP5000");
_r3; IMPORTRANGE(_id; "Rutin!B3:AP5000");
_r4; IMPORTRANGE(_id; "PO Reg_HO!B3:AP5000");
_raw; VSTACK(
QUERY(_r1; "select * where Col1 is not null offset 1"; 1);
QUERY(_r2; "select * where Col1 is not null"; 0);
QUERY(_r3; "select * where Col1 is not null"; 0);
QUERY(_r4; "select * where Col1 is not null"; 0)
);
_c1; ARRAYFORMULA(IF(INDEX(_raw;;1)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;1)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c2; ARRAYFORMULA(IF(INDEX(_raw;;2)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;2)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c3; ARRAYFORMULA(IF(INDEX(_raw;;3)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;3)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c6; ARRAYFORMULA(IF(INDEX(_raw;;6)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;6)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c7; ARRAYFORMULA(IF(INDEX(_raw;;7)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;7)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_data; HSTACK(
_c1; _c2; _c3;
CHOOSECOLS(_raw;4;5);
_c6; _c7;
CHOOSECOLS(_raw;SEQUENCE(COLUMNS(_raw)-7;1;8))
);
_col34; ARRAYFORMULA(IF(INDEX(_data;;34)="";"";REGEXREPLACE(INDEX(_data;;34);"^\d+\.\s*";"")));
_col35; ARRAYFORMULA(IF(INDEX(_data;;35)="";"";REGEXREPLACE(REGEXREPLACE(INDEX(_data;;35);"^\d+\.\s*";"");"(?i)DROP LOKASI";"DROP")));
_c27; ARRAYFORMULA(IFERROR(
IF(
(SEQUENCE(ROWS(_data))=1) + REGEXMATCH(INDEX(_data;;27)&"";"^\d+$");
INDEX(_data;;27);
""
);
""
));
IFERROR(
HSTACK(
CHOOSECOLS(_data;SEQUENCE(26));
_c27;
CHOOSECOLS(_data;SEQUENCE(6;1;28));
_col34;
_col35;
CHOOSECOLS(_data;SEQUENCE(COLUMNS(_data)-35;1;36))
);
""
)
)
Why did this bug suddenly appear?
The formula had been running fine for months, and I'm sure the header used to display correctly. It's highly likely that Google Sheets updated its underlying evaluation engine or changed how array formulas parse mixed data types. What worked fine yesterday suddenly became a silent failure, causing the header cell to go blank without throwing any formula errors.
REGEXMATCH on columns inside a LET function, always remember that your first row holds your table header. You can use this simple pattern to bypass filters on the first row of any range:
IF(
(SEQUENCE(ROWS(_range))=1) + [your_filter_condition];
_range;
""
)
Topics
Topics in this note
Explore related ideas through the topics connected to this note.
Share this article
Discussion
Preparing the comments area...