This is part 2 in our series on spreadsheets and data visualization. In this part we’ll cover using Google Sheets with Fulcrum. 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.
What does Sheets do that other tools don’t?
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 Google Sheets feature is the connectivity of your documents, unlike standalone desktop files. Google Sheets’ cloud service powers excellent collaboration capabilities within your documents. You can add collaborators via email address, allowing them view, comment, or edit access. While working with others, you can see edits happening in real-time. Sheets indicates where others are editing. This real-time collaboration means you don’t need to shuffle files via email. Instead, share a document link, invite others with “comment” permission, and they can add comments cell by cell. After reviewing, you can resolve comments and check the document’s revision history for changes.
Real-time collaboration on Sheets
Getting Your Data to Google 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:
=IMPORTDATA(‘https://web.fulcrumapp.com/shares/e4a6d5fb95a9cc6e.csv’)
Check out our previous post with a deeper dive on how to pipe data into Google Sheets from Fulcrum.
Working with Data on Mobile
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.
Inline Charts with Sparklines
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.