Plausible Stats

plausible.gs

const TOKEN = "";
const PLAUSIBLE_URL = "https://plausible.acme.com";
const SITES = [
  {
    col: "C",
    siteId: "acme.com"
  },
  {
    col: "D",
    siteId: "softwarebyacme.com"
  },
  {
    col: "E",
    siteId: "example.com"
  },
  {
    col: "F",
    siteId: "corporateacme.com"
  },
  {
    col: "G",
    siteId: "jobsatacme.com"
  },
  {
    col: "H",
    siteId: "acmeblog.com"
  }
];

const TEMPLATE_SHEET = "_template_";
const SHEET_NAME = ({ year, month }) => `${month}/${year}`;
const START_AT_ROW = 7;
const FIRST_COL_LETTER = "A";
const SUM_COL_LETTER = "B";
const FIRST_DATA_COL_LETTER = "C";
const LAST_DATA_COL_LETTER = "H";

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const templateSheet = spreadsheet.getSheetByName(TEMPLATE_SHEET);
const ui = SpreadsheetApp.getUi();

function colNum(columnLetter) {
  let columnNumber = 0;
  for (let i = 0; i < columnLetter.length; i++) {
    columnNumber = columnNumber * 26 + (columnLetter.charCodeAt(i) - 65 + 1);
  }
  return columnNumber;
}

function getDaysInMonth(year, month) {
  const lastDayOfMonth = new Date(year, month, 0);
  return lastDayOfMonth.getDate();
}

function transformDataFromPlausible(data) {
  data = typeof data === "object" ? data : JSON.parse(data);
  return data.results.reduce((obj, { date, pageviews }) => {
    const day = new Date(date).getDate();

    obj[day] = pageviews;
    return obj;
  }, {});
}

function fetchDataFromPlausible({siteId, year, month}) {
  const daysInMonth = getDaysInMonth(year, month) 
  const date = `${year}-${month}-01, ${year}-${month}-${daysInMonth}`;
  const metrics = "pageviews";

  const response = UrlFetchApp.fetch(`${PLAUSIBLE_URL}/api/v1/stats/timeseries?site_id=${siteId}&period=custom&date=${date}&metrics=${metrics}`, {
    method: 'GET',
    headers: {
      'Authorization': `Bearer ${TOKEN}`
    }
  });

  return transformDataFromPlausible(response.getContentText());
}

function createSheetSkeleton(year, month) {
  spreadsheet.setActiveSheet(templateSheet)

  const sheet = spreadsheet.duplicateActiveSheet();
  sheet.setName(SHEET_NAME({ year, month }));

  const days = getDaysInMonth(year, month);

  let currentRow = START_AT_ROW;

  for (let day = 1; day <= days; day++) {
    sheet.insertRowBefore(currentRow);
    
    sheet.getRange(currentRow, colNum(FIRST_COL_LETTER)).setValue(day);

    sheet.getRange(currentRow, colNum(SUM_COL_LETTER)).setValue(`=SUM(${FIRST_DATA_COL_LETTER}${currentRow}:${LAST_DATA_COL_LETTER}${currentRow})`)

    currentRow++;
  }

  // Current row is already one row ahead as the last cycle still adds 1
  const avgTitleRow = sheet.getRange(`${FIRST_COL_LETTER}${currentRow}`);
  avgTitleRow.setValue('AVG');
  avgTitleRow.setFontWeight("bold");

  const avgValueRow = sheet.getRange(`${SUM_COL_LETTER}${currentRow}`);
  avgValueRow.setValue(`=AVERAGE(${SUM_COL_LETTER}${START_AT_ROW}:${SUM_COL_LETTER}${currentRow-1})`);
  avgValueRow.setNumberFormat("0");
  return sheet;
}

function fillStats(sheet, col, stats) {
  Object.keys(stats).forEach((day) => {
    const lastRowNum = sheet.getLastRow();
    const lastRow = sheet.getRange(`${col}${lastRowNum}`);
    const pageviews = stats[day];
    
    const row = parseInt(day) + START_AT_ROW - 1;

    sheet.getRange(`${col}${row}`).setValue(pageviews);
    lastRow.setValue(`=AVERAGE(${col}${START_AT_ROW}:${col}${lastRowNum - 1})`)
    lastRow.setNumberFormat("0");
  });
}

async function run(year = "2023", month = "12") {
  const sheet = createSheetSkeleton(year, month);

  for (const { siteId, col } of SITES) {
    const stats = fetchDataFromPlausible({
      siteId,
      year,
      month,
    });
    fillStats(sheet, col, stats);
  }
}

async function runForYear(year = "2023") {
  for (let month = 1; month <= 12; month++) {
    run(year, month);
  }
}

function onOpen() {
  ui.createMenu('Plausible Stats')
    .addItem('Fetch Stats For Month', 'showMonthDialog')
    .addItem('Fetch Status For Year', 'showYearDialog')
    .addToUi();
}

function showYearDialog() {
  const result = ui.prompt('Enter Year and Month (YYYY)', ui.ButtonSet.OK_CANCEL);

  if (result.getSelectedButton() == ui.Button.OK) {
    const year = result.getResponseText();
    runForYear(year);
  }
}

function showMonthDialog() {
  const result = ui.prompt('Enter Year and Month (YYYY-MM)', ui.ButtonSet.OK_CANCEL);

  if (result.getSelectedButton() == ui.Button.OK) {
    const [year, month] = result.getResponseText().split('-');
    run(year, month);
  }
}