Paul's Programming Notes     Archive     Feed     Github

Indexes Help... A Lot

I just brought 4 queries from 15 seconds to less than 1 second by adding indexes to the columns in the ON() portion on the join.

Indexes help... A lot.

New Vocabulary Word

CRUD
-Create
-Read
-Update
-Delete

An example of a CRUD application would be something that tracks orders in a database. All 4 actions are going to be necessary to track those orders. CRUD: The basic database operations.

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.