Everything You Never Wanted to Know: Google Spreadsheets

Spreadsheets and Spaceships; the classic rebuttal to EVE online's title.  This guide is a pre-post for another upcoming series reviewing existing tools.  This is to pair with the writeup for EVE-Central.  These two articles were written in parallel, so there will be a decent amount of overlap.

Most of the lessons covered in this guide are Google-specific commands.  Some will work in excel, but several functions will be Google-only.  We will once again be skipping the laser-focused specifics and remedial topics, focusing instead on those that may have a spreadsheet (or dozen) and wants to improve their existing tools.  Also, we will cover a few best-practices for design so as not to get bogged down with the slew of new features.  There will be a set of example spreadsheets to illustrate concepts.

Resources are at the end of the article.  After the break to save some screen space.

Introduction

Google Drive provides a suite of easily-shared common office tools.  Documents, presentations, forms, spreadsheets, everything a budding small business would need to thrive, and none of the cost of MS Office.  Also, being cloud-hosted comes with some additional benefits.  The cloud allows for incredibly easy access of live web data, without having to write clunky excel scripts.  Also, being able to share these documents means that you and your corp can control access to tools.  This guide is purely for the Spreadsheets toolset.

Basic Functions 

I write this because there was a suite of basic functions I did not know about when I switched into Google Spreadsheets.  Feel free to skip ahead if these get filed in the "duh" category.

Anchor reference ($)

This delimiter allows you to use the drag-copy function while anchoring specific references so they will not drag.  For instance, building 4 condors.  By making the first reference (mineral*$qty), you can then drag-copy across the other minerals (rows) to complete the reference in 1/8th the time.

Technically, the $ freezes whatever its next to:

• $A1: Will freeze COL to A
• Drag-copy COLs: reference will remain A1
• Drag-copy ROWs: ROW will update, but COL will remain A
• A$1: will freeze ROW to 1
• Drag-copy COLs: COL will update, but ROW will remain 1
• Drag-copy ROWs: reference will remain A1

&, JOIN(), REGEXEXTRACT()

Being code minded, I often want to programatically update string cells. Manually filling every cell with their real names, and then having to do it over again for the T2 variant sucks. Also, different groups might need to be added/stripped in more elaborate methods, hence what these string manipulators are for. Using these tools, you can add a lot of quick functionality for only a small amount of work.

• &: allows you to join strings (and references) together.  Think java string printing "hello "+x+" world"
• Great for joining small things together 
• A1=Target Painter I, A2=A1&"I"-->Target Painter II
• Great for adding cell references into string commands
• "api.eveonline.com/api/CharacterList?vcode="&vcode&"&apikey="&apikey
• Join(): works like most scripting join().  Delimiter, string_array
• Convert to csv string
• Join item id's for importXML()
• Use like an iterated &
• RegExExtract()
• Allows for regular expression parsing
• Regular Expression cheat sheet

Array Functions

• Transpose: swap rows/columns.  
• Example: make a list a header
• Sumproduct(): Multiplies each element by corresponding element then sums the outcome
• Example: cost of materials

ImportXML()

ImportXML is the single most useful function for EVE tools.  ImportXML allows you to automatically load nearly-live EVE market data.  Using the numerical typeID for items you want to track (like minerals, products, ships, etc), you can query the APIs of sites like EVE-central.  Also, you can filter out specific data using an xpath query.

Some resources for the uninitiated:

• Sample Spreadsheet
• typeID lookup
• EVE-Central API resource

Some notes to keep in mind:

• Tends to update on-the-hour
• Can force update by changing URL (add/remove "http://" from address)
• DB is most likely to fail request on-the-hour due to cron traffic
• Cannot script cron (as far as I know)
• Join() is your friend
• Join maxes out at 100 entries
• Google's complexity limit is 50 importXML calls.  For very-large sheets, be smart about queries
• Use join (or combine join's)
• Query more data per request:
• Ill-advised xpath: "//sell/min"
• Better xpath: "//sell" or no xpath
• More data per call means less calls for the same data
• xpath "|" does not work like you think it does.  Will concatenate on the END of the first query, not beside the first query #WorkingAsIntended
• Each importXML call will make the spreadsheet slower
• See best-practices notes for how to cope with these limitations

WARNING: Do not try to queryXML() EVE API data.  Whoever set up those XML's was a turd and did not make them xpath compatible.  It can be done, but you need to write an app-script to parse the data, existing functions will not do it.

QUERY()

Query is a tougher tool to use, but incredibly powerful for simplifying a lot of the manual work of updating spreadsheets.  Query allows a nearly-complete SQL-like interface for parsing data.  This becomes EXTREMELY useful for some more eloquent features or further staving off the need for custom code.  I am by no means a SQL pro, but a few simple cases can add a lot of power to your spreadsheet.

Uses:

• Quickly reference price data with a simple command
• Query('Price data'!A:X, "SELECT B where A='"&cell reference&"'",False)
• Group useful data:
• Automatically grab highest and lowest data without needed complex IF()'s
• List collections of data on a sortable variable
• Sort all the frigates out of a list of ships
• Group suitable products between a certain cost

Notes:

• When referencing strings (item names), make sure to include single quotes around value
• ='string' and close SQL query with a double quote
• Not fully formed, only need SELECT and sort criteria, no FROM
• Finicky just like SQL, some frustration will ensue
• Be careful about overrunning boundaries   CONTINUE() will stomp over cells and give precedence to whatever it is continuing

Best Practices

-Or- How I learned to stop worrying about complexity and trust importRange()

Import functionality takes a lot of processing power and will slow down your sheet significantly.  Pile on Query() and a lot of sumproduct() calls, and your sheet will not process.  Instead, make your more complicated tools modular and use importRange() to recycle common functionality.  

Personally, I use importRange() on my personal priceDB.  Also, importRange() is great for bringing in any other data you might care about.  Also, it allows you to protect the sensitive sheets (APIs, work you don't want to share) and import the useful data.  Protection isn't foolproof, it's a security-through-obscurity solution.  Technically, only authors with access to the source material can import into their controlled sheets... but this doesn't give feedback to the source owner as to who is remotely accessing the data.

The biggest lesson I can share is modularity.  Have one sheet for prices, one sheet for t1 building, and so on. Hosting ALL THE THINGS on one sheet will only make a crawling behemoth.  Also, have some patience with importRange().  If the range is dependent on something slower, it may take a minute or two to get the right values and propagate them through the sheet.

Being smart about sharing

Google gives 3 levels of access control, additionally with read/write access for each.  Though the obvious level is to switch the entire sheet on or off, there are some middle ranges that might be useful.

• Hidden sheets
• As long as sheets are protected, they cannot be viewed when hidden
• Collaborators still have access to hidden sheets
• Make a second public sheet
• Hide 100% of the secret sauce in another sheet
• Make a public shell that only shows final data

Personally, I wish "public" could force a sign on anyway, but only invited collaborators will show as non-anonymous.  

A note about Drive-Mobile

Because I am a non-stop nerd, I just want to share that Drive-Mobile is a great place to VIEW these complex sheets... but a terrible place to EDIT them.  Edit with EXTREME caution.  Functions cannot be written in mobile, and some dependent references may not populate as intended.

Resources

• Google Doc function help
• SQL for dummies
• XML for dummies
• Example Spreadsheet