count clicks by pagetype GSC

count clicks by pagetype GSC

function analyzeURLs() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  deleteSheets(['Page Type Analysis', 'Language Namespace Analysis', 'Parameters Analysis', 'Copied & Analyzed']);

  // Create a copy of the original sheet
  const newSheet = sheet.copyTo(SpreadsheetApp.getActiveSpreadsheet());
  newSheet.setName('Copied & Analyzed');

  const pageTypeClicks = calculateClicks(data, 'pageType');
  const langClicks = calculateClicks(data, 'langNamespace');
  const paramClicks = calculateClicks(data, 'paramPresence');

  // Add the analyzed data to the new sheet
  newSheet.getRange(1, 3).setValue('Page Type');
  newSheet.getRange(1, 4).setValue('Language Namespace');
  newSheet.getRange(1, 5).setValue('Parameters');

  data.slice(1).forEach((row, index) => {
    const url = row[0];
    const pathParts = getPathname(url).split('/');
    let pageType;
    if (['de', 'nl'].includes(pathParts[1])) {
      pageType = pathParts.length > 3 ? pathParts[2] : "no page type";
    } else {
      pageType = pathParts.length > 2 ? pathParts[1] : "no page type";
    }
    const langNamespace = ['de', 'nl'].includes(pathParts[1]) ? pathParts[1] : "no namespace";
    const hasParam = getQuery(url) ? 'withParam' : 'withoutParam';

    newSheet.getRange(index + 2, 3).setValue(pageType);
    newSheet.getRange(index + 2, 4).setValue(langNamespace);
    newSheet.getRange(index + 2, 5).setValue(hasParam);
  });

 appendDataAndChart('Page Type Analysis', pageTypeClicks);
appendDataAndChart('Language Namespace Analysis', langClicks);
appendDataAndChart('Parameters Analysis', paramClicks);
}

function deleteSheets(sheetNames) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(sheetName => {
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (sheet) spreadsheet.deleteSheet(sheet);
  });
}

function appendDataAndChart(sheetName, data, total) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
  appendData(sheet, data, total);
  createChart(sheet, sheetName, 2, Object.keys(data).length + 1);
}

function calculateClicks(data, type) {
  return data.slice(1).reduce((clicks, row) => {
    const url = row[0];
    const pathParts = getPathname(url).split('/');
    let element;
    if (type === 'pageType') {
      if (['de', 'nl'].includes(pathParts[1])) {
        element = pathParts.length > 3 ? pathParts[2] : "no page type";
      } else {
        element = pathParts.length > 2 ? pathParts[1] : "no page type";
      }
    } else if (type === 'langNamespace') {
      element = ['de', 'nl'].includes(pathParts[1]) ? pathParts[1] : "no namespace";
    } else if (type === 'paramPresence') {
            element = getQuery(url) ? 'withParam' : 'withoutParam';
    }
    clicks[element] = (clicks[element] || 0) + (parseInt(row[1]) || 0);  // Assume clicks are in the second column and are integers
    return clicks;
  }, {});
}

function appendData(sheet, data) {
  let totalClicks = 0;
  for (const value of Object.values(data)) {
    totalClicks += value;
  }

  sheet.appendRow(['Type', 'Clicks', 'Percentage']);
  for (const [key, value] of Object.entries(data)) {
    const percentage = (value / totalClicks) * 100;
    sheet.appendRow([key, value, percentage.toFixed(2) + '%']);
  }
}

function createChart(sheet, title, startRow, endRow) {
  const chartBuilder = sheet.newChart();
  chartBuilder
    .setChartType(Charts.ChartType.PIE)
    .addRange(sheet.getRange(startRow, 1, endRow-startRow+1, 3)) // include all three columns in the chart's data
    .setPosition(2, 5, 0, 0)
    .setOption('title', title)
    .setOption('pieSliceText', 'percentage') // Show percentage in the pie slices
    .setOption('tooltip.text', 'both') // Show both type and clicks in the tooltip
    .setOption('sliceVisibilityThreshold', 0); // Include all slices, even if their value is 0
  const chart = chartBuilder.build();
  sheet.insertChart(chart);
}







// Custom function to extract the pathname from a URL
function getPathname(url) {
  var link = url.split('?')[0];
  var pathname = link.replace(/^.*\/\/[^\/]+/, '');
  return pathname;
}

// Custom function to extract the query parameters from a URL
function getQuery(url) {
  var query = url.split('?')[1];
  return query;
}