I have a workflow that pulls time card entries out of one database, processes them, and plops them into a Google Sheet which feeds another app. This workflow is working well, except occasionally, instead of appending the JSON payload to the end of the google sheet, it appears to paste at kind of a random middle point and overwrite everything it pastes on top of.
The only thing I can think of is that sometimes while troubleshooting people's time card issues, I have to use a data filter on the underlying Google sheet.
Is it a known issue that Append works weird if the sheet its appending to has data filters applied? Is there some other obvious thing I'm missing?
Hey Phil! Yikes, this definitely shouldn't be happening. Apologies for the delayed response—we're working through a bit of a backlog!
Could you clarify what you mean by data filters? A screenshot would be great! A screenshot of your append query would be helpful as well.
No worries @victoria !
So basically, if I take the underlying Google Sheet and hit Create a Filter and then filter on some value, it shortens the sheet and shows only the rows that match the filter, see screenshot 1 below.
If ReTool happens to run an automation that appends new rows to that sheet while I have the filter applied, it looks like it treats the last filtered row as if it were the actual last row and appends to the next rows in the series. In the case of this screenshot, it would treat rows 14167+ as if they don't exist even though they do exist, just filtered out. This causes it to overwrite those rows.
You can see in the second screenshot the Google edit tracking showing ten rows dumped into the middle of the sheet, overwriting the previous values. The row just above was for a guy named Josh Cornell and I had been filtered on him when the automation ran, so it overwrote the ten rows just below his last row.
Thank you for the explanation! I *think* this might be expected after checking Google. In fact, there's another (non-Retool) forum question that seems to be similar to yours:
https://community.n8n.io/t/google-sheet-append-doesnt-append-rows-on-a-filtered-sheet-insertdataoption-param/7591
When you have a filter applied to a Google Sheet and new rows are appended to the sheet through an automation or any other means, it is expected behavior for Google Sheets to treat the last visible row (based on the filter) as the last row for appending new data. This is because the filter is a visual representation, and any actions performed on the sheet, including appending data, consider only the currently visible rows.
If you want to ensure that the automation considers all rows, including the ones currently hidden by the filter, you may need to see if it's possible to clear the filter before running the automation and then reapply it afterward if necessary.
Let me know if you have any questions!
