Use the ImportRange() function to share data between Google spreadsheets and to generally make life more satisfying
An important goal in data management is to avoid redundancy. We Prometheans despise redundancy. When we have information about something, we store that information in exactly one place. Why would we want to do that? If we store it twice, there’s a possibility that those two values may become out of sync, of course. It would be a problem, for example, if we thought that Henry’s single favorite food were both sweet potatoes and bacon. Henry only has one favorite food. It’s sweet potatoes. A researcher studying Henry should never have to question that.
Researchers often do encounter this very problem, though. We’ve inherited client databases that included multiple (different) birthdays for the same individual. We’d never allow that to happen in a RexDB® system.
What if you’re working with spreadsheets instead of a database, though? It’s one thing if you have a powerful query language (such as HTSQL) that makes it easy to track down the one place where a value is stored. It’s less straightforward if you have multiple spreadsheets that all depend on the same information.
You can use Google Docs to share data between spreadsheets. It’s exciting.
Let’s begin by taking a look at this spreadsheet: Seinfeld characters and fun facts. It’s a list of ten Seinfeld characters with a fun fact about each.
Now imagine we want to make a second document, one that lists the same Seinfeld characters, each with their occupation. We plan to share this information only with a select group of friends, so it can’t be on the same document as the original information. But we want to start with the same data: a list of characters. And since we’re lazy, we don’t want to re-type the list. What do we do?
We use the wonderful, mysterious ImportRange function. In the first cell of the new document, enter this formula:
Now, the characters listed on the first doc are displayed on the second! (And if I want to add more characters to the first spreadsheet, they’ll show up on the second).
So, how’s that work exactly? The ImportRange function takes two arguments:
Spreadsheet key: The unique key that’s shown in any google doc’s URL:
Range: The cells you’d like to import.
Wasn’t that great? We told you it would be exciting.
Epilogue: Why is the imported range blue?
That’s a convention we follow in order to make it clear that the data is calculated and one shouldn’t try to edit it on the second spreadsheet.