codeforboston
Remove timestamp from feedback google sheet data?
I could see how that could be used to identify a person, especially when the app is new.
Hm, maybe. I would argue that it still wouldn't be that hard to identify someone if we're only testing with a few users at a time and checking for new feedback after each session.
If we do decide to do this though, the way we have it set up now makes it a little hard... From https://developers.google.com/apps-script/guides/triggers/installable#google_apps_triggers
Script executions and API requests do not cause triggers to run. For example, calling FormResponse.submit() to submit a new form response does not cause the form's submit trigger to run.
We could maybe instead use an "open" trigger for the spreadsheet (although that only runs if the person opening it has permission to edit), or a time-based trigger, to clear the "Timestamp" column. I don't think there's a way to prevent form submissions from being timestamped in the first place, but I'm not sure.
You've got a good point. I guess I just want to make it a little bit harder, but maybe that's not really the threat profile we're dealing with here. Also, dagnabit, I forgot about that google docs quirk. Isn't there anything to respond to a push request or anything? Hmmm.
If that's not the case, I guess either of those two triggers are probably the best shot we've got if we want to do this thing. I just don't see why we'd need the timestamps and it feels a bit better (to me) to strip out as much of the unnecessary information as we can.
Another thought is to make it a practice to delete an entry once we've dealt with the feedback with which it's associated. Maybe I'll make an issue for that, too.
I'll be researching this over the next few days, the fix may be as simple as tweaking the formula to just show the date.
So, it turns out that you can hide a column pretty easily in Google Sheets. If all users that we don't want to see timestamps don't have write access, this should be pretty airtight,
Hmm, that's a simple solution visually, but can the data for that still be found?
Yes, but only by users with edit privileges only. Hard to say for certain since the official docs seem.....non-existent?
Possibly. Does this information end up on the sheet automatically? I feel like if the information creates that much of a liability, it shouldn't be there period.
Honestly, perfect security isn't possible. I looked at the console on Google Sheets for a few minutes and as far as I can tell, it's pretty opaque - might be possible to extract data through there but it's not clear how. Maybe there's an easier way to see data that's been hidden without editing. But at some point I feel we have to ask what type of "attacker" we're really protecting against.
The biggest threat is probably ourselves, when reviewing feedback, associating comments with the people who made them based on the time or order of submission, even if we don't intend to. Hiding the timestamps helps with that (although we could still accidentally unhide them, so that's not ideal). If we could also randomize the order each time somehow, that would obscure the order of submission - not sure how easy that would be for a sheet managed by Google Forms though.
You have several good points, but looking at the html, I found the timestamps pretty easily.
Hm, I see one timestamp as part of what looks like a description of the sheet for previews or something, but it cuts off after just a little bit of the first client data structure. This was by doing a Ctrl-F for "2018". Is that what you found or did I miss something?
No, not that. It's in places like this:
[
null, null, 3,
[ null, 3, null, 43213.94681386574 ],
null, null, 2
]
That number is a legacy timestamp created from a bug in Lotus way, way back. It's the number of days since Dec 30, 1899. https://stackoverflow.com/a/3963650
If you want, you can put some nonsense word in a column, hide it, and then search for that.
Whoa OK, that's pretty wild! I guess it makes sense that it would store dates relative to some epoch rather than in human-readable form though.
This actually seems like it might be an artifact of using a form as a middleman between our code and the google sheets. Might be worth trying this instead: https://medium.com/@dmccoy/how-to-submit-an-html-form-to-google-sheets-without-google-forms-b833952cc175