Dragging and reordering rows can break queries

[danepowell]

Note that this is a bug report for v1.1. This problem appears to be solved in v2, but I wanted to post an issue in case any other users of v1.1 ran across it.

If you try to reorder rows on the Bundles sheet by dragging them around, this can break the rest of the spreadsheet in unexpected ways.

You can recreate this by simply dragging a row from midway down the bundles sheet up into the first row of the sheet (actually row 2 since there's a header).

This will cause the bundles query to "helpfully" automatically adjust itself to only use row 3 and onward.

[danepowell]

Actually, there's another place where dragging rows breaks things, even in v2: if you drag rows around on the fields tab, all of the conditional formatting goes haywire. Maybe it's best just to advise people not to do that...

[TravisCarden]

Thanks, @danepowell. The first problem of broken queries was a known issue in v1.x which has indeed been solved in v2.0.0. Unfortunately, I have no way to get the fix out to users of v1.1. Since there's no update delivery mechanism for Google Sheets, each version does become effectively unmaintainable once released. (If you have thoughts on that problem, I'm all ears!)

As to the conditional formatting going haywire when reordering rows on the "Fields" tab, I'm aware of a problem with zebra stripes breaking in that scenario. Do you see other formatting breaking?

[danepowell]

The two major problems were the zebra stripes (aka conditional formatting), and the hidden "type" column. For some reason when you drag rows around, the "type" column becomes populated with literal text values, and this breaks the auto-population function. I've confirmed both of these happen in v2

[TravisCarden]

I may be able to make the zebra stripes code more resilient. I'm not sure the issue with the "Type" column is surmountable, though. I wonder if the column is worth the trouble. What do you think, @danepowell?

[danepowell]

I'm actually not sure what that column does, since it's normally hidden right? I'm okay either way, if we decide not to fix it then maybe just document "avoid dragging rows".

[TravisCarden]

The column doesn't actually "do" anything in the sense of contributing to validation, Behat tests, or diagrams. It's purely informational. And it's brittle in more than one way. 🤔 I think I'm going to get rid of it. I'm not willing to make a concession about such a basic behavior as dragging rows unless absolutely unavoidable.

[TravisCarden]

@danepowell, I've decided to remove the zebra stripes until they can be implemented more robustly. As before, I'm not willing to recommend that people give up common spreadsheet features and behaviors to compensate for the quirks of a nicety. I'm closing this issue "fixed". Feel free to reopen it or create a new one if you discover anything else. Thanks!