Trim string fetched from a Google Spreadsheet range and return the largest value

Question

I am building a small script and, on it, I need to "read" the largest value in a range of cells. The cells look like this: "a-10", "a-3", "a-4" and so on, but their real values are 10, 3 and 4 respectively. I cannot remove the "a-" from the cells.

What I have so far is this:

Code:

function getLargestValue() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheets=['Sheet1','Sheet2','Sheet3'];
  var values=[];
  for(var i=0;i<sheets.length;i++)
  {
    var sht=ss.getSheetByName(sheets[i]);
    var rng=sht.getDataRange();
    var rngA=rng.getValues();
    for(var j=1;j<rngA.length;j++)
    {
      rngA[j][0] = substring(2, rngA[j][0]);
      values.push(rngA[j][0]);
    }
  }
  values.sort(function(a, b) {return a - b;});
  var max = values[values.length-1];
  SpreadsheetApp.getUi().alert('The max is ' + max); //Just checking to be sure it works
  return max;     
  // must show the largest value on "Max" sheet
}

Link to spreadsheet:

https://docs.google.com/spreadsheets/d/16WrsztYlOV7qDOMBqGYU8az26Bw9NoqdQ4Imk_hZM90/edit?usp=sharing

I still need to trim the strings and insert the largest value in a different sheet. How could I do that?

Thank you.

Answer 1

Before your sort function I would try something like

// Use a regex to get any numbers from the cell value and make
// a new array
var numbers = values.map(function(el) { return /[0-9]+/.exec(el) }

and then at the end before returning

// Put the 'max' value in 'MAX' sheet in cell 'A1'
ss.getSheetByName('MAX').getRange("A1").setValue(max)

Side note: You can also get a maximum number like this:

var max = Math.max(1,2,3,4,5)

OR

var max = Math.max.apply(null,ARRAY_OF_NUMBERS)

Answer 2

function getLargestValue() 
{
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheets=['Sheet1','Sheet2','Sheet3'];
  var values=[];
  for(var i=0;i<sheets.length;i++)
  {
    var sht=ss.getSheetByName(sheets[i]);
    var rng=sht.getDataRange();
    var rngA=rng.getValues();//this will give you a 2D array
    for (var items in rngA) //foreach loop, easier than for loop
    {
      var result = rngA[items][0].substring(2);//split string at 2nd character
      if(!isNaN(parseFloat(result)) && isFinite(result))// check whether it is a number (since cell has strings like "Values")
      {
        values.push(result);//adds all numbers to the array
      }   
    }
  }

  values.sort(function(a, b) {return a - b;});
  var max = values[values.length-1];
  //SpreadsheetApp.getUi().alert('The max is ' + max); //Just checking to be sure it works
  //return max;     
  // must show the largest value on "Max" sheet
  ss.getSheetByName('MAX').getRange("A2").setValue(max)
}