OK so I ran into this again. This definitely looks like a Retool shortcoming to me now.
The root cause is that Google Sheets has a pretty complex logic for deciding what "the table" is in a given Sheet. And there might be several "tables" detected (by Google) within a Sheet.
Details are here.
For this reason, Google specifies that when appending, it's important to specify the correct range. However, Retool automatically fills in an update range of A1:ZZ1 and there is no way to change this. So this results in the wrong "table" being detected, and thus data being appended with a shift applied.
See also e.g. this bug report on a related toolkit. (many other bug reports on the same project, it's a well known gotcha with Google Sheets append)
One fix would be for Retool to support setting the range manually, overwriting the A1:ZZ1 default.