0

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(); 
  • Here i run into the Error - Cannot read property 'range' This is a common problem and is easily solved: onEdit(e) is designed to run when you edit a cell. But if you run it from the Apps Script Editor then you will get the error. This answer in StackOverflow provides a more detailed, easy to understand explanation: TypeError: Cannot read property “range” from undefined. – Tedinoz Nov 8 at 11:59
  • Thanks, for your comment I created a new function recordMax() as installable trigger. But my knowledge about scripts is still to basic. I carry on reading thru the ClassSheet. And poste a update when i come closer. – diGga Nov 8 at 13:35
  • I'm not sure that an installable trigger is required here. A simple trigger was OK (except, of course, that it MUST be called onEdit(). – Tedinoz Nov 10 at 3:18
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);
 
  }
 
}
| improve this answer | |
  • Thanks Tedinoz for this Answer. It works pretty good! Except i wanted to record the value in each row and not just in one cell for the whole other column. maybe i was a bit unclear in this. Let me process this, cool would be when you give me a hint in which row of the code I have to start modifing to achive a row by row record max. – diGga Nov 8 at 13:55
  • I think that I understand now. I posted a second answer; how does that suit you? – Tedinoz Nov 10 at 4:50
0

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();
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.