This Google Apps Script is designed to analyze and visualize web traffic data from Google Sheets. It generates a line chart that displays the total and median clicks for each month, discarding any incomplete months. The chart also includes a trendline to help identify patterns or trends over time. This script is especially useful for SEO analyst…

Google-Apps-Script-Monthly-Traffic-Analysis

function createChart() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the last row with content
  var lastRow = sheet.getLastRow();
  
  // Get data range
  var dataRange = sheet.getRange('A2:B' + lastRow);
  var data = dataRange.getValues();

  // Create an object to hold monthly data
  var monthlyData = {};
  var monthDays = {};

  // Iterate over data and sum clicks for each month
  data.forEach(function(row) {
    var date = new Date(row[0]);
    var month = date.getMonth() + 1;
    var year = date.getFullYear();
    var monthKey = year + '-' + month;

    // Aggregate clicks
    if(monthlyData[monthKey]) {
      monthlyData[monthKey] += row[1];
    } else {
      monthlyData[monthKey] = row[1];
    }

    // Track unique days in the month
    if(monthDays[monthKey]) {
      monthDays[monthKey].add(date.getDate());
    } else {
      monthDays[monthKey] = new Set([date.getDate()]);
    }
  });

  // Convert monthlyData object to an array for chart input, excluding incomplete months
  var chartData = Object.keys(monthlyData).filter(function(key) {
    var month = parseInt(key.split('-')[1]);
    var daysInMonth = new Date(parseInt(key.split('-')[0]), month, 0).getDate();
    return monthDays[key].size === daysInMonth;
  }).map(function(key) {
    // Use toLocaleDateString to format the date as "Month Year"
    var date = new Date(key.split('-')[0], key.split('-')[1] - 1, 1);
    var formattedDate = date.toLocaleDateString('default', { year: 'numeric', month: 'long' });
    return [formattedDate, monthlyData[key]];
  });

  // Sort chart data by date (earliest date first)
  chartData.sort(function(a, b) {
    return new Date(a[0]) - new Date(b[0]);
  });

  // Add data to new sheet
  var chartSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('ChartData');
  chartSheet.getRange(1, 1, chartData.length, 2).setValues(chartData);

  // Create a new chart
  var chartBuilder = chartSheet.newChart()
      .addRange(chartSheet.getRange(1, 1, chartData.length, 2))
      .setChartType(Charts.ChartType.LINE)
      .setOption('title', 'Monthly Clicks Traffic')
      .setOption('vAxis.title', 'Clicks')
      .setOption('hAxis.title', 'Month')
      .setOption('trendlines', { 0: { 
        type: 'linear',
        lineWidth: 1,
        opacity: 0.5,
        showR2: true,
        visibleInLegend: true,
        color: 'green',
        labelInLegend: 'Trendline',
      }})
      .setPosition(5, 5, 0, 0);
  
  chartSheet.insertChart(chartBuilder.build());
}