Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Google Sheets fetch content

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:

  1. Create a Google Sheet.
  2. 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.
  3. Share the document so that it’s readable by anyone with the link.
  4. Plug that link (minus the /edit#gid=0 bit) into the shortcode below.
  5. 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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#2 2021-06-15 08:29:01

planeth
Plugin Author
From: Nantes, France
Registered: 2009-03-19
Posts: 215
Website

Re: Google Sheets fetch content

Brilliant !

Offline

#3 2021-06-15 10:42:34

etc
Developer
Registered: 2010-11-11
Posts: 5,028
Website GitHub

Re: Google Sheets fetch content

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.

Offline

#4 2021-06-15 10:48:10

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Google Sheets fetch content

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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#5 2021-06-15 13:46:33

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Google Sheets fetch content

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)


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#6 2021-06-16 08:29:18

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,134
GitHub

Re: Google Sheets fetch content

Bloke wrote #330487:

Just for prosperity,

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

Offline

#7 2021-06-16 09:08:14

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,250
Website GitHub

Re: Google Sheets fetch content

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


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

Board footer

Powered by FluxBB