data/sheetImport.jsjavascript
/**
* Importing Joshua's workout history from his Google Sheets ("Workout Progress - Upper Body / Lower Body / Core",
* exported as CSV). Each sheet is a grid: the first row is session dates (newest first), each later row an exercise,
* and each cell what was done that day, as `;`-separated groups of `weight, reps`:
*
* 300lbs, 10 one set of 10 at 300 lbs
* 60lbs, 2x5 2 sets of 5
* 50lbs, 6,10 a set of 6, then a set of 10
* 3x8 bodyweight: 3 sets of 8
* 02:00:00 / 2x 30 seconds / 5x0:30 timed holds (Sheets turns 2:00 into 02:00:00)
*
* Anything else is read as far as it can be, and the cell's own text is kept in the workout's note so nothing is lost.
* This module only parses; scripts/import-sheets.js writes to the database.
* @module sheetImport
*/
/**
* Parse CSV text (RFC 4180: quoted fields, "" for a quote, commas and line breaks inside quotes, CRLF, a BOM).
* @param {string} text
* @returns {string[][]}
*/
export const parseCsv = text => {
const rows = []
let row = []
let field = ''
let quoted = false
const source = text.replace(/^\uFEFF/, '')
for (let index = 0; index < source.length; index++) {
const character = source[index]
if (quoted) {
if (character === '"' && source[index + 1] === '"') {
field += '"'
index++
} else if (character === '"') {
quoted = false
} else {
field += character
}
} else if (character === '"') {
quoted = true
} else if (character === ',') {
row.push(field)
field = ''
} else if (character === '\n' || character === '\r') {
if (character === '\r' && source[index + 1] === '\n') index++
row.push(field)
rows.push(row)
row = []
field = ''
} else {
field += character
}
}
if (field || row.length) {
row.push(field)
rows.push(row)
}
return rows
}
/** Known misspellings in the sheets, fixed on import (the original text goes in the report). */
const spellingFixes = [[/pullldown/i, 'pulldown']]
/**
* An exercise name as stored: trimmed, single-spaced, known typos fixed.
* @param {string} name
* @returns {string}
*/
export const cleanName = name => spellingFixes.reduce((text, [pattern, fix]) => text.replace(pattern, fix), name.trim().replace(/\s+/g, ' '))
/**
* The key two names are matched by: case and spacing don't matter ("Seated leg curl" is "Seated Leg Curl").
* @param {string} name
* @returns {string}
*/
export const nameKey = name => cleanName(name).toLowerCase()
/**
* A timed effort as written in the sheet, or null when the text isn't a time: `02:00:00` (Sheets' reading of 2:00)
* becomes `2:00`, `00:20` becomes `0:20`, `30 seconds` stays.
* @param {string} text
* @returns {string|null}
*/
export const readDuration = text => {
const value = text.trim()
const secondsOnly = value.match(/^(\d+)\s*(?:seconds?|secs?|s)$/i)
if (secondsOnly) return `${Number(secondsOnly[1])} seconds`
const clock = value.match(/^(\d{1,2}):(\d{2})(?::(\d{2}))?$/)
if (!clock) return null
// Sheets turns "2:00" into "02:00:00" (hh:mm:ss); a hold is minutes:seconds, so hh:mm:00 reads as mm:ss
const [minutes, seconds] = clock[3] === '00' || clock[3] === undefined ? [clock[1], clock[2]] : [String(Number(clock[1]) * 60 + Number(clock[2])), clock[3]]
return `${Number(minutes)}:${seconds}`
}
/**
* Read a list of reps like `10`, `2x5`, `6,10`, `2x6, 4` into set groups.
* @param {string} text
* @returns {Array<{sets: number, reps: number}>|null} null when any part isn't a rep count.
*/
export const readReps = text => {
const parts = text.split(',').map(part => part.trim()).filter(Boolean)
if (!parts.length) return null
const groups = []
for (const part of parts) {
const multiple = part.match(/^(\d+)\s*x\s*(\d+)$/i)
if (multiple) groups.push({ sets: Number(multiple[1]), reps: Number(multiple[2]) })
else if (/^\d+$/.test(part)) groups.push({ sets: 1, reps: Number(part) })
else return null
}
return groups
}
/**
* One `;`-separated group of a cell: a weight (optional) and its reps, or a timed hold.
* @param {string} text
* @returns {{rows: Array<{weightEffort: (string|null), sets: (number|null), reps: (number|null)}>, exact: boolean}}
* exact is false when part of the text couldn't be read (the cell's text then goes in the note).
*/
export const readGroup = text => {
const value = text.trim()
// A timed hold, possibly with sets: "02:00:00", "2x 30 seconds", "5x0:30"
const timed = value.match(/^(?:(\d+)\s*x\s*)?(.+)$/i)
const duration = timed && readDuration(timed[2])
if (duration) return { rows: [{ weightEffort: duration, sets: Number(timed[1] || 1), reps: null }], exact: true }
// A weight first: "300lbs, 10", "70.5, 8" (lbs implied), "2x100lbs, 3x8" (a pair), "130lba" (typo)
const weighted = value.match(/^((?:\d+\s*x\s*)?\d+(?:\.\d+)?)\s*(lba|lbs?)?\s*,?\s*(.*)$/i)
// A number with no unit is a weight only when it can't be a rep count: a decimal (70.5) or 30 and up (315)
const bareWeight = weighted && !weighted[2] && /,/.test(value) && /^\d+(\.\d+)?$/.test(weighted[1]) && (/\./.test(weighted[1]) || Number(weighted[1]) >= 30)
if (weighted && (weighted[2] || bareWeight)) {
const weightEffort = `${weighted[1].replace(/\s+/g, '')}lbs`
const rest = weighted[3].trim()
if (!rest) return { rows: [{ weightEffort, sets: null, reps: null }], exact: false }
const groups = readReps(rest)
// Exact only with a real unit: a bare number or the "lba" typo is kept in the note too, for review
if (groups) return { rows: groups.map(group => ({ weightEffort, ...group })), exact: /^lbs?$/i.test(weighted[2] || '') }
// Reps followed by words: "10 (right leg)", "6 failed due to quads"
const leading = rest.match(/^([\dx,\s]*\d)\b(.*)$/i)
const leadingGroups = leading && readReps(leading[1])
if (leadingGroups) return { rows: leadingGroups.map(group => ({ weightEffort, ...group })), exact: false }
return { rows: [{ weightEffort, sets: null, reps: null }], exact: false }
}
// Bodyweight: "3x8", "20", "2x15, 10", "12,8,10"
const groups = readReps(value)
if (groups) return { rows: groups.map(group => ({ weightEffort: null, ...group })), exact: true }
const leading = value.match(/^([\dx,\s]*\d)\b(.*)$/i)
const leadingGroups = leading && readReps(leading[1])
if (leadingGroups) return { rows: leadingGroups.map(group => ({ weightEffort: null, ...group })), exact: false }
return { rows: [], exact: false }
}
/**
* A whole cell: its groups, in order.
* @param {string} text
* @returns {{rows: Array<Object>, exact: boolean}}
*/
export const readCell = text => {
// "120lbs, 8: 125lbs, 8" uses ":" between groups where ";" was meant (a time like 2:00 never has a comma before it)
const groups = text.split(/;|:(?=\s*\d+(?:\.\d+)?\s*lbs)/i).map(group => group.trim()).filter(Boolean)
const read = groups.map(readGroup)
return { rows: read.flatMap(group => group.rows), exact: read.every(group => group.exact) && read.length > 0 }
}
/**
* A sheet's CSV as workouts.
* @param {string} text
* @param {string} area The sheet's body area, e.g. 'upper'.
* @returns {{exercises: Array<{name: string, area: string, original: string}>, workouts: Array<Object>, problems: string[]}}
*/
export const parseSheet = (text, area) => {
const [header = [], ...rows] = parseCsv(text)
const problems = []
const dates = header.map((cell, column) => {
if (column === 0) return null
const value = cell.trim()
const date = value.match(/^(\d{4}-\d{2}-\d{2})\.?$/)
if (date && date[1] !== value) problems.push(`${area}: date "${value}" read as ${date[1]}`)
if (!date && value) problems.push(`${area}: column ${column + 1} has "${value}", not a date; its cells are skipped`)
return date ? date[1] : null
})
// Dates run newest first. One that breaks the order while its neighbours keep it (2026-02-18, 2025-02-14,
// 2026-02-11) is a typo: when the neighbours' year makes it fit, that's the date used, and it's reported
const columns = dates.map((date, column) => date ? column : null).filter(column => column !== null)
columns.forEach((column, index) => {
const date = dates[column]
const previous = dates[columns[index - 1]]
const next = dates[columns[index + 1]]
const outOfOrder = (previous && date > previous) || (next && date < next)
if (!outOfOrder || (previous && next && previous < next)) return
const neighbourYear = (previous || next).slice(0, 4)
const fixed = `${neighbourYear}${date.slice(4)}`
if ((!previous || fixed <= previous) && (!next || fixed >= next)) {
problems.push(`${area}: date ${date} is out of order between ${previous} and ${next}; read as ${fixed}`)
dates[column] = fixed
} else {
problems.push(`${area}: date ${date} is out of order between ${previous} and ${next}; kept as written`)
}
})
const exercises = []
const workouts = []
rows.forEach((row, rowIndex) => {
const original = (row[0] || '').trim()
if (!original) return
const name = cleanName(original)
exercises.push({ name, area, original })
row.slice(1).forEach((cell, index) => {
const text = cell.trim()
if (!text) return
const date = dates[index + 1]
if (!date) {
problems.push(`${area}: "${name}" has "${text}" under column ${index + 2}, which has no date; skipped`)
return
}
const { rows: read, exact } = readCell(text)
if (!exact) problems.push(`${area}: ${date} ${name}: "${text}" read as ${read.length ? read.map(each => `${each.weightEffort || 'bodyweight'} ${each.sets ?? '?'}x${each.reps ?? '?'}`).join(', ') : 'nothing'}; the cell's text is kept in the note`)
read.forEach(each => workouts.push({ date, exercise: name, area, row: rowIndex, ...each, note: exact ? null : text }))
if (!read.length) workouts.push({ date, exercise: name, area, row: rowIndex, weightEffort: null, sets: null, reps: null, note: text })
})
})
return { exercises, workouts, problems }
}
/** Equipment guessed from an exercise's name, checked in order; a name can match several. */
const equipmentRules = [
[/dumbbell|zottman|hammer curl|arnold press(?!.*cable)|wrist curl|shoulder 6 ways/i, 'dumbbells'],
[/barbell|romanian deadlift(?!.*dumbbell)|hip thrust/i, 'barbell'],
[/ez-bar/i, 'EZ bar'],
[/t-bar/i, 'T-bar row'],
[/kettlebell|kickstand rdl/i, 'kettlebell'],
[/cable|pulley|pushdown|face pull(?!.*trx)/i, 'cable machine'],
[/smith/i, 'Smith machine'],
[/trx/i, 'suspension trainer (TRX)'],
[/\bmts\b|machine|chest press|leg press|leg extension|leg curl|pec fly|pulldown|abduction|adduction|hack squat|v-squat|horizontal calf|seated calf|ab core|abdominal crunch|rear delt(?!.*cable)|mid row|high row|iso-lateral|lat pulldown|shoulder press(?!.*dumbbell)/i, 'machine'],
[/incline|decline press|bench press|chest press(?!.*machine)|preacher|seated dip|triceps extension|dumbbell rows|bulgarian/i, 'bench'],
[/dips?\b/i, 'dip bars'],
[/pull-?ups?|chin|hanging|monkey bars|mixed grip leg raises/i, 'pull-up bar'],
[/captain'?s chair/i, "captain's chair"],
[/ab wheel/i, 'ab wheel'],
[/back extension/i, 'back extension bench'],
[/decline crunch/i, 'decline bench'],
[/ball pass/i, 'exercise ball']
]
/**
* Equipment an exercise probably uses, from its name. A guess for Joshua to review; an empty list means bodyweight
* or unknown.
* @param {string} name
* @returns {string[]}
*/
export const guessEquipment = name => {
const found = []
for (const [pattern, equipment] of equipmentRules) {
if (pattern.test(name) && !found.includes(equipment)) found.push(equipment)
}
const saysMachine = /\bmts\b|machine|iso-lateral/i.test(name)
// Free weights only count as a machine exercise when the name says so ("Dumbbell Chest Press" isn't)
if (found.includes('machine') && !saysMachine && (found.includes('dumbbells') || found.includes('barbell'))) found.splice(found.indexOf('machine'), 1)
// Cable work is done standing at the machine, not on a bench
if (found.includes('cable machine') && found.includes('bench')) found.splice(found.indexOf('bench'), 1)
// A machine press isn't also a bench exercise
if (found.includes('machine') && found.includes('bench') && (saysMachine || /chest press/i.test(name))) found.splice(found.indexOf('bench'), 1)
return found
}