Palimpsest is for the people, the people are not for palimpsest.
Public
Edited
Oct 16, 2023
allReplies = fetchAllReplies()
repliesDates = fetchRepliesDates()
repliesLast30Days = fetchRepliesByDate(repliesDates, 30)
repliesLatest50 = getRepliesByAmount(repliesDates, 50)
fetchDataRaw = fetchRepliesRaw(1, 5)
/*
* Uses fetchRepliesRaw and extracts the row values from the result
* @param {Number} startRow The first row to get the reply from
* @param {Number} endRow The last row to get the reply from
* @returns An array of row values
*/
async function fetchReplies(startRow, endRow){
const result = await fetchRepliesRaw(startRow, endRow)
return result.valueRanges[0].values;
}
/*
* Fetches specific rows from Google Sheet, fixed columns A:M
* @param {Number} startRow The first row to get the reply from
* @param {Number} endRow The last row to get the reply from
* @returns The result as json
*/
async function fetchRepliesRaw(startRow, endRow){
const repliesFetch = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?ranges=replies_2022!A${startRow}:M${endRow}&majorDimension=ROWS&key=${apiKey}`);
const json = await repliesFetch.json();
return json
}
/**
* Fetches all replies excluding the header row: A2:M
* @returns An array of row values
*/
async function fetchAllReplies(){
const repliesFetch = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?ranges=replies_2022!A2:M&majorDimension=ROWS&key=${apiKey}`);
const json = await repliesFetch.json();
return json.valueRanges[0].values;
}
/**
* Fetches column C of replies excluding the header
* @returns An array of row values
*/
async function fetchRepliesDates(){
const result = await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values:batchGet?ranges=replies_2022!C:C&majorDimension=ROWS&key=${apiKey}`);
const json = await result.json();
const data = json.valueRanges[0].values.slice(1) // Remove header
.map((v,i) => ( new Date(String(v[0]).slice(0, 10)))) // Convert string to simple date
// Remove errors
.filter(v => v.simpleDate != "Invalid Date")
return data
}
/*
* Fetches the latest replies by day amount (30 = last 30 replies)
* @param {Array} dates An array of dates (C row in sheet) to calculate row position from
* @param {Number} dayAmount The number of days to fetch
* @returns An array of row values
*/
async function fetchRepliesByDate(dates, dayAmount){
// Get the laste date from sheet
const lastDate = dates.slice(-1)[0];
// Determine what the start date should be
let startDate = new Date(lastDate);
startDate.setUTCDate(startDate.getDate() - dayAmount);
console.log(startDate, startDate.getTime().toLocaleString());
// Get the row positions
const firstRow = dates.findIndex(v => v.getTime() == startDate.getTime());
const lastRow = dates.length + 1 // header is not included and length is 0 based
const replies = await fetchReplies(firstRow, lastRow);
return replies
}
/**
* Fetches a specific amount of the latest replies
* @param {Array} dates An array of dates (C row in sheet) to calculate row position from
* @param {Number} replyAmount The number of replies to fetch
* @returns {Array} An array of row values
*/
async function getRepliesByAmount(dates, replyAmount){
const lastRow = dates.length;
const firstRow = lastRow - replyAmount +1;
const replies = await fetchReplies(firstRow, lastRow);
return replies
}
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
