Update lesson to work with google sheets

[colinquirk]

It's not clear to me if this lesson would work with google sheets out of the box, but considering there is a very small cost to getting google sheets working on your personal machine, I think it would be a nice alternative to libreoffice which has known issues reported in the lesson (e.g. different delimiters).

[angela-li]

Yes, agreed that Google Sheets may be a better alternative to LibreOffice. For data validation, Google Sheets also has more functionality.

[chris-prener]

I like this suggestion @colinquirk, and thanks for your patience! I agree with @angela-li that it would be perhaps preferred over LibreOffice.

However, I'm not in a place to do the makeover myself. Is this something you would like to contribute to, @colinquirk ?

[colinquirk]

Sorry, I don't think I'll be able to take this on. Maybe a new instructor will be interested in taking a shot.

[chris-prener]

No worries @colinquirk !

[kerchner]

Has anyone yet tried running through the material to at least determine at which points Google Sheets users would have to do something different?

[chris-prener]

not that I am aware of, @kerchner!

[dolsysmith]

Having taught this lesson last week with Excel, I just ran through the demonstrations and exercises using Google Sheets. With one exception -- in the Data Validation lesson -- the functionality in Sheets tracks closely to Excel for this content. See my summary below:

• Lesson 2, Formatting Data Tables in Spreadsheets
  • SAFI_messy.xslx opens in Sheets with all of the formatting from the original, though it may be necessary to perform the additional step of selecting ("Open in Sheets") once the user has uploaded the file to their drive.
  • SAFI_clean.csv also opens in Sheets with no issue (see above for the additional step if uploading to Drive).
• Lesson 3, Formatting Problems (no exercises)
• Lesson 4, Dates as Data
  • After opening dates.xlsx in Sheets, at least in my locale, the MONTH, DAY, and YEAR functions work as expected on the MM_DD_YEAR tab. On the DD_MM_YEAR tab, they produce a VALUE error, much like in Excel. I am not sure how Google Sheets would behave in locales where DD-MM-YEAR is the standard format.
  • The "default year" exercise also behaves the same in Sheets as in Excel.
• Lesson 5, Quality Assurance
  • It is not trivial to limit the user's input in the no_membrs column to whole numbers. This is possible but requires entering a custom formula into the Data Validation menu: =IF(D124=INT(D124),True,False).
  • It is easy to restrict the user's input to a range of numbers, but non-integers are not automatically excluded.
  • Restricting entries to a list of hard-coded values, or to a list of values from another range, works as in Excel.
  • The functionality found in Excel under the Input Message and Error Alert tabs is present in the Sheets Validation popup, but it's more limited. (The user can set custom help text but cannot set a custom error message.)
• Lesson 6, Exporting Data
  • In Sheets, the user should select File--Download--Comma-separate values (as opposed to File--Save in Excel).

In summary, I think Sheets would probably be a viable option for those without access to Excel. (And although this topic doesn't come up in the Lessons, I have found in my own work that Sheets can be easier to work with than Excel when importing CSV data from other sources. Excel, for instance, tends to make assumptions about certain data types -- e.g., dates and long integers -- that can prove problematic if you don't correct for them on input. Sheets's type system seems a bit more flexible in this respect.)

(This comment submitted as part of the Carpentries Instructor Training Checkout process.)

[chris-prener]

thanks so much @dolsysmith - this is wonderful feedback! It looks like moving forward on a Google Sheets version of this is a viable option.

[bkmgit]

Made a pull request for a version using LibreOffice #121 . LibreOffice is also available as a cloud version through Collabora Office. Onlyoffice is also available in the cloud, but the data validation section would need to be changed for this. WPS Office has an online version that at present is free to use and would allow for minimal additions to the validation section.

[lvermeyden]

If help is still needed to update the lesson to work with google sheets I am happy to assist with that.

[ndporter]

Copying feedback received on Slack from @froggleston on data privacy and cloud services here in case people want to work on this issue:

I think this is a good point to raise, as human-related or identifiable data shouldn't be stored or processed in formats processable by cloud providers, e.g. Google Docs are processed for content by Google and indexed to allow the efficient searching the service provides. Users should be made aware that cloud providers are able to and regularly do scan content on their systems for malware protection, viruses etc so I would at least recommend that a note is added to the lessons that human-related data should not be stored or analysed in open cloud formats like google docs or sheets.

Looking at organisations that have very well-documented data policies relating to data services, e.g. Univ Michigan (https://safecomputing.umich.edu/dataguide/), Personally Identifiable Information is permitted within Google suite services (sheets, docs etc), but Protected Health Information and Sensitive Identifiable Human Subject data are not. It might be helpful to suggest that staff and students at their respective institutions would need to make sure they comply with their institutions' data policies.

[kerchner]

Thinking about the learning objectives of this workshop, they transcend any particular spreadsheet program, so if someone later has data that requires a more secure environment, they will easily be able to apply what they learned in this lesson to a different program. Google Sheets, equally accessible to everyone, removes the software setup barrier for Carpentries lesson participants, and could result in all participants being in the exact same program. In my opinion that pedagogical benefit far outweighs any future concerns about working with sensitive data outside of the workshop - where, again, the lessons learned from the workshop could be applied in a different spreadsheet program.