Tuesday, May 7, 2013

Dynamically Import Data From Other Workbooks - Google Spreadsheet

I made a google spreadsheet which pulls data from two other workbooks using "importRange" and QUERY function. You can use either one if you just want to pull a few columns from another spreadsheet. QUERY provides more flexibility by allowing you to use a SQL statement. Here is an example of a query which pulls 2 columns from another workbook and filters out the blank rows in column 41:

=QUERY(importRange("yourSpreadsheetKeyHere", "Donations!A3:BB200000"), "select Col41, Col2 WHERE Col41!='' ", 1)

One of the biggest pains during the entire process was google spreadsheet's vlookup. I learned the vlookup function only searches for a value in the first column in the Array. It's also helpful if you format the results of your vlookup as "Plain Text" under the numbers format drop-down. Of course, something formatted as a number won't match something formatted as plain text.

You could use this technique if you have a list of customer id's with names, and a separate spreadsheet with more customer information. With this, you could combine that information.

No comments:

Post a Comment