Leo

I built a reporting file on twelve separate IMPORTRANGE calls and someone renamed a tab in one source, which killed the lot on a Monday morning. Now I import whole sheets as a wide range like A:Z into hidden staging tabs and do all the slicing locally with QUERY. Fewer strings to break, and when something does break it breaks in one place I can find.

TODAY in a days-since column that everything else references. Replaced it with a single cell holding TODAY and pointed the column at that cell. From 18 seconds to under two.

Ctrl+end took me to row 26,914. I have no memory of ever putting anything down there.

The worst-month rule is the bit people skip. I set mine off a good spring and then spent all of January moving money back in, which defeats the entire point of a fixed wage.

If you truly cannot restructure, put every changeable value in a config tab and have all twelve tabs reference it. Category names, thresholds, date boundaries, all in one place, and the formulas themselves stop needing edits because only the inputs change. It is not as clean but it turns twelve edits into one.

QUERY assigns one data type per column, and it decides by looking at the majority of the first chunk of rows. Whichever type loses gets returned as blank, not converted. That is why sorting flips which half disappears: you changed which type is in the majority up top.

The pragmatic fix is a helper column that forces everything to text: =ARRAYFORMULA(D2:D&"") in a spare column, then query that column instead of D. Ugly, reliable, done in thirty seconds.

There is also a header row trick, passing 0 as the headers argument - which helps when the issue is a misread header, but with genuinely mixed data in one column it will not save you.

Good call, and I reach for FILTER first these days for exactly this reason. QUERY earns its keep on group by and pivot, not on plain where clauses.

Almost everyone assumes that. You can even see it, print add_row.defaults after a couple of calls and you will watch the list grow.

I ran five nights for seven months and then lost about six weeks completely, where I did not open the repo at all and felt sick when I thought about it. The lost six weeks cost more than the extra nights had gained, and worse, I came back to a codebase I had written while exhausted and spent another fortnight understanding my own decisions. If I could send one message back it would be that tired code is a loan with a bad interest rate.

The default value is created once, when the def line runs, not each time you call the function. So there is exactly one list living on the function object, and every call that does not pass its own list appends to that same one. The fix is the None sentinel: take rows=None as the default, and on the first line of the body do rows = [] if rows is None else rows. Same trap applies to dicts, sets and anything else mutable you put in a default.

I changed a shared file's locale to fix exactly this and broke a column of dates that four people entered over six months. Took me longer to unpick that than it would have taken to retype every formula in the file by hand for a year.

The print server one is embarrassingly obvious and solves an actual daily annoyance here. Starting with that.

Handy debugging habit: pull each piece out into its own cell as a standalone array first. =ARRAYFORMULA(D2:D > E2) on its own shows you immediately that it produces one value rather than a column. Testing the pieces separately finds these in a minute instead of an afternoon.

If you can change the import at all, that is the cheaper fix. In the CSV import dialog turn off convert text to numbers and dates, so nothing gets guessed, then convert deliberately with the formula above. Half the pain here is Sheets being helpful at the wrong moment.

Two practical warnings on the IMPORTRANGE route. Each source needs authorising once, and a single broken one shows as one #REF that takes down the whole stack, so leave a note about which row range belongs to which site. And IMPORTRANGE refreshes on its own schedule, so numbers can be a few minutes stale: fine for a tracker, not fine if someone is reading it live on a call.

Add the site name as a column inside each source tab rather than trying to add it during the stack. Much easier to read later and it survives reordering.