Google-sheets – How to define the column/row/range where the script should start

google sheetsgoogle-apps-script

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();

Best Answer

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);
 
  }
 
}
Related Topic