This is part 2 in our series on spreadsheets and data visualization. In this part we’ll cover the powerful Google Sheets application and why it’s one of the best. Be sure to also check out part 1, part 3, part 4, and part 5 of the series.
In part 1 of our series, we talked about the origin of spreadsheet applications and reviewed a basic primer on visualization techniques for converting data into charts and graphs. For the rest of this series, we’ll be talking about one of our favorite data tools, and certainly our favorite spreadsheet tool: Google Sheets.
In 2006, Google entered the market of productivity applications with the first two entries in what eventually became Google Docs: Writely, an online word processor created by a startup they acquired in 2005, and Google Spreadsheets, a spreadsheet application built on technology called XL2Web that they bought in the same year. At the time there were very few SaaS products for document editing and productivity, and Google built a strong user base over the first several years with their combination of advanced technology and cost-free platform for basic document editing and sharing. Over the last decade of development while competing with Microsoft Office, Google Sheets still remains the premier online spreadsheet service.
In today’s software world, everything’s moving to the cloud. The days of buying software in a box are numbered. As part of Google’s GSuite, Sheets is a service, not just an application installed on your computer. The Google Docs team regularly releases new functionality and improvements for all of their Apps subscribers. Compared to the traditional pricing structure for Excel licenses, Sheets is a steal.
My favorite feature of Google Sheets is how “connected” your documents become, versus standalone files sitting on your desktop. The most visible feature powered by the cloud service nature of Google Sheets is the excellent set of collaboration capabilities available within your documents. You can add collaborators for your document by email address, and grant them access to view, comment, or make edits to your sheet. When working with others on documents, you can actually see in real-time when others are making edits. Sheets shows you where other users are editing in the document. With this sort of real-time collaboration, gathering feedback from your team on a spreadsheet document no longer requires shuffling files around as email attachments — you send out a link to a document, invite everyone with “comment” permission, and your team can add their own comments cell by cell for you to discuss right alongside the data. Once you’ve reviewed, you can resolve comments inline and view a history of changes in the document revision history.
Real-time collaboration on Sheets
If you’re just beginning to explore cloud-based spreadsheets, you’ll naturally have data files to bring along with you to try out with Google Sheets. The good thing is, Sheets makes it easy import existing spreadsheet data. Using the import function, you can load in data from many different sources, including Excel, OpenOffice, CSV, and others. One of the most powerful additions to this, though, is the special IMPORTDATA function that allows you to feed data into a Google Sheet from a web URL. This is one of the fastest ways to get data you’ve collected in Fulcrum into Google Sheets for analysis, sharing, and publishing using Fulcrum’s data shares. Give the function any public URL to a dataset, and it’ll load the data into your sheet automatically:
Check out our previous post with a deeper dive on how to pipe data into Google Sheets from Fulcrum.
Google has built excellent mobile applications for Sheets that work great on iOS or Android devices for reviewing, commenting, and editing documents from your phone or tablet. Opening up a sheet on your iPad gives you almost all of the functionality needed to do editing. Mass edits, chart generation, and certain other tasks are difficult, but if you just need to review some data, make comments or minor edits, the mobile apps are a lifesaver when you can pull it up interactively versus looking at a PDF of a spreadsheet on your phone.
Google Sheets for iPad — super convenient on-the-go
From your tablet or phone you can even see when other users are in your sheet making edits, just like on the web — yet another useful capability made possible by cloud services that you can’t do with an XLS file in your email. The Sheets mobile apps also let you download offline copies for editing if you’re going disconnected.
Sparklines are miniature inline charts that can be populated within individual cells in your data. Google Sheets has a SPARKLINE function built in that makes it easy to insert these neat mini charts in your data files.
Miniature charts with Sparklines
Since your document is in the cloud and has access to the internet, Google provides special functions that can make calls to external web services to automate certain tasks. One great resource (if you have the need) is the GOOGLETRANSLATE function, which can call to Google Translate to translate text inline. The IMAGE function can load an image file from a URL into your sheet. There are quite a few neat functions like this that are unique to Google Sheets.
When competing with a heavyweight, mature application like Excel, there are naturally going to be things newer entrants like Google Sheets can’t do, and there are several. Based on pure customization capability and power user functionality, Excel still wins in raw power. But the number of users that need that power, and more importantly, need it on a regular basis, are few. We love Sheets because it’s a data platform that acknowledges the cloud and is friendly to connect with other web services.
Of course this post only scratches the surface of the cool things you can do with Google Sheets data. In future posts we’ll talk about more of the advanced power tools like Google Apps Script, the sharing and publishing capabilities, the excellent Explorer utility, and how you can integrate Google Sheets to send data to and from your datasets.