API Google Sheeet

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:

:white_check_mark: 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.

  1. Log in to your Matomo instance.
  2. Navigate to Administration > API.
  3. Copy your Auth Token (you’ll need this for authentication).
  4. 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.

  1. Open your Google Sheet.
  2. Go to Extensions > Apps Script.
  3. 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 domain
  • YOUR_TOKEN with your actual API token

Step 4: Schedule Monthly Automation

  1. In the Script Editor, go to Triggers (:stopwatch: icon on the left).
  2. Click “+ Add Trigger”.
  3. Choose:
  • Function: fetchMatomoData
  • Event source: Time-driven
  • Type of time trigger: Month timer
  • Day/time: As you prefer

:white_check_mark: Done!

Each month, Google Sheets will automatically fetch the

1 Like