Randomly losing edits made collaboratively in Google Sheets
November 30, 2018 1:10 PM   Subscribe

I'm having a problem with a Google Sheets spreadsheet that's being edited by multiple people. About 98% of the time it's fine. But some of the edits are randomly disappearing, and this is causing big problems.

I used Google Forms to create a ticketing system at work. The system is crude, but people seem to like it. Well, except for one big problem, which I've explained further below.

The data from the submission form gets fed into a Google Sheets spreadsheet. On the spreadsheet, I manually added another column called "Status" (in other words, "Status" is not one of the fields that gets submitted through the Google Form).

There are about 15 people who take care of the tickets that are submitted. These people enter notes into the "Status" column. Most of the time, everything works fine. But people keep complaining to me that their notes sometimes disappear. There is no apparent pattern to it -- it happens randomly, to random people, on random machines.

Of course, these disappearing notes wreak havoc on the normal workflow. If I can't fix this problem, I'll have to switch away from Google Forms/Sheets and use something else entirely.

I've instructed people to use Google Chrome exclusively, and I told them to refresh the screen before they make any edits. I'm not sure if they follow either or both instructions. Our PCs are all running Windows 10, and I've set permissions on the sheet so that anyone with the link can edit it. I'm fairly confident that this problem is not the result of malicious behavior.
posted by akk2014 to Computers & Internet (11 answers total)
Is there any chance emojis are being used? I have found previously that they just do not play nicely with Gsheets or Gdocs at all and have caused me to lose data.
posted by teststrip at 1:15 PM on November 30, 2018

No, there are no emojis. Just normal text.
posted by akk2014 at 1:17 PM on November 30, 2018

Do you have it set to "off-line mode" permitted? I lost data on a tracking sheet I was using for wedding planning, and I found it's because I could open it on my iPad but it wouldn't always sync before I closed it.
posted by DoubleLune at 1:21 PM on November 30, 2018

If you hit Ctrl-Alt-Shift-H, can you find a version of the spreadsheet in the history that has the data in it?

If not, one thing to check might be the connectivity of the editors, as well as if the changes are being saved - there are a few different metrics that Google uses to decide whether to create a savepoint. See here for one explanation I found; it sounds like there are a variety of pages where it's mentioned. (I just searched for "How often does google docs save")
posted by sagc at 1:21 PM on November 30, 2018 [1 favorite]

I'm not sure how well people know Google Sheets but is it possible they are typing into the box and then navigating away from the box before the text "enters"? I have a Sheet I use mostly like this and have never experienced the same thing, so I'd want to rule out user error before scrapping the idea.
posted by jessamyn at 1:28 PM on November 30, 2018 [1 favorite]

Sheets has problems with text wrapping which causes weird format issues.

It could also be a problem with users hitting 'enter' for a line break. In sheets, that drops you to the next call, which may be getting overwritten when the next row is populated by your form submission. Make sure your users know to do Ctrl+enter for a hard return.
posted by Think_Long at 3:08 PM on November 30, 2018

Pebkac, probably. I have/had a similar problem at work and could never figure it out, and it only ever happened to the same 1 or 2 people. I kindly reinforced the idea that they need to remember to actually submit the form and the problem was mysteriously solved.
posted by Literaryhero at 7:05 PM on November 30, 2018

Wait, are they entering forms or sheets? As far as I know sheets should automatically update and I have no idea what could be happening then.

Actually! I do have one colleague that was always complaining that their Google Doc wasn't saving and that is why they were missing deadlines, but I assume that was just a "dog ate my homework" thing. If this is happening to multiple users that's weird.
posted by Literaryhero at 7:09 PM on November 30, 2018

@LiteraryHero: Just to clarify, there is no problem with the form submissions themselves. Those always work fine. The disappearing edits occur in the "Status" column of the Google Sheet that is the target of the form submissions.
posted by akk2014 at 7:22 AM on December 1, 2018

I don’t know if this will help but I find that the sheet where Google dumps the form replies is special and doesn’t actually like to be touched. I make a new tab that pulls in all of the form responses using the query formula and then you can add whatever columns you want. That might fix the wonkiness.
posted by bleep at 11:28 AM on December 1, 2018

Now that I'm home and on my computer rather than my phone I can give you the actual formula. This is taken from an actual spreadsheet I'm using right now.

=query('Form responses'!A:C,"select *")

Make a new sheet and put this in the first cell.
Where it says A:C here, that represents the columns you want to pull in, and then "select *" means pull in all of them in order.

Now you can add new columns to the right.
posted by bleep at 2:35 PM on December 1, 2018

« Older Where should I eat in Philadelphia?   |   Yellow (or "gold") and black, wool, striped scarf... Newer »

You are not logged in, either login or create an account to post comments