In this article, we'll show you how to connect your Matomo data to Geckoboard using Google sheets.
Generate a token_auth in Matomo
You'll need to have generated a token_auth in Matomo. Without it, you'll only use a session token_auth that will only be valid while you are logged in. Follow Matomo's guide to generate your unique token_auth.
Create a script that calls Matomo's API
In the example below, we’re going to export a custom report in Matomo.
In Matomo, click on the Export icon.
Choose CSV under Export Format, choose All under Row Limit, then click Show Export URL.
Insert your unique
token_authin the Export URL.Open a new Google sheet and give it a name.
From the menu, click on Extensions, then Apps Script.
In the editor window, paste the following code.
function myFunction() { var csvUrl = "EXPORT_URL"; var csvContent = UrlFetchApp.fetch(csvUrl).getContentText(); var csvData = Utilities.parseCsv(csvContent); var sheet = SpreadsheetApp.getActiveSheet(); sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData); }Switch back to Matomo, and copy the Export URL.
Return to your Apps Script, and replace the
EXPORT_URLin the code with the copied Export URL.Append
&convertToUnicode=0at the end of the URL. If you'd prefer to report on yesterday's data, append with&date=yesterday, instead.Rename the Project and Save.
Click the Run icon and, when prompted, authorize the script to connect to an external service.
You will now see data imported from Matomo in your Google sheet.
Set up a trigger to automate the data import from Matomo
Without a timed trigger you'd need to manually run the import script each time to see the latest data from Matomo.
In your Apps Script, hover over the Clock icon on the left, then click on Triggers.
Click the + Add Trigger button in the bottom right corner of the page.
Select the Event source as Time-driven, then choose the frequency to run the import.
Click Save.
Connect the Google Sheet to Geckoboard
Switch to Geckoboard to connect the Google sheet containing your imported Matomo data.
Click + Add widget and select either Spreadsheets or Google Sheets from the data sources list.
From the Select a data source panel, hover over Google Drive and click Connect.
In the pop-up window you'll need authorize the connection to Geckoboard.
Select your new spreadsheet from the list.
You can now create spreadsheet widgets powered by your Matomo data import. As an example, this is a leaderboard displaying the total orders.
To learn how to create more spreadsheet widgets powered by Sheets data, select from the following visualization types: