GSC Dates Average Charts

GSC Dates Average Charts

// Global spreadsheet object
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
//console.log(spreadsheet);

function main() {
  console.log("main");
  generateLineCharts();
}

// Function to delete multiple sheets
function deleteSheets(sheetNames) {
  console.log("in sheet names");
  console.log(sheetNames);
  sheetNames.forEach(sheetName => {
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (sheet) spreadsheet.deleteSheet(sheet);
  });
}

// Helper function to generate charts
function generateChart(sheet, column1, column2, title, positionRow, positionCol) {
  var dateRange = sheet.getRange(column1);
  var dataRange = sheet.getRange(column2);
  var tempSheet = sheet.getParent().insertSheet();

  // Create a new temporary data range
  var tempRange = tempSheet.getRange(1, 1, dateRange.getNumRows(), 2);

  // getValues() returns a 2D array, even if the range is a single column
  var dateValues = dateRange.getValues();
  var dataValues = dataRange.getValues();

  // Prepare a new 2D array for the temporary range
  var tempValues = dateValues.map(function(row, index) {
    return [row[0], dataValues[index][0]];
  });

  // Set values to the temporary range
  tempRange.setValues(tempValues);

  var chartBuilder = tempSheet.newChart();
  chartBuilder.addRange(tempRange)
              .setChartType(Charts.ChartType.LINE)
              .setOption('title', title)
              .setOption('trendlines', { 0: { 
                    type: 'linear',
                    lineWidth: 1,
                    opacity: 0.5,
                    showR2: true,
                    visibleInLegend: true,
                    color: 'green',
                    labelInLegend: 'Trendline',
              }})
              .setPosition(positionRow, positionCol, 0, 0)
              .asLineChart();
  sheet.insertChart(chartBuilder.build());

  // Delete the temporary sheet
 // sheet.getParent().deleteSheet(tempSheet);
}


// Helper function to handle the CTR value
function parseCtr(ctr) {
  return typeof ctr === "string" ? parseFloat(ctr.replace('%', '')) : ctr;
}

// Helper function to handle the CTR value
function parseNumber(value) {
  return typeof value === "string" && value.includes('%') ? parseFloat(value.replace('%', '')) : parseFloat(value);
}

// Helper function to compute averages
// Helper function to compute averages
function computeAverages(aggregateData) {
  var newData = [["Month-Year", "Average CTR", "Average Position", "Average Clicks", "Average Impressions"]];
  for (var key in aggregateData) {
    var avgCtr = aggregateData[key].ctrSum / aggregateData[key].count;
    var avgPos = aggregateData[key].posSum / aggregateData[key].count * -1; // Multiply by -1 to flip the chart
    var avgClicks = aggregateData[key].clicksSum / aggregateData[key].count;
    var avgImpressions = aggregateData[key].impressionsSum / aggregateData[key].count;
    newData.push([key, avgCtr, avgPos, avgClicks, avgImpressions]);
  }
  return newData;
}


function generateLineCharts() {
  var sheet = spreadsheet.getSheetByName('Dates');
  var data = sheet.getDataRange().getValues();

  // Delete existing "Processed Data" sheet if it exists
  deleteSheets(['Processed Data']);

  // Create a new sheet for processed data
  var newSheet = spreadsheet.insertSheet('Processed Data');

  // Prepare the data for aggregation
  var aggregateData = {}; 
  for (var i = 1; i < data.length; i++) {
    var date = new Date(data[i][0]);
    var year = date.getFullYear();
    var month = date.getMonth() + 1; // getMonth() returns month index starting at 0

    var monthYearKey = year + "-" + (month < 10 ? "0" + month : month);
    var ctr = parseNumber(data[i][3]);
    var pos = data[i][4];
    var clicks = parseNumber(data[i][1]);
    var impressions = parseNumber(data[i][2]);

    if (!aggregateData[monthYearKey]) {
      aggregateData[monthYearKey] = {
        ctrSum: ctr,
        posSum: pos,
        clicksSum: clicks,
        impressionsSum: impressions,
        count: 1
      };
    } else {
      aggregateData[monthYearKey].ctrSum += ctr;
      aggregateData[monthYearKey].posSum += pos;
      aggregateData[monthYearKey].clicksSum += clicks;
      aggregateData[monthYearKey].impressionsSum += impressions;
      aggregateData[monthYearKey].count += 1;
    }
  }

  // Compute averages and prepare data for new sheet
  var newData = computeAverages(aggregateData);

    // Sort newData by date (Month-Year)
  newData.sort(function(a, b) {
    var dateA = new Date(a[0]);
    var dateB = new Date(b[0]);
    return dateA - dateB;
  });

  // Write the new data to the new sheet
  newSheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

// Generate charts
// Generate charts
generateChart(newSheet, 'A2:A' + newData.length, 'B2:B' + newData.length, 'Average CTR per Month', 1, 6);
generateChart(newSheet, 'A2:A' + newData.length, 'C2:C' + newData.length, 'Average Position per Month', 12, 6);
generateChart(newSheet, 'A2:A' + newData.length, 'D2:D' + newData.length, 'Average Clicks per Month', 23, 6);
generateChart(newSheet, 'A2:A' + newData.length, 'E2:E' + newData.length, 'Average Impressions per Month', 34, 6);




}