IMPORTXML (Google Sheets) FAQ and Optimization

28 Jun 2017

I got an email from a reader John who had some issues running the IMPORTXML script that I’d posted about in May.

A couple of clarifying pieces of information:

How do I get the Google Sheet ID?
  • The var id = '' requires your Google Sheet ID. The easiest way to get this ID is to look at the the URL of your Google Sheet.
  • Example:
  • The id is the hashed value between d/ and /edit, so in this case, var id = '1234567890abcdefghijklmnop';.
I keep getting the “Service using too much computer time for day” email error. Any way around this? Is it possible to have the script run on only one particular page rather than every page in the sheet? I wonder if that might help. Or another way?

Are you sure you really need to search each sheet, row, and column for the existence of IMPORTXML statements, and then, and only then, update a cell every five minutes?

Would the script run just as well if you just update a cell every five minutes regardless of conditions?

You really just want a script that updates the number after the hash #, e.g. cell E2 in ""&E2.

I recommend that you make one new sheet (make it the last sheet in the whole workbook) that will contain all of your IMPORTXML statements. If you’re doing cryptocurrency stuff like most people using the script, name the sheet Prices, and that’s where all of your other sheets can reference values.

What I do is just have a page where all of the links have their # number pointed at cell E2. Then my script looks like the following. Remember that IMPORTXML runs every time you change the hash number, so all we really need is a script that updates one cell every few minutes. We don’t need to search for IMPORTXML and waste computing time when we know that we definitely want this cell updated!

function RefreshImports() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;   

  var id = "your_google_sheet_id";
  var ss = SpreadsheetApp.openById(id);
  var sheets = ss.getSheets();
  // Get the last sheet
  var sheet = sheets[sheets.length - 1];
  // Use a blank cell here, set it to whatever you want
  sheet.getRange("E2").setValue(Math.round(Math.random() * 100000));

Using a one-page approach will condense your IMPORTXML statements, making them easier to find, and it’ll help avoid the “Service using too much computer time for day” issue, since it’ll require next to zero computation time to run this script.

Seraphon Skink Priest

20 Jun 2017

I’ve finished painting a quick Skink Priest for my Seraphon army.

Nothing too fancy with this guy, just a nice little feather gradient, some gold jewelry, and a custom-ish base. I’ve decided on a palette of emerald/gold/white to signify my heroes. Normal schmucks just get pink shields to tie them together.

Unfortunately, I over-primed this model. If you look closely you can see the bubbling paint on the feathers, and a distinct lack of feather-like texture as well. Definitely over-primed! Oh well, on a small piece like this, you’re not going to notice at a distance.

I’m happy with the general color scheme (picked by my girlfriend Amanda, who is infinitely better with color theory than I am). On my next Skink model, I will be very careful to not over-prime it.

Next up is a unit of Kroxigors, a Bastiladon, and three Terradon Riders. A lot of work!

Seraphon "Getting Started" Box Set - Painted

16 Jun 2017

Here is my battle-ready Seraphon army. These are the models that you get from the Seraphon Getting Started box. One Old-Blood on a Carnosaur, eight Saurus Knights, and twelve Saurus Warrios.

The painting on these is decent quality, but everything could use some work. I haven’t painted a lot of models in a long time, and I found myself using shortcuts to get this done. Regardless, I am quite pleased with the army’s look.

I have a few more models en route. Will post pics eventually.

Python - Sending a Wildcard Query to ElasticSearch using Requests

07 Jun 2017

Here’s an easy wrapper for ElasticSearch using request and json.

import requests
import json

def es_wildcard_query(uri, key, term):
    """Wildcard Elasticsearch Query"""
    query = json.dumps({
        "query": {
            "wildcard": {
                key: '*%s*' % term
    response = requests.get(uri, data=query)
    results = json.loads(response.text)
    return results

# Sample Usage:
# print es_wildcard_query('http://your_es_url/_search?', 'key', 'search_term')