How to define the column/row/range where the script should start

Question

In column Q start at row 15 is dynamic cell which goes down the column and contains a %-Nr. Next to it in column R i want to show the recorded max value per row. During my research i found here this code snipped which is where i was looking for.

Answer

function recordMax() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1:B1");
  var values = range.getValues()[0];
  range.setValues([[values[0], Math.max(values[0], values[1])]]);
} 

Now i tried to fit this to my sheet and changed it to onEdit for autoupdate. Also i wanted to change the Range "A1:B1" to my Q:R start at row 15. This is how far i came and didn't work

var mainWsName = "WATCHLIST AKTIEN";
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);

function onEdit(e) {
  var activeCell = e.range; // Here i run into the Error - Cannot read property 'range'  
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  
  if(wsName == mainWsName && c == 18 && r > 14){ // start in Q15 Nr. to recordMax. show Max in R15
  var range = ws.getRange(r, 18,1,2);
  var values = range.getValues()[0];
  range.setValues([[values[0], Math.max(values[0], values[1])]]);
  }
}

Also it should set back to 0 when cell B2 is empty I guess i would implement another IF something like this

if(wsName == mainWsName && ws.getRange(r,2).getValue()==''){
    ws.getRange(r, 18,1,2).clearContent(); 

Answer 1

Your existing code needs only a few tweaks:

• if(wsName == mainWsName && c == 17 && r >= startrow){

You are trying to detect a value entered in Column Q, so your need to test for c == 17. In addition, I modified "r" to r >= startrow since this allows you to declare startrow as a variable, and re-use it when updating the value in Column R at the end of the function.

• var range = ws.getRange(startrow, colq,r-startrow+1,1);

Row and column indexing for getRange starts at 1. In the answer, the range starts with row 15, in Column Q and the number or rows goes down to, and includes the edited row (r-startrow+1). Compare this with your range parameters "(r, 18,1,2)".

• var values = range.getValues();

The range returns values for a single Column (Column Q),

• var max = Math.max.apply(null, values);

This is a slight variation on your answer. However, I took my code from an answer that specifically covered Google Sheets: Need help finding the maximum value in a column in Google Sheets using Google Apps Script.

• ws.getRange(startrow,colq+1).setValue(max);

The target range is row 15, but the Column index number is for Column R (Q+1).

---

var mainWsName = "WATCHLIST AKTIEN";
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);

function onEdit(e) {

  var activeCell = e.range; 
  // Logger.log("the active cell is "+activeCell.getA1Notation());

  // establish some variables
  var startrow=15;
  var colq=17;
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  // Logger.log("DEBUG: row = "+r+", column = "+c+", sheet = "+wsName);
 
 
  // apply logic to detect an updated value in Column Q
  if(wsName == mainWsName && c == 17 && r >= startrow){ // start in Q15 Nr. to recordMax. show Max in R15
  
  var range = ws.getRange(startrow, colq,r-startrow+1,1);
  // Logger.log("DEBUG: the range = "+range.getA1Notation());
  
  var values = range.getValues();
  // Logger.log(max); // DEBUG
  var max = Math.max.apply(null, values);
  Logger.log("the max value is "+max);
  
  // update cell R15 with the max vaklue in Column Q
  ws.getRange(startrow,colq+1).setValue(max);
 
  }
 
}

Answer 2

This answer addresses a different reading of the OP's question.

Users may enter a percentage value in cell Q15, or any subsequent row in Column Q. The goal is to evaluate the amount entered in Column Q (row 15 and above) against the amount in the same/adjacent row of Column R.

For example:

• for an amount entered in cell Q15, the script will evaluate the maximum of the values in cells Q15 and R15, and will update this value in cell R15;
• for an amount entered in cell Q16, the script will evaluate the maximum of the values in cells Q16 and R16, and will update this value in cell R16.
• This continues for all values in row 15 and above in Column Q.

There is one provisio - when the value in the relevant row of Column R is null. In this case, the script detects the null value and updates the cell value of Column R with the value from Column Q.

---

var mainWsName = "WATCHLIST AKTIEN";
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);

function onEdit(e) {

  var activeCell = e.range;
  // Logger.log("DEBUG: the active cell is "+activeCell.getA1Notation());
  var editvalue = e.value;
 
  var startrow=15;
  var colq=17
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  //Logger.log("DEBUG: row = "+r+", column = "+c+", sheet = "+wsName+", and value = "+editvalue);
 
  if(wsName == mainWsName && c == 17 && r >= startrow){ 
  
    // this is an edit to be updated
  
    // declare the range and get the values
    var range = ws.getRange(r, c,1,2);
    // Logger.log("DEBUG: the range = "+range.getA1Notation());  
    var values = range.getValues();
    
    // now test if R has a value
    if ( values[1] != null){
       // the value in Column R has a value
       range.setValues([[editvalue, Math.max(editvalue, values[1])]]);
      // Logger.log("range updated");
    }
    else {
       // the value in Column R does NOT have a value
     // create a 2D, 2 Column, 1 row array containing the value in Column Q
      var newvalues = [[values[0],values[0]]];
      // update the range with the new array value
      range.setValues(newvalues);
      //Logger.log("R was blank, so updated with Q");
    }
  }
  // updatre the values in the spreadsheet immediately
 SpreadsheetApp.flush();
}