Quantified Self – Google And Android – Aggregation

In this post I’m going to show you how to consolidate and aggregate your data easily between Google SpreadSheets.

To implement this I’ll be using Google SpreadSheets and SQL.

I’m making the assumption that you have your data in Google Spreadsheets already, if not see this post – Quantified Self – Google And Android – Capture.

If you  simply want to move data from one location to another you can simply enter a formula “=B2”. This would copy the contents of the cell B2 into the current cell. If you change the contents of B2 then the change is also reflected in this cell too.

If you want to reference data from a different sheet on the same spreadsheet then you simply prefix the cell reference with the sheet name. A formula “=Sheet2!B2” would copy the contents of the cell B2 from the sheet Sheet2 into the current cell. If you change the contents of B2 in Sheet2 then the change is also reflected in this cell too.

If you want to reference a range of cells you can simply put these references into adjacent cells:
“=A1”, “=B1”, “=C1”
“=A2”, “=B2”, “=C2”
“=A3”, “=B3”, “=C3”
This would result in a 3×3 grid of cells who’s contents referenced other cells.

What if – you need to reference cells in another workbook? The same principle applies but it’s a function that is used to implement this “=ImportRange(spreadsheetkey, sheet!range)” where the spreadsheetkey is the big long key (mixture of characters and numbers) in the address bar between “key=” and the next (if any) “&” character. For example “=ImportRange(abcd1234MakeBelieveKeyForDemoPurposes4321dcba, Sheet1!A1:C3)”

In this example, Google will go the the worksheet indicated by your key, Sheet1 and get the contents of the range A1 to C3 (a 3×3 grid) and bring the contents into your current worksheet cell. Now obviously we can’t put the contents of a 3×3 grid into a single cell. The cell with the ImportRange function is the top left of our target range and will contain the contents of A1 in the example above. Google will automatically create a formula into the cells to the right and below your ImportRange that say”=Continue(…..)”. You don’t need to worry about these, if you edit the range in your ImportRange then the appropriate number of Continue statements will be added.

Cell/range from another worksheet

Aggregation from same sheet

Aggregation from another worksheet

Differences in SQL, columns vrs references.

 

Leave a Reply