How to graduate from spreadsheets

[Mari Vihuri]

So, I thought I'd start a discussion on something that's been running through my mind. I've heard a lot of dismay from the more technically-oriented folks about the prevalence of spreadsheet use in libraryland. I'm aware of some of the reasons why, and also have a basic sense of some of the alternatives. I also know the solution to this is going to vary hugely based on what you're actually trying to do with the data, but I'm going to pose the question anyway: 1) What are some of the downsides to relying on spreadsheets? 2) What are some other ways library folks might be able to do the work they do with spreadsheets?

[Sam Popowich]

I've raised this in a few venues, so this is a topic dear to my heart. Thanks for raising it.

For me it isn't so much *relying* on spreadsheets - that is, I don't think there's a downside to using them for whatever you like. The problem comes when you reach the limit of what your spreadsheet software can do (or what you know how to do with your software) and then you either start doing the task by hand (tedious, time-consuming) or have to get someone else to do it for you (can also be time-consuming). A good example is being able to parse a spreadsheet. Say I have a spreadsheet of journal titles and platforms, in a many-to-many relationship. I might have this in a spreadsheet because that's how my platform exports its report. So we have repeated titles and repeated platforms, and I want to know how many journal titles are only available on a single-platform. AFAIK, this is the kind of thing that's not easily doable in a spreadsheet program, so people faced with this problem will start to process things manually. A little bit of programming would allow someone to solve this problem quickly and move on. 

So yeah, 1) for me there isn't a downside to relying on spreadsheets until you reach the limit of what the software can do and 2) a little bit of programming lets you overcome those barriers and work with the spreadsheet data more effectively.

On Tue, Jun 10, 2014 at 2:24 PM, Mari Vihuri <mvi...@gmail.com> wrote:

So, I thought I'd start a discussion on something that's been running through my mind. I've heard a lot of dismay from the more technically-oriented folks about the prevalence of spreadsheet use in libraryland. I'm aware of some of the reasons why, and also have a basic sense of some of the alternatives. I also know the solution to this is going to vary hugely based on what you're actually trying to do with the data, but I'm going to pose the question anyway: 1) What are some of the downsides to relying on spreadsheets? 2) What are some other ways library folks might be able to do the work they do with spreadsheets?

--
You received this message because you are subscribed to the Google Groups "libcode" group.
To unsubscribe from this group and stop receiving emails from it, send an email to libraries-learnin...@googlegroups.com.
Visit this group at http://groups.google.com/group/libraries-learning-code.
For more options, visit https://groups.google.com/d/optout.

--

Sam Popowich
Discovery Systems Librarian
University of Alberta Library
Edmonton, Alberta
Sam.Po...@ualberta.ca
780-492-8215

[Mari Vihuri]

 I definitely agree Sam, and that makes a lot of sense. I think one of the main barriers I see to this though is that spreadsheets are relatively accessible to most people working in libraryland. A programmer might export data as a CSV file or Excel sheet because they know the librarian who needs the data will know what to do with it (or, that's what the platform offers), and they are probably tied up in their own work. Then the librarian may eventually pass on the spreadsheet to a student worker, who then may or may not a) know enough about formulas or maybe macros or b) know enough programming to make use of that data. But then what if the librarian needs to continue working with this data too, or the numbers need to be crunched periodically, etc. etc.

I guess I'm kind of wondering: are there tools we can use in the gap between relying on spreadsheets for everything and needing programming skills to get useful results? I feel like we need to develop some baby steps between one and the other so we can slowly build up the kinds of tech fluencies we need across libraryland. Are there some "smarter" (technically-speaking) but still broadly accessible ways of dealing with data? Sometimes people ask me how I've accomplished something using Excel, but I'm always a bit hesitant to pass on the knowledge because it feels very hacked together and not a good practice to continue, it's just the best solution I've got at the time.

For example: I've currently got a makeshift setup involving Asana (our project management tool), Zapier (similar to If This Then That, which might be more familiar to some folks), and Google Forms and Scripts. This setup automatically updates a Google Spreadsheet and generates dynamic stats. It's far from perfect, and far from sustainable, so I'd eventually like to figure out the skills I need to make use of APIs and write scripts to make this happen without Zapier. But even then I'm still relying on a spreadsheet because that's familiar to the other folks that are working on this project, now or in the future. And even then some of the tools that I'm using would be unfamiliar to librarians and student workers. So, I fall asleep worrying about the best way to ensure we've got a working system and people that will know how to use it in the future if I get hit by a bus, etc.

Apologies for that digression--I'm not looking for solutions to my specific situation (though if you have some, get in touch!), but I'm hoping if I describe some of what I'm grappling with we can collectively start to parse out the issues at hand and figure out some good options.

So, everyone: what are some of the issues you have with spreadsheet culture (to coin a phrase)? What are some solutions you would propose?

[Mita Williams]

I would say that spreadsheets are a gateway drug to learning to code.  I would, except that there aren't that many folks in my immediate circles who have made that particular jump. But I was one. So this is thread speaks to me.

You mentioned that you are working with scripts... but are you working with Google App Scripts? If not, it might fit your needs (I wrote an intro about it some months ago here: http://www.accessola2.com/olita/insideolita/wordpress/?author=25)

Hope this helps!

Mita

 

--

[Mari Vihuri]

Ooh! That's awesome Mita, thank you for the link. Yep, I've got a bit of it working with Google Apps Scripts, but it's very... uh MacGyvered together, and limited by how much time I (as a student employee) can realistically spend on developing it in the context of other priorities. But I'm going to take a look at what you've written because it looks like it could be very useful.

I'm also with you in that spreadsheets-are-a-gateway-drug minority, then! (Well, that and some of the web development skills I've picked up over the years). I know I've had success explaining how certain formulas work when I'm passing on knowledge to other students. I think it helps that formulas are basically small, discrete bits of "code" which let you instruct your spreadsheet software to manipulate data for you. So they get you thinking programmatically, and serve as small but important building blocks on the way to understanding programming. It also helps illustrate how much I rely on community and documentation (i.e. googling things until I figure them out), all of which are important concepts for learning how to code. 

... I don't think I was going anywhere in particular with this, but just wanted to chime in with agreement!

[ni...@satifice.com]

I've also definitely thought that learning how to deal with data outside of spreadsheets (in my personal case CSV files or other plain text formats), would be a great way to introducing librarians to a 'programmers' sensibility to their workflow, since there are fairly easy bash commands for doing some tasks with CSV files that can be super tedious if doing them by hand.