Fetches a table from Google Sheet

Procedure: create a Google Sheet with three columns (here: city, country, event), go to menu File > Publish to Web > csv. Copy the url and put it in the Frontmatter

Data coming from: https://docs.google.com/spreadsheets/d/e/2PACX-1vSSxdNS80jca7uKpvDQtUq0KsDfSPxgk6LB3vMGZcwgHU2rdkRLn3vtTsWCtEVwvWUOfPT6MD8PwuKb/pub


  • Paris, France, JoomlaDay FR
  • Köningstein, Germany, JoomlaDay DE
  • Rome, Italy, Joomla World Conference
  • New Jersey, USA, Joomla User Group New Jersey

See another example: /gsheet2


Notice the 'identity_key' null in the frontmatter.
Pages assumes a identity_key == id (ie a first column called id in the Google Sheet), but that doesn't exist in the present Sheet.
This will be fixed in the future to make pages smarter to handle this use-case, for now setting it to null works fine. There is another weird problem here though which i think is a bug with google. If i set the http protocol to 1.1 when making this request google is returning the csv without the headers, if i change it to 1.0 it does return th csv header. Without the header pages cannot create the collection, it needs to know city, country and event headers of course. Very weird issues, smells like a Google bug

    ---
title: Google Sheet
@collection:
    model: webservice
    config:
        url: https://docs.google.com/spreadsheets/d/e/2PACX-1vSSxdNS80jca7uKpvDQtUq0KsDfSPxgk6LB3vMGZcwgHU2rdkRLn3vtTsWCtEVwvWUOfPT6MD8PwuKb/pub?output=csv
        identity_key: null
    state:
        limit: 20
@process:
    filters: highlight
---
    <h2>Fetches a table from Google Sheet</h2>
<p>Procedure: create a Google Sheet with three columns (here: city, country, event), go to menu File > Publish to Web > csv. Copy the url and put it in the Frontmatter</p>
<p>Data coming from: <a target="_blank" href="https://docs.google.com/spreadsheets/d/e/2PACX-1vSSxdNS80jca7uKpvDQtUq0KsDfSPxgk6LB3vMGZcwgHU2rdkRLn3vtTsWCtEVwvWUOfPT6MD8PwuKb/pub">https://docs.google.com/spreadsheets/d/e/2PACX-1vSSxdNS80jca7uKpvDQtUq0KsDfSPxgk6LB3vMGZcwgHU2rdkRLn3vtTsWCtEVwvWUOfPT6MD8PwuKb/pub</a></p>

<hr>

<div class="well">
<ul>
<? foreach(collection() as $row) : ?>
	<li>
		<?= $row->city ?>, <?= $row->country ?>, <?= $row->event ?>
	</li>
<? endforeach; ?>
</ul>
<?= helper('paginator.pagination') ?>
</div>

<hr>

<p>See another example: <a href="/gsheet2">/gsheet2</a></p>

<hr>

<p>Notice the 'identity_key' null in the frontmatter.<br />Pages assumes a identity_key == id (ie a first column called id in the Google Sheet), but that doesn't exist in the present Sheet.<br /><small>This will be fixed in the future to make pages smarter to handle this use-case, for now setting it to null works fine. There is another weird problem here though which i think is a bug with google. If i set the http protocol to 1.1 when making this request google is returning the csv without the headers, if i change it to 1.0 it does return th csv header. Without the header pages cannot create the collection, it needs to know city, country and event headers of course. Very weird issues, smells like a Google bug</small></p>

<?= source('/gsheet') ?>

<h3>See hereafter the var_dump(collection())</h3>
<pre><code><?= var_dump(collection()); ?></code></pre>

See hereafter the var_dump(collection())

object(ComPagesModelEntityItems)#1712 (4) {
  ["000000000b697441000000005f64ca0b"]=>
  array(4) {
    ["city"]=>
    string(5) "Paris"
    ["country"]=>
    string(6) "France"
    ["event"]=>
    string(12) "JoomlaDay FR"
    ["ordering"]=>
    int(1)
  }
  ["000000000b697414000000005f64ca0b"]=>
  array(4) {
    ["city"]=>
    string(12) "Köningstein"
    ["country"]=>
    string(7) "Germany"
    ["event"]=>
    string(12) "JoomlaDay DE"
    ["ordering"]=>
    int(2)
  }
  ["000000000b697415000000005f64ca0b"]=>
  array(4) {
    ["city"]=>
    string(4) "Rome"
    ["country"]=>
    string(5) "Italy"
    ["event"]=>
    string(23) "Joomla World Conference"
    ["ordering"]=>
    int(3)
  }
  ["000000000b697416000000005f64ca0b"]=>
  array(4) {
    ["city"]=>
    string(10) "New Jersey"
    ["country"]=>
    string(3) "USA"
    ["event"]=>
    string(28) "Joomla User Group New Jersey"
    ["ordering"]=>
    int(4)
  }
}