All-time-high script - Overwrite column B when the integer in column A is larger

0
votes

I am trying to create a list displaying two columns; the first column live balances of a list of bank accounts (calculated using other information within the sheet) and the second column showing an all-time-high for the corresponding account on that same row.

Therefore, if the live balance (in column A) is greater than the previous all-time-high balance (in column B), then then column A will overwrite column B. If column A is less than column B, column B will continue to display the all-time-high.

If the current balance is 0 and has never had a positive balance - thus indicating it is a new account with no previous all-time-high - the all-time-high shown in column B would show 0 also.

But it is important that the values shown in column A can remain to be live calculations after the script has been triggered and for the script not to overwrite the SUM function behind the figures in column A.

I am new to scripts and have been struggling with this for a while. If anyone can provide any assistance, I'd be grateful.

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

You have to use the methods getDisplayValues and setValue from the Range Class.

The first method gets the value displayed in the cell, independently if it comes from a formula or if it's a manually-imputed value. This will return you a 2D array, so you looping through it with 2 for loops will compare all the values from both columns. Then, use a Switch statement to compare the values in each condition you need.

Knowing this, I can give you some pseudo-code tips:

function compareColumns(){
   var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
   var columnsAB = sprsheet.getRange('A1:B10').getDisplayValues(); 

  for (var i = 0; i < columnsAB.length; i++){


   for (var j = 0; j < columnsAB[i].length; j++){

      //here you can use the Switch to compare the values of columnsAB[i][0] with columnsAB[i][1]

      //if condition meets:
      sprsheet.getRange('B'+i).setValue(columnsAB[i][0]);

   }
 }