How do I copy the highest value of a cell?-CodePudding

Basically I want to copy a cell but take the largest number that cell had.

I have a cell B1 with initial value 10 Copying cell B1 on another cell ex B2 the value will always be 10 But if cell B1 becomes 5 I want cell B2 to remain 10. But in turn if cell B1 becomes 20 also cell B2 becomes 20 and so on....... if cell B1 still decreases to 8 for example, cell B2 now always remains 20, continuing in this way.

Basically I want B2 to take the value of B1 but not equal only the largest value B1 has ever had.

CodePudding user response：

function max() {
  let mval = parseInt(PropertiesService.getScriptProperties().getProperty('mval'));
  const ss = SpreadsheetApp.openById(gobj.globals.ssid);//this is a global object that I keep things I use a lot.  Just replace it with the id of whatever spreadsheet you using
  const sh = ss.getSheetByName('Sheet0');//change as required
  let val = sh.getRange('B1').getValue();//change as required
  if (val > mval) {
    PropertiesService.getScriptProperties().setProperty('mval',val);
    return val;
  } else {
    return mval;
  }
}

CodePudding user response：

In your situation, how about the following sample script created by Google Apps Script? In this sample script, the script can be used as the custom function.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save it.

function SAMPLE(v, key = "sample") {
  if (v != "" && isNaN(v)) throw new Error("Inputted value is not number.")
  const p = PropertiesService.getScriptProperties();
  if (v == "") {
    p.deleteProperty(key);
  }
  const previousValue = p.getProperty(key);
  if (previousValue) {
    const pv = Number(previousValue);
    v = pv < v ? v : pv;
  }
  p.setProperty(key, v);
  return v
}

In order to use this script, please put a custom function of =SAMPLE(A1Notation) to a cell. By this, the script is run.

Result:

When the above script is run for your flow in your question, the following result is obtained.

In this script, when the cell is empty, the PropertiesService is reset.

Note:

When you want to use this custom function at the multiple cells, in the current stage, one maximum value can be stored. But, when you want to use each maximum value for each cell, for example, please put a custom function of =SAMPLE(A1Notation, "keyName"). By this, the specific key is used for PropertiesServices. By this, each custom function can be used for each maximum value.

References:

CodePudding user response：

Use onEdit trigger to get the latest value of B1, whenever B1 is edited.
Copy that value to B2, if it is larger than current B2 value

/**
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
function onEdit(e) {
  const [sheetName, maxRangeName] = ['Sheet1', 'B1'];
  let sheet;
  if (
    e.range.getA1Notation() !== maxRangeName ||
    (sheet = e.range.getSheet()).getName() !== sheetName
  )
    return;
  const b2Rng = sheet.getRange('B2'),
    [b1, b2] = [e.value, b2Rng.getValue()];
  if (b1 > b2) b2Rng.setValue(b1);
}