Hey, welcome to the new site.
Since you’re reading this, it means you’re seeing the new, minimal version of my site hosted on AWS. You’ll also notice that this is no longer Drupal-powered. That’s right, it’s Jekyll-generated HTML baby. Check out those tiny pagesizes. Mmm.
Enjoy.
Here’s a snippet you can add to automatically refresh a cell that performs an IMPORTXML() lookup.
Key components - you need a blank cell somewhere in your Google Sheet that we can use for cache-busting.
For the example, let’s say it’s J24. Make sure J24 isn’t occupied by data. We’re going to write a random number to this field.
Next, create a cell that holds the URL you are fetching.
Important: format it like so: ="http://example.com/xmlfeed#"&J24
Note the trailing hash sign - that is just a throwaway parameter that we’ll use to refresh.
Now add the following in the script editor as a timed trigger.
/**
* From: http://stackoverflow.com/a/33875957/1677912
* Adapted by Davis E. Ford, May 5, 2017
*/
function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.
// At this point, we are holding the lock.
var id = "YOUR_SHEET_ID";
var ss = SpreadsheetApp.openById(id);
var sheets = ss.getSheets();
for (var sheetNum=0; sheetNum<sheets.length; sheetNum++) {
var sheet = sheets[sheetNum];
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var tempFormulas = [];
for (var row=0; row<formulas.length; row++) {
for (col=0; col<formulas[0].length; col++) {
// See https://regex101.com/r/bE7fJ6/2
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
if (formulas[row][col].search(re) !== -1 ) {
// Use a blank cell here, set it to whatever you want
sheet.getRange("J24").setValue(Math.round(Math.random()*100000));
}
}
}
// After a pause, replace the import functions
Utilities.sleep(2000);
for (var i=0; i<tempFormulas.length; i++) {
var cell = tempFormulas[i];
sheet.getRange( cell.row, cell.col ).setFormula(cell.formula)
}
// Done refresh; release the lock.
lock.releaseLock();
}
}Google Spreadsheets will automatically update whenever a cell is updated on the sheet ;) If you set a five minute trigger, that’s the frequency your IMPORTXML() cell will update.
Python’s str.title() and string.capwords(str) methods have some flaws. Namely:
str = "my dog's bone/toy"
assert str.title() == "My Dog'S Bone/Toy"
assert string.capwords(str) == "My Dog's Bone/toy"As you can see, str.title() doesn’t quite format the string “Dog’s” correctly - it instead opts to convert the “‘s” to uppercase. This results in somewhat nonsensical strings.
string.capwords(str) is better about honoring the possessive case, but it has its own flaws. Firstly - it doesn’t recognize words that occur after common punctuation like /, (), -, _, etc. This means that “bone/toy” will only be converted to “Bone/toy”
I’ve written my own answer to this problem, hopefully it helps anyone else who needs to capitalize the first letter of each word after punctuation without using title() or simply relying on capwords().
My solution first takes advantage of title() to solve most of the capitalization. It then uses a Regular Expression to look for an upper-cased letter preceded by a single quote mark that is in turn preceded by a lower-cased letter(this solves the issue of retaining contractions while matching single quotes within strings.)
import re
import string
def lowercase_match_group(matchobj):
return matchobj.group().lower()
# Make titles human friendly
# http://daviseford.com/python-string-to-title-including-punctuation
def title_extended(title):
if title is not None:
# Take advantage of title(), we'll fix the apostrophe issue afterwards
title = title.title()
# Special handling for contractions
poss_regex = r"(?<=[a-z])[\']([A-Z])"
title = re.sub(poss_regex, lowercase_match_group, title)
return title
def title_one_liner(title):
return re.sub(r"(?<=[a-z])[\']([A-Z])", lambda x: x.group().lower(), title.title())
str = "my dog's bone/toy has 'fleas' -yikes!"
assert title_extended(str) == "My Dog's Bone/Toy Has 'Fleas' -Yikes!"
# Note the errors that would occur with native implementations
assert str.title() == "My Dog'S Bone/Toy Has 'Fleas' -Yikes!"
assert string.capwords(str) == "My Dog's Bone/toy Has 'fleas' -yikes!"If an issue is repeatable, it’s fixable.
If you can’t see the symptom, it’s hard to do anything.
Walk away from a problem and do something else. You’ll figure it out then.
The car doesn’t care that it can’t move. Only you do.
Diagnosing a problem correctly is rewarding.
The only thing more rewarding is correctly predicting the solution from a distance.
If there isn’t an aftermarket part available, that part probably isn’t the issue.
The more aftermarket options are available, the less reliable that part is.
If a task is simple, no one makes a Youtube video for it.
If a task is very difficult, no one makes a Youtube video for it.
You don’t pay for a mechanic’s labor. You pay for physical application of his prior experience.
It either works or it doesn’t.