My script retrieves and sums time durations from various external sheets, but the duration is coming as a string. How do I convert it properly? Example below:
function retrieveDurations() {
let externalDocs = {
first: 'docURL_one',
second: 'docURL_two'
};
let durationMap = {};
for (let key in externalDocs) {
let book = SpreadsheetApp.openByUrl(externalDocs[key]);
let sheetsList = book.getSheets();
for (let i = 0; i < sheetsList.length; i++) {
let currentSheet = sheetsList[i];
let records = currentSheet.getDataRange().getValues();
for (let j = 5; j < records.length; j++) {
let row = records[j];
let caseName = row[6];
let durationVal = row[4];
if (!caseName) continue;
durationMap[caseName] = (durationMap[caseName] || 0) + durationVal;
}
}
}
let activeBook = SpreadsheetApp.getActiveSheet();
for (let item in durationMap) {
activeBook.appendRow([item, durationMap[item]]);
}
}
Another approach that proved effective was implementing a helper function to convert the duration string into a JavaScript Date object. For instance, I split the string by a delimiter, then created a new date using the hours, minutes, and seconds. This method allowed me to extract the total milliseconds and convert them into seconds or minutes as needed. I found that handling the conversion separately in a dedicated function simplified the process and reduced bugs related to numeric misinterpretation when summing durations.
In a similar scenario, I discovered that the primary issue was the string format returned by the API, which doesn’t automatically convert back to an internal numeric duration. My approach was to implement a utility function that parses the duration string by separating hours, minutes, and seconds from its delimiter. Once split, I computed the total seconds and then converted that to a useful numeric value for summing. This method not only provided consistency while summing durations across multiple sheets but also reduced the errors that arose from directly handling raw string values.