Saturday, January 3, 2015

IMPORTXML for Google Sheets in EVE

Lots of recent posts on EVE Forums and the EVE-Central Google Group about seemingly recent problems with the IMPORTXML function on Google Sheets, specifically with import calls to the EVE-Central market data APIs.  As discussed in this thread, the problem seems to be mostly with the old version of Google Sheets which is gradually being replaced with a newer version of Google Sheets.  Google is supposed to auto-convert all existing sheets to the new version over the next year.  If you're having problems, there's a good chance you're using an old sheet.  There are at least two ways to fix this:

  1. Convert an old sheet to a new sheet (more on that below); or
  2. Don't use IMPORTXML, instead create a Google App Script for your sheet.

Here's how to upgrade from an old sheet to a new sheet:

  1. Figure out if you're using the old or new Google Sheets.  Your using the new Google Sheets if there is a small green checkbox in the lower right corner of your sheet: 
  2. If you're using an old sheet, you can manually convert to a new sheet.  Currently, the only way to do this is to create a new sheet and copy.  This page gives instructions for how to do that.
  3. There are a few feature differences between the old version of sheets and the new which you can read about towards the bottom of this page.  Some things they don't mention explicitly, for example (and this bit me already): named ranges now require the sheet as well as the range name.  You used to be able to define a range like "Foo = sheet1!A1:A20" and refer to that range on any other sheet as just "Foo".  Now you have to refer to it as "sheet1!Foo" which is kind of a pain (or...maybe there's some workaround I haven't found yet).
My own preference is to write Google App Script for these types of things.  Steve Ronuken has a long post here about replacing IMPORTXML with script instead.

No comments:

Post a Comment