Hello,
We would like to create an API that automates the transfer of our Matomo data to a Google Sheet each month.
Could you please let me know the steps to follow?
Best regards,
Charlotte
You should ask ChatGPT or similar for somethnig like this. It can guide you step by step for your particular case and write the code you need.
I pasted your post to ChatGPT and it replied this:
Step-by-Step Guide to Automate Matomo to Google Sheets
Step 1: Get Access to Matomo API
Matomo provides a powerful API to extract analytics data.
- Log in to your Matomo instance.
- Navigate to Administration > API.
- Copy your Auth Token (you’ll need this for authentication).
- Take note of your Matomo Site ID and Matomo URL.
Step 2: Define the Data You Need
Decide:
- What report(s) you want (e.g., visits, pageviews, conversions, etc.)
- The frequency (monthly)
- Any segments or filters
Example API call:
https://your-matomo-domain.com/index.php
?module=API
&method=VisitsSummary.get
&idSite=1
&period=month
&date=last1
&format=JSON
&token_auth=YOUR_TOKEN
Step 3: Set Up Google Sheets Script
Use Google Apps Script to fetch data and write it to a Sheet.
- Open your Google Sheet.
- Go to Extensions > Apps Script.
- Paste a script like this:
function fetchMatomoData() {
const url = 'https://your-matomo-domain.com/index.php?module=API&method=VisitsSummary.get&idSite=1&period=month&date=last1&format=JSON&token_auth=YOUR_TOKEN';
const response = UrlFetchApp.fetch(url);
const data = JSON.parse(response.getContentText());
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.clear(); // Optional: clear previous data
sheet.appendRow(['Metric', 'Value']);
for (let key in data) {
sheet.appendRow([key, data[key]]);
}
}
Replace:
your-matomo-domain.com
with your actual Matomo domainYOUR_TOKEN
with your actual API token
Step 4: Schedule Monthly Automation
- In the Script Editor, go to Triggers (
icon on the left).
- Click “+ Add Trigger”.
- Choose:
- Function:
fetchMatomoData
- Event source: Time-driven
- Type of time trigger: Month timer
- Day/time: As you prefer
Done!
Each month, Google Sheets will automatically fetch the
1 Like