Suppose you have data in Matomo that you want to easily present on a Google Sheet. The script below will help you extract the data from Matomo using the API and parse it into the JSON. From there, you can go one step further and publish the data directly to a Google Sheet.

Step 1 - Get Matomo API Key

Visit Your website and get an API Key.

https://mywebsite.matomo.cloud/index.php?module=API&action=listAllAPI&date=today&period=day

Step 2

Visit Google Apps Scripts and create a new script. Paste this code below

const API_KEY = 'XXXXXXXXXXXXXXXXXXXX';

function myFunction(start,end) {        
  visitsOverTime();
}

/* ** ** ** ** ** ** ** ** ** ** ** ** 
Capture Daily Basic User Visits Data and Publish to Sheets
* ** ** ** ** ** ** ** ** ** ** ** **/
function visitsOverTime(start,end){
  const property = {
    chrisjmendez: {
      website: 9
    }
  }
  const time = {
    period: {
      day: 'day',
      week: 'week',
      month: 'month'
    },
    day_start: 'today',
    day_end: 'yesterday',
    mo_start: '2022-04-01',
    mo_end: '2023-02-28',
    last_mo: 'lastMonth'
  }
  var url = `https://mywebsite.matomo.cloud/index.php?` + 
  `module=API&format=JSON&idSite=${product.chrisjmendez.website}&period=${time.period.month}` + 
  `&date=${time.last_mo}&method=API.get&filter_limit=-1` + 
  `&format_metrics=1&expanded=1&translateColumnNames=1` +
  `&language=en&token_auth=${API_KEY}&convertToUnicode=0`;

  var data = UrlFetchApp.fetch(url).getContentText();
      data = JSON.parse(data);

  var kpis = {
    'Property': 'Name of My Property',
    'Unique Page Views': data.nb_uniq_pageviews,
    'Total Page Views': data.nb_pageviews,
    'Outbound Links': data.nb_outlinks,
    'Bounce Rate': data.bounce_rate,
    'Avg Time on Site': data.avg_time_on_site,
    'Number of Actions / Visit': data.nb_actions_per_visit_new,
    'Growth from Search Engines': {
      'Absolute': data.Referrers_visitorsFromSearchEngines,
      'Percentage': data.Referrers_visitorsFromSearchEngines_percent
    },
    'Growth from Socials': {
      'Absolute': data.Referrers_visitorsFromSocialNetworks,
      'Percentage': data.Referrers_visitorsFromSocialNetworks_percent
    },
    'Growth from Active Paid Campaigns': {
      'Absolute': data.Referrers_visitorsFromCampaigns,
      'Percentage': data.Referrers_visitorsFromCampaigns_percent
    },
    'Growth from Direct Visits': { // This could include internal staff
      'Absolute': data.Referrers_visitorsFromDirectEntry,
      'Percentage': data.Referrers_visitorsFromDirectEntry_percent
    }, 
    'Growth from Partner Websites': {
      'Absolute': data.Referrers_visitorsFromWebsites,
      'Percentage': data.Referrers_visitorsFromWebsites_percent
    },

  }

  console.log(kpis);

  //TODO: Automagically Plug this into the SpreadSheet itself.
  //var sheet = SpreadsheetApp.getActiveSheet();
  //sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

}