Last active
January 3, 2024 18:17
-
-
Save lilianalillyy/4dfc700814daa0d20f732183a1c0bcaf to your computer and use it in GitHub Desktop.
Plausible Stats
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment