Google Sheets Script - Loop through array, find array items with only text and partial text and replace whole item

Question

I have a code that is half working, the last thing I can't figure out is how to find an array item containing a number (eg 123) and some text (m/s) and replace this whole item with a completely blank array item: []. No number and no m/s.

I am not familiar with most of the terminology and am a complete novice at this. This is what I've got so far:

function condForm() {
  var sh = ss.getSheetByName('Sheet1');
  var range = sh.getRange("D4:D20"); //*** for the sake of brevity setting to 20 instead of 154
  var values = range.getValues();  
  var row = [];
  var cols = values[0].length;

  for (var i = 0, l = values.length; i < l; i++)
  {
    row = values[i];
    for (var j = 0; j < cols; j++)
    {
      if (row[j] === 'TTK') { row[j] = ''; }
      if(typeof row[j] != "string") { continue; } // line to allow indexOf to work
      if (row[j].indexOf('m/s') > -1) { var newValue = row[j].replace('m/s', '') } //attempting to use indexOf to search for partial match to m/s, does not work but does not return error
    }
  }
Logger.log(values);
}

Without code the logger outputs [[400.0], [600.0], [600.0], [600.0], [], [1283 m/s], [TTK], [440.0], [661.0], [771.0], [771.0], [], [960m/s], [TTK], [381.0], [667.0], [667.0]]

And with the code above the output becomes [[400.0], [600.0], [600.0], [600.0], [], [1283m/s], [], [440.0], [661.0], [771.0], [771.0], [], [960m/s], [], [381.0], [667.0], [667.0]] = same number of array items = great success

I attempted to use indexOf to search for any array items containing m/s. It didn't work but also didn't return an error. What's it doing? Am I on the right lines at all?

I would like a way to turn [1283m/s] and [960m/s] into blanks: [].

Thank you for reading.

My code was sourced from Max Makhrov: https://stackoverflow.com/a/47575871/1341041

and from Andres Duarte: https://stackoverflow.com/a/60226151/1341041

Answer 1

As far as I can tell you're changing the array row. But you're never changing the array values.

Probably you need something like this in the middle:

...

if (row[j] === 'TTK') { values[i][j] = ''; }
if (typeof row[j] != "string") { continue; }
if (row[j].indexOf('m/s') > -1) { values[i][j] = row[j].replace('m/s', '') }

...

But actually for your purpose I'd propose to use the native tool TextFinder:

function condForm() {
  const sheet = ss.getSheetByName('Sheet1');
  const range = sheet.getRange('D4:D20');
  // range.createTextFinder('m/s').replaceAllWith('');
  range.createTextFinder('.*m/s.*').useRegularExpression(true).replaceAllWith('');
  range.createTextFinder('^TTK$').useRegularExpression(true).replaceAllWith('');
}

https://developers.google.com/apps-script/reference/spreadsheet/text-finder

Just in case, if you really into the nested loops it as well can be done a bit cleaner this way:

var values = [[400], [600], [600], [600], [], ['1283 m/s'], ['TTK'], [440], [661], [771], [771], [], ['960m/s'], ['TTK'], [381], [667], [667]];

for (row in values) {
  for (col in values[row]) {
    if (values[row][col] == 'TTK') values[row][col] = '';
    // values[row][col] = values[row][col].toString().replace(/\s*m\/s/g,'');
    values[row][col] = values[row][col].toString().replace(/.*m\/s.*/,'');
  }
}

console.log(values);