Wednesday, October 17, 2012

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()

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:
  • 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


4 comments:

  1. Useful post, subscribed. I'm very interested in further development on this. I've tried alternatives to Excel myself. Got very disappointed with OpenOffice/LibreOffice (clunky, slow, complicated to do anything in its macro language), Google Docs (I was not aware of all the extra features you describe, so wrote it off as "feature-lacking", as well as very slow due to network latency), ended up buying Office 2007 (cheap sell-out of old copies, I don't need the new shebang) which I know and use at work (and don't mind writing VBA code either) and for me "simply works".

    ReplyDelete
    Replies
    1. Nothing beats the cloud for sharing. Unfortunately, trying to use excel IRL for engineering work finally burned me out on the total lack of "hard core" functionality. The fact excel won't just get out of the way and let me do what I want. Between swearing off excel, and needing to share eve-docs among friends... Drive wins

      Delete
  2. Your file shares on Twitter got me hooked on Drive, and I've been tinkering with calculating manufacturing and profit margins.

    Have you found a decent solution yet for pulling wallet data out of the API? I've played with MySQL and PHP, and maybe I'll get there eventually, but it's a lot of heavy lifting given limited play time...

    ReplyDelete
    Replies
    1. Due to the style of the EVE API, you need to write a google app-script to parse the data. It's pretty basic java-like scripting and there are some great tutorials and google API guides attached to the whole Google Developer suite.

      My example of an asset parser can be found attached to this: https://docs.google.com/spreadsheet/ccc?key=0Atv4WV8DEJUPdDZVT3ZEM21obXg2UTRjV19iSFQ5SUE#gid=0

      warning: I have not touched it since CCP upgraded to HTTPS protocol. So there may be some authentication bugs. More info here: https://www.aideronrobotics.com/discussion/1450/ssl-certificate-problems

      Delete