1

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".

Improve this question
1
  • Index0f() can use a second parameter which is a starting index check you favorite JavaScript reference Commented Nov 24, 2020 at 13:29

1 Answer 1

Reset to default
1

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Sry, both ways do not work for me as they still alert "0"
@GeorgSchöppen post a screenshot of your sheet. Because I can't reproduce your issue. And actually post the full code you are using. You might have variables / code that interfere with my code.
@GeorgSchöppen also make sure your don't have extra spaces or capital letters. The searching keyword has to be exactly like that Mueller, Klaus. No extra spaces, no different uppercase or lowercase letters. Exactly like that.
@GeorgSchöppen this is not Mueller, Klaus but Test Test ... Mueller, Klaus Test .. Add this extra and very relevant information to your question. Try again my updated answer (the optimized code).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.