Hi,
I would like to display the most views pages of my site, using Google Analytics as data source. I know that GA provides some very powerful api’s, but they’re quite complicated. I wonder if there’s a plugin or a wrapper which is already doing the dirty job.
Thank you
Francesco
Not that I know of, but maybe this plugin can serve as a starting point: https://github.com/gearsdigital/kirby-analytics-dashboard. Guess you might just as well get the most viewed pages instead of visitors from the API.
theres a plugin for google sheets for GA that generates a spread sheet. you could fetch that data as an excel sheet and use @texnixe spreadsheet plugin to display it on your site.
https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on
Nice hint. I’m currently playing with the Google Sheet extension of GA and it works great!
I saw this plugin from @texnixe
but it seems its goal is to display a portion of the sheet inside a page. While this is very interesting, I’d like to parse the sheet and get the cell values.
Well, that one can generate a html table from a spreadsheet.
@texnixe other plugin might be more useful, because it turn spread sheet in to a structure field. From there you can do what ever kirby flavoured stuff u want.
The tag code in the spreadsheet plugin does in fact exactly that: parse data from a spreadsheet. If you install the plugin, you can basically copy and paste the data from the tag and use it in your template (with some modifications, of course).
So you can create a new SpreadsheetReader object like this:
$reader = new SpreadsheetReader('path_to_excel_file');
and take it from there.
That would be an unnecessary detour, because the data could be parsed directly without saving it to Kirby pages first.
Sounds good. But - what if the excel file is actually a Google Sheet file and it’s located somewhere else?
I’m trying to read this file, for instance:
hmm well it used to be that you curl the file down, which could have been tied into a cron job, but seems they turned that option off.
A quick look at the docs hints you can fairly easily use the api to export and download the Excel file. I have never done this, but if its anything like using the calendar API, you will probably need to add a library to your project and set an Oauth token.
You could try googling around, i bet someone out there has written a perl/python/bash script to grab a file from google drive. If the file is publicly accessable, I can’t see why you cant just curl it, but i’ve seen reports of google restricting it.
A cheeky way might be to something similar to the dropbox sync, but rather install the google drive app on your sever if your able to (you would need a VPS for this). You could then symlink the file into your content folder.
Isn’t it possible to access a .csv version of that file?
Well, maybe this is helpful as well: https://www.fillup.io/post/read-and-write-google-sheets-from-php/
I just stumbled on this CLI tool that can save a googlesheet to a JSON file on disk. Might suit your needs.
Alternatively, I just ran a little test. If you make the sheet both public and publish it (File > Publish to the web), you can indeed use curl to grab it. Just use the url google sheets gives you when you publish. Note the query string on the end - if you need CSV or other format, tweak the query string.
curl https://docs.google.com/spreadsheets/d/e/YOURSHEETID/pub?output=xlsx > sheet.xlsx
If you need to keep the the spreadsheet private, I would go with that cli tool i linked yesterday, but that will only give you JSON as a result, or use the docs API to fetch it.
@texnixe and @jimbobrjames: thanks both for your answers. You saved me a lot of time. I ended by downloading the Google Sheets Document as csv and then using some php to convert csv into arrays.