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
}
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.