Currently trying to figure out how to create a custom google sheets function that:
- Takes a given range such as a column like C4:C13 for example,
- Goes through each cell and checks if any of the given values within the cell, (which are all monetary values, no strings), have a strike through or not,
- Returns the sum of each value that has strike through, (not total amount of cells with strikethrough).
From the experimenting and trying to troubleshoot my script, the result throws different errors that don’t make sense or don’t really help me figure out how to move further.
This is what I have so far:
function SUM_STRIKETHROUGH(range) {
//if (!range || !range.length) {
// return 'Invalid range';
//}
var sum = 0;
var sheet = SpreadsheetApp.getActiveSheet();
var range2 = sheet.getRange(range)
var value = range2.getValue();
for (var i = 0; i < range.length; i++) {
for (var j = 0; j < range[i].length; j++) {
if (value.getValue.isStrikethrough()) {
sum += value.getValue;
}
}
}
return sum;
}
// Refreshes the custom function every time the sheet is edited.
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = sheet.getDataRange();
var formulas = range.getFormulas();
for (var i = 0; i < formulas.length; i++) {
for (var j = 0; j < formulas[i].length; j++) {
if (formulas[i][j].indexOf('SUM_STRIKETHROUGH') !== -1) {
var rangeString = formulas[i][j].replace('=SUM_STRIKETHROUGH(', '').replace(')', '');
var rangeArray = sheet.getRange(rangeString);
var rangeValues = rangeArray.getValues(); // get the actual values
sheet.getRange(i + 1, j + 1).setValue(SUM_STRIKETHROUGH(rangeValues));
}
}
}
}
I have some programming knowledge but not enough to search the internet and build the script myself successfully.
Thank you in advance.
New contributor
Conrad Kadelbach is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.