API Google Sheeet

General questions
1

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

2

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