I have a google spreadsheet where I want to use a custom function, like this
In the end there will be an arbitrary number of these example fields in the spreadsheet, and I need to count the ever changing order numbers for each customer field.
I have made an onEdit trigger, but that seems to run the function across the entire datasheet producing an error:
"The starting row of the range is too small. at countOrders(Code:25)".
I think the problem is the function is not tied to the cell I put it in, and the code runs relative to the cell.
/*
This function counts the rows above the current row, up until the predefined header
*/
function countOrders() {
//CHANGEABLE SETTINGS
var HEADERTEXT = "Odrenummer" //What kind of text to look for
var COLUMN = 1 // What column to search for the header
var ACTIVEPOS = 5 // Where to look for the active/passive field
// Open current spreadsheet, and get the rownumber where the function is run
var sheet = SpreadsheetApp.getActiveSheet()
var cell = sheet.getActiveCell()
var rowPointer = cell.getRow()
// Initialize the count variabel,
// and make the rowPointer point to the row above the function
var count = 0
rowPointer--
// Count rows between
while (data != HEADERTEXT){
var range = sheet.getRange(rowPointer,COLUMN)
var data = range.getValue()
rowPointer--
// only count up if the ordernumber is not empty
if (data != ""){
count ++
}
}
// Is the status active?
var state = sheet.getRange(rowPointer,ACTIVEPOS);
state = state.getValue()
// Return the counted rows, minus the header
if (state == "Aktiv"){
return count-1
}
return 0
}
function onEdit(e) {
countOrders();
}
function onChange(e) {
countOrders();
}
I expect the output to update if I append more orders, or if I change the status of the field from "Aktiv" to "Passiv".
I also expect to be able to copy paste the area to produce a second one below, making the code work relative to its position.
