I am a complete beginner to writing any code. But with the help of a friend and my brother i was able to write a custom function in google sheets that counts a very specific way in a very specific range of the sheet. But first let me explain what i am trying to do.
What i want to do is count the items in the columns above the red cells.
- 1 for every x, x?, BM and k
- 2 for every xx?
- 4 for every KM
? in my example can be anything but it is only used for numbers.
The old sheet had the following string of functions repeated for every red cell and every week (with the appropriate dependencies)
=COUNTIFS(F147:F172;"x?")+COUNTIFS(F147:F172;"x")+COUNTIFS(F147:F172;"k")+COUNTIFS(F147:F172;"xx?")*2+COUNTIFS(F147:F172;"BM")+COUNTIFS(F147:F172;"KM")*4
What i wanted to do is subsitute that string of functions with a function =DIENSTE(). This is of course less prone to errors and if i ever want to count "FWA" with 6 or anything else, it is much easier to change it in a single place instead of 300 times in the sheet itself.
Here is what i came up with:
function DIENSTE() {
var sheet = SpreadsheetApp.getActiveSheet();
var currentCell = sheet.getActiveCell();
var currentRow = currentCell.getRow();
var currentCol = currentCell.getColumn();
var count = 0;
for (var i=1; i<=26; i++) {
var cell = sheet.getRange(currentRow-i, currentCol);
var value = cell.getValue();
if (value == 'KM')
count = count + 4
else if (value.startsWith('xx'))
count = count + 2;
else if ((value == 'k') || (value == 'BM') || (value.startsWith('x')))
count = count + 1;
}
return count
}
The problem is: It works fine once. But everytime i change something in the sheet it does not update the count. Am i missing something? The other thing is i need this to be fast. With the current string of functions it updates instantly. If this does not is there any way to optimize it or any other way to do this?
Any help is very appreciated. Thanks in advance.
