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

Yuri Khristich · Accepted Answer · 2021-08-22 22:01:38Z

2

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);

edited Aug 22, 2021 at 22:01

answered Aug 22, 2021 at 15:14

Yuri Khristich

14.6k3 gold badges11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

patwick600akabob Over a year ago

Thanks Yuri, I ended up using the bottom solution. I couldn't work out how to log the array with the 2nd function which does look a lot cleaner. Also weirdly it was telling me that getSheetByName wasn't a function but I implemented with your last solution and it was fine?? Thanks again, great code!

Yuri Khristich Over a year ago

Oops. My bad. It was the typo. There should be ss instead of SpreadsheetApp --> const sheet = ss.getSheetByName('Sheet1');

patwick600akabob Over a year ago

Ah I see, I still couldn't get the text-finder to log the array. Is it much better to use it instead of the nested loop? Which is working perfect by the way. My next plan is to apply Math.max to get the highest number and then I want to implement dynamic ranges, probably using coordinate syntax instead of cell references.

Yuri Khristich Over a year ago

If you want to delete all texts that contain "m/s" all you need is just to tweak the regex. Replace range.createTextFinder('m/s').replaceAllWith(''); with range.createTextFinder(".*m/s.*").useRegularExpression(true).replaceAllWith(''); in textFinder example. Or replace replace(/\s*m\/s/g,''); with replace(/.*m\/s.*/,''); in the nested loops example.

Yuri Khristich Over a year ago

As for what is better "textFinder" or to loop through the plain JS arrays -- it's up to you. "TextFinder" doesn't give you the array, you will need to use range.getValues() after. So the both ways are about the same after all, in your case.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related