If I'm understanding correctly, you want a repeating section that acts as a running log of time spent doing things? That's doable, but does require a bit of infrastructure to run. The HTML is dead simple, but the sheetworkers will get a little complicated. I know you asked for this with a button to do the calculation, but I'm also going to show it with the totals autocalculating.
So, assuming that we have the following html for our repeating section:
<fieldset class="repeating_log">
<input type="text" name="attr_name">
<label>
<span>duration</span>
<input type="number" name="attr_duration">
</label>
<label>
<span>rest?</span>
<input type="checkbox" name="attr_rest" value="1">
</label>
<label>
<span>Elapsed</span>
<input type="number" name="attr_elapsed" readonly>
</label>
<button type="action" name="act_calculate">!!CALCULATE!!</button>
</fieldset>We can keep a running tally of elapsed time with the following javascript:
/**
* Orders a single ID array.
* @param {string[]} repOrder - Array of IDs in the order they are in on the sheet, typically from the corresponding _repOrder attribute.
* @param {string[]} IDs - Array of IDs to be ordered. Aka the default ID Array passed to the getSectionIDs callback
* @param {object} [attributes] - the object containing your results from getAttrs
* @returns {string[]} - The ordered id array
*/
const orderSection = function(repOrder,IDs=[], attributes){
const attributeKeys = Object.keys(attributes);
// create an array with the custom ordered IDs at the front followed by the unordered IDs in the back
const idArr = [...repOrder.filter(v => v),...IDs.filter(id => !repOrder.includes(id.toLowerCase()))]
// filter this array of IDs to remove old IDs from deleted rows that stay in the _reporder attribute
.filter(id => {
const exists = attributeKeys.some(k => k.includes(id));
return exists;
});
return idArr;
};
const calculateTime = (event,attributes,orderedIDs,setObj) => {
// extract the section and sourceID from the sourceAttribute. We'll need this for later work
const [,section,sourceID] = event.sourceAttribute.toLowerCase().match(/(repeating_.+?)_(.+?)_/) || [];
const sourceRow = `${section}_${sourceID}`;
// Find where the section that triggered the listener is located in the IDarray
const sourceIndex = orderedIDs.indexOf(sourceID)
// separate all the IDs that are before the sourceID, not including the sourceID
const beforeIDs = orderedIDs.slice(0,sourceIndex);
// A function to work through the beforeIDs from back to front and total the elapsed time
// This pattern is called a queue or burndown. We're going to use it here because it's more efficient that reversing the array and iterating over it normally
const calcElapsed = (total = 0) => {
// extract the last id in the list of IDs that come before our sourceID.
const id = beforeIDs.pop();
const row = `${section}_${id}`;
// get the value of the rest checkbox and convert it to a number
const isRest = +attributes[`${row}_rest`] || 0;
if(isRest){
// if the row we got from beforeIDs is a rest row, then we just return the total and we stop working the queue
return total;
}
const duration = +attributes[`${row}_duration`] || 0;
total+= duration;
if(beforeIDs.length){
// If there are more rows to check, then continue working the queue.
return calcElapsed(total);
}else{
// otherwise we have our total and we just return it
return total;
}
};
const sourceRest = +attributes[`${sourceRow}_rest`] || 0;
const sourceDuration = +attributes[`${sourceRow}_duration`] || 0;
const useSourceDuration = sourceRest ?
0 :
sourceDuration
// Call the calcElapsed function we defined above and get the total. However, if beforeIDs is empty, then we just set elapsed to the duration value of the source row
setObj[`${section}_${sourceID}_elapsed`] = beforeIDs.length ?
calcElapsed(useSourceDuration) :
(+attributes[`${section}_${sourceID}_duration`] || 0);
if(!sourceRest){
//calculate any rows that might be dependent on this row.
let runningTotal = setObj[`${section}_${sourceID}_elapsed`];
// recalculate any values that might be dependent on this value
for (let i = sourceIndex + 1; i < orderedIDs.length; i++){
const iRow = `${section}_${orderedIDs[i]}`;
const iDuration = +attributes[`${iRow}_duration`] || 0;
const iRest = +attributes[`${iRow}_rest`] || 0;
runningTotal += iDuration;
setObj[`${iRow}_elapsed`] = runningTotal;
if(iRest){
break;
}
}
}
};
const getLogAttrs = (event,callback) => {
// extract the section and sourceID from the sourceAttribute. We'll need this for later work
const [,section,sourceID] = event.sourceAttribute.match(/(repeating_.+?)(?:_(.+?))?(?:$|_)/) || [];
// Get the row IDs of each row in the section so that we can get all possibly relevant data.
getSectionIDs(section,idArray => {
// assemble an array of attribute names to get from the array of ids in the section. We'll also get the reporder of the section so that we can sort these ids by their actual position in the section.
const getArr = idArray.reduce((arr,id) => {
// push the name of each repeating attribute we want onto the array that is being assembled
['duration','rest'].forEach(attr => arr.push(`${section}_${id}_${attr}`))
return arr;
},[`_reporder_${section}`]);
// use the getArr to query the R20 database for the values
getAttrs(getArr,attributes => {
// an empty object that we'll use to collect the changes we want to commit to the sheet.
const setObj = {};
// turn the reporder attribute into an array
const repOrder = attributes[`_reporder_${section}`]?.toLowerCase().split(/\s*,\s*/) || [];
// Sort the IDs into their actual order in the section (handles any reordering that a user might have done).
const orderedIDs = orderSection(repOrder,idArray,attributes);
callback(event,attributes,orderedIDs,setObj);
setAttrs(setObj);
});
});
};
// On these events, call our calculateTime function
// Un comment the below listener to enable the calculation button
/*
on('clicked:repeating_log:calculate',(event) => {
getLogAttrs(event,calculateTime);
})
*/
// comment out the below listeners to disable the autocalculation
on('change:repeating_log:rest change:repeating_log:duration clicked:repeating_log:calculate',(event) => {
getLogAttrs(event,calculateTime);
});
on('remove:repeating_log change:_reporder:log',(event) => {
getLogAttrs(event,(e,attributes,orderedIDs,setObj) => {
// This is not the most efficient handling for removals and order changes as it will cause some duplicate calculating of elapsed, but it will work just fine and shouldn't cause any performance issues unless the logs get to be truly massive
orderedIDs.forEach(id => {
const pseudoEvent = {sourceAttribute:`repeating_log_${id}_`};
calculateTime(pseudoEvent,attributes,orderedIDs,setObj);
});
});
});Let me know if the comments need more elaboration or if I misunderstood what you were trying to do.
Here's what it looks like in action with the autocalculation.
