CODEWITHSUNDEEP
javascriptloopsgoogle-apps-scriptgoogle-sheetsspreadsheet
Georg Schöppen
Georg Schöppen

Reputation: 21

Google Apps Script - Usage of "indexOf" method

first: I really tried hard to get along, but I am more a supporter than a programmer.

I put some Text in Google Calc and wanted to check the amount of the occurances of "Mueller, Klaus" (It appears 5 times within the data range). The sheet contains 941 rows and 1 Column ("A").

Here is my code to find out:

function countKlaus() {
  
  // Aktives Spreadsheet auswählen

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  
  // Aktives Tabellenblatt auswählen
  var sheet = ss.getSheetByName("Tabellenblatt1");

  var start = 1;
  var end = sheet.getLastRow();

  var data = sheet.getRange(start,1,end,1).getValues();
  var curRow = start;
  var cntKlaus = 0;
  
  for( x in data )
  {
    var value = daten[x];
    //ui.alert(value);
    if(value.indexOf("Mueller, Klaus")> -1){
      cntKlaus = cntKlaus + 1;
    }
  }
 
  ui.alert(cntKlaus);
  
}

The result message is "0" but should be "5".

Upvotes: 1

Views: 221

Answers (1)

Marios
Marios

Reputation: 27350

Issues:

You are very close to the solution, except for these two issues:

  • daten[x] should be replaced by data[x].

  • ui.alert(cntKlaus) should be replaced by SpreadsheetApp.getUi().alert(cntKlaus).

Solution (optimized by me) - Recommended:

function countKlaus() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Tabellenblatt1");
  const cntKlaus = sheet
                   .getRange('A1:A' + sheet.getLastRow())
                   .getValues()
                   .flat()
                   .filter(r=>r.includes("Mueller, Klaus"))
                   .length;
  SpreadsheetApp.getUi().alert(cntKlaus);
}

You can leave out this term + sheet.getLastRow() since we are filtering on a non-blank value. But I think it will be faster to have less data to use filter on in the first place.

References:

  • flat : convert the 2D array to 1D array.
  • filter : filter only on "Mueller, Klaus".
  • Array.prototype.length: get the length of the filtered data which is the desired result.
  • includes: check if Mueller, Klaus is included in the text.

Bonus info

Just for your information, my solution can be rewritten in one line of code if that's important to you:

SpreadsheetApp.getUi().alert(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tabellenblatt1").getRange('A1:A').getValues().flat().filter(r=>r.includes("Mueller, Klaus")).length);

Upvotes: 1

Related Questions