Text
function onEdit(e) {
const ss = e.source;
const sheet = e.range.getSheet();
// --- SETTINGS (match your file) ---
const LOG_SHEET = "Log";
const DATA_SHEET = "Data";
const CHECKBOX_CELL = "D1";
const START_ROW = 7; // first workout row in Log
// ----------------------------------
// Trigger only when the Log checkbox is checked
if (sheet.getName() !== LOG_SHEET) return;
if (e.range.getA1Notation() !== CHECKBOX_CELL) return;
if (e.value !== "TRUE") return;
const log = ss.getSheetByName(LOG_SHEET);
const data = ss.getSheetByName(DATA_SHEET);
if (!data) throw new Error('Data sheet not found. Check the tab name is exactly "Data".');
// Read session header (stored once)
const sessionDate = log.getRange("B1").getValue();
const sessionId = log.getRange("B2").getValue();
const workout = log.getRange("B3").getValue();
// Determine how many rows to process by checking Exercise column (B)
const lastRow = log.getLastRow();
if (lastRow < START_ROW) {
e.range.setValue(false);
return;
}
const exerciseCol = log.getRange(START_ROW, 2, lastRow - START_ROW + 1, 1).getValues(); // B
let lastDataRow = -1;
for (let i = exerciseCol.length - 1; i >= 0; i--) {
if (exerciseCol[i][0] !== "" && exerciseCol[i][0] !== null) {
lastDataRow = START_ROW + i;
break;
}
}
if (lastDataRow === -1) {
e.range.setValue(false);
return;
}
// Pull Log table values A:G (Muscle..Comment) for the filled portion
const logValues = log.getRange(START_ROW, 1, lastDataRow - START_ROW + 1, 7).getValues(); // A:G
// Build rows to append into Data A:I:
// [Date, ID, Workout, Muscle, Exercise, Set, Weight, Unit, Reps]
const rowsToAppend = [];
for (const r of logValues) {
const muscle = r[0]; // A
const exercise = r[1]; // B
const setNr = r[2]; // C
const weight = r[3]; // D
const unit = r[4]; // E
const reps = r[5]; // F
// const comment = r[6]; // G (not stored in Data right now)
if (exercise === "" || exercise === null) continue; // skip empty rows
rowsToAppend.push([sessionDate, sessionId, workout, muscle, exercise, setNr, weight, unit, reps]);
}
if (rowsToAppend.length === 0) {
e.range.setValue(false);
return;
}
// Append to Data at next empty row
const destRow = data.getLastRow() + 1;
data.getRange(destRow, 1, rowsToAppend.length, 9).setValues(rowsToAppend);
// CLEAR ONLY manual input columns in Log (keep formulas!)
// Keep: A (muscle formula), C (set formula), E (unit formula)
log.getRange("B" + START_ROW + ":B" + lastDataRow).clearContent(); // Exercise
log.getRange("D" + START_ROW + ":D" + lastDataRow).clearContent(); // Weight
log.getRange("F" + START_ROW + ":F" + lastDataRow).clearContent(); // Reps
log.getRange("G" + START_ROW + ":G" + lastDataRow).clearContent(); // Comment
// Uncheck checkbox
e.range.setValue(false);
}
Featured
Advertisement
Category
Action