Google Sheets fetch content

Bloke · 2021-06-14 22:50:27

Here’s an interesting one. Sometimes you want to display content from an external file in a page somewhere: a list of things for sale in a shop, a catalog, some stats, whatever. That’s easy if the file is a text file: write one item per line. Upload the file to some web-accessible location and you can load it into your shortcode with a line of PHP using file() and then process it.

Then you consider handing it over to the client. That’d be too much hassle. FTP? Text files? You can almost hear them saying, “Can we keep the contents in Google Sheets instead?”

As it turns out, the answer is yes… with some trickery.

Google enforces strict OAuth2.0 for access to GSuite content, which is a real pain. New Google Cloud project, register API keys, embed those in your Txp system somehow, then authorize the ‘app’ to use them first time, then use the 16MB PHP client library code that Google supply to write code to access the files. Yes, that’s more than twice the size of Txp itself!

Forget that. So here’s the trick:

Create a Google Sheet.
Write your content one item per row. You could get more creative here if you want, but for demonstration purposes, let’s stick with that.
Share the document so that it’s readable by anyone with the link.
Plug that link (minus the /edit#gid=0 bit) into the shortcode below.
Profit.

How it works: By manipulating the URL it’s possible to tell GSuite to Export the file as CSV. The shortcode then just uses curl() to fetch that URL, splits it on newline and renders the content item by item.

A lot of the (scary-looking) code here is handling caching so the file isn’t hammered constantly. The default is to only hit the file at most once per hour. Anyway, here’s the shortcode called ‘gsheet’:

<txp:hide>
Usage (replace the long ID with the one from your shared doc):
<txp::gsheet file="https://docs.google.com/spreadsheets/d/1ab26b5efgd829898w09r7-240895923gbac" />

Attributes:
* file:
The file URL to fetch, without the /edit#gid=0 part. If you leave the final trailing slash by mistake after removing the above bits, the shortcode will strip it off.
* wraptag:
Standard HTML tag to wrap the list in. Default: ul.
* break:
Standard HTML tag to wrap each item in. Default: li.
* class:
HTML class to apply to the wraptag. Default: empty.
* cache:
Time (in seconds) to cache the content. Default: 3600 (1 hour).
</txp:hide>

<txp:php filename='<txp:yield name="file" trim="/" />' wraptag='<txp:yield name="wraptag" default="ul" />' break='<txp:yield name="break" default="li" />' class='<txp:yield name="class" />' cache='<txp:yield name="cache" default="3600" />'>
// Hack the sheet to squirt out a CSV file.
$sheet = $filename."/export?format=csv";

// Is it time to refresh the content?
$hash = md5($sheet);
$lastUpdate = get_pref("smd_gsheet_{$hash}_lastmod", 0);
$now = time();

if ($lastUpdate === 0 || $now > $lastUpdate + $cache) {
    // Yep: grab the new content...
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $sheet);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);

    // ... split it by newline (n) to get the rows...
    $items = str_getcsv(curl_exec($ch), n);
    curl_close($ch);

    // ... and cache it.
    set_pref("smd_gsheet_{$hash}_data", json_encode($items), PREF_HIDDEN);
    set_pref("smd_gsheet_{$hash}_lastmod", $now, PREF_HIDDEN);
    $lastUpdate = $now;
} else {
    // Fetch the cached content instead.
    $items = json_decode(get_pref("smd_gsheet_{$hash}_data", array()), true);
}

if ($items) {
    // Split each row. And rejoin it again since we're only using
    // one column in this case!
    foreach ($items as &$row) {
        $row = implode(n, str_getcsv($row, ";"));
    }

    // Preamble. Remove/tweak at will.
    echo '<p>List updated: '.safe_strftime('%Y-%b-%d %H:%M', $lastUpdate).'</p>'.n;

    // Output the wrapped list.
    echo doWrap($items, $wraptag, $break, $class);
} else {
    // Some 'oops' message here.
    echo '<p>List is being prepared. Please check back later.</p>';
}
</txp:php>

The shortcode requires Txp 4.8.5 or higher as it uses the PHP variable import feature. For earlier versions you could replace the <txp:php> variable import with a bunch of outer <txp:variable>s and then use them inside the PHP code block by importing global $variable;.

As I say, nothing to stop you using this for more complex spreadsheet data with multiple columns. You simply have to split each row again at comma to get each cell and then process it accordingly. This is just an ugly hack to demonstrate the concept.

Hope it’s helpful to someone.

EDIT: updated to use str_getcsv().
EDIT2: use safe_strftime() instead of date().

Last edited by Bloke (2021-06-15 13:52:29)

planeth · 2021-06-15 08:29:01

Brilliant !

etc · 2021-06-15 10:42:34

Bloke wrote #330470:

nothing to stop you using this for more complex spreadsheet data with multiple columns. You simply have to split each row again at comma to get each cell and then process it accordingly. This is just an ugly hack to demonstrate the concept.

Possibly a bit more robust approach for parsing csv.

Bloke · 2021-06-15 10:48:10

etc wrote #330483:

Possibly a bit more robust approach for parsing csv.

Good call! I’ll update the shortcode. Thank you.

EDIT: updated.

Last edited by Bloke (2021-06-15 11:01:36)

Bloke · 2021-06-15 13:46:33

Just for prosperity, if anyone wants the pre-4.8.5 version of the shortcode:

<txp:hide>
Usage (replace the long ID with the one from your shared doc):
<txp::gsheet file="https://docs.google.com/spreadsheets/d/1ab26b5efgd829898w09r7-240895923gbac" />

Attributes:
* file:
The file URL to fetch, without the /edit#gid=0 part. If you leave the final trailing slash by mistake after removing the above bits, the shortcode will strip it off.
* wraptag:
Standard HTML tag to wrap the list in. Default: ul.
* break:
Standard HTML tag to wrap each item in. Default: li.
* class:
HTML class to apply to the wraptag. Default: empty.
* cache:
Time (in seconds) to cache the content. Default: 3600 (1 hour).
</txp:hide>

<txp:variable name="filename"><txp:yield name="file" trim="/" /></txp:variable>
<txp:variable name="wraptag"><txp:yield name="wraptag" default="ul" /></txp:variable>
<txp:variable name="break"><txp:yield name="break" default="li" /></txp:variable>
<txp:variable name="class"><txp:yield name="class" /></txp:variable>
<txp:variable name="cache"><txp:yield name="cache" default="3600" /></txp:variable>

<txp:php>
global $variable;

// Hack the sheet to squirt out a CSV file.
$sheet = $variable['filename']."/export?format=csv";

// Is it time to refresh the content?
$hash = md5($sheet);
$lastUpdate = get_pref("smd_gsheet_{$hash}_lastmod", 0);
$now = time();

if ($lastUpdate === 0 || $now > $lastUpdate + $variable['cache']) {
    // Yep: grab the new content...
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $sheet);
    curl_setopt($ch, CURLOPT_HEADER, 0);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);

    // ... split it by newline (n) to get the rows...
    $items = str_getcsv(curl_exec($ch), n);
    curl_close($ch);

    // ... and cache it.
    set_pref("smd_gsheet_{$hash}_data", json_encode($items), PREF_HIDDEN);
    set_pref("smd_gsheet_{$hash}_lastmod", $now, PREF_HIDDEN);
    $lastUpdate = $now;
} else {
    // Fetch the cached content instead.
    $items = json_decode(get_pref("smd_gsheet_{$hash}_data", array()), true);
}

if ($items) {
    // Split each row. And rejoin it again since we're only using
    // one column in this case!
    foreach ($items as &$row) {
        $row = implode(n, str_getcsv($row, ";"));
    }

    // Preamble. Remove/tweak at will.
    echo '<p>List updated: '.safe_strftime('%Y-%b-%d %H:%M', $lastUpdate).'</p>'.n;

    // Output the wrapped list.
    echo doWrap($items, $variable['wraptag'], $variable['break'], $variable['class']);
} else {
    // Some 'oops' message here.
    echo '<p>List is being prepared. Please check back later.</p>';
}
</txp:php>

I’m using this version live on a site now to pull a list of items to a web page.

Last edited by Bloke (2021-06-15 13:52:48)

gaekwad · 2021-06-16 08:29:18

Bloke wrote #330487:

Just for prosperity,

I can’t decide if this should’ve been ‘just for posterity’, but I like it!

Bloke · 2021-06-16 09:08:14

Hehe, I love messing with metaphors and stock phrases to see if anyone notices :)

Textpattern CMS

Textpattern CMS support forum

#1 2021-06-14 22:50:27