Zellen vergleichen & markieren / Array (Google Tabellen / Spreadsheets) Script

Zellen vergleichen & markieren

Hier zeige ich wie Ihr mit der GOOGLE TABELLEN Script Programmierung einen Bereich durchsuchen und mit einem Suchwort vergleichen könnt. In der Tabelle gibt es eine Zelle mit einem Suchbegriff oder Suchwort. Jede Zelle aus einer vorher ausgewählten Spalte wird überprüft. Rechts neben den Einträgen wird dann eine Markierung gesetzt wenn der Suchbegriff mit dem Eintrag der Zelle übereinstimmt. Wer jetzt das erste mal ein Google Tabellen Script programmiert sollte sich zuerst dieses Tutorial hier anschauen: Mein erstes Google Tabellen Script programmieren

Der Bereich aus der Spalte die durchsucht werden soll wird mittels getRange in ein Array eingelesen und kann dann über eine FOR-Schleife "for(x in data)" abgearbeitet und verglichen werden.

Dieses Tutorial ist extra für Anfänger die sich mit dem Tabellenkalkulationsprogramm GOOGLE TABELLEN / SPREADSHEETS noch nicht so viel beschäftigt haben und zeigt die Funktionen und Möglichkeiten Schritt für Schritt.

Hier das Script zum Kopieren

function markiereZelle(){

    var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tabellenblatt1");
    
    var startzeile=5; // Zeile ab der die Suche beginnen soll
    var endzeile=sheet.getLastRow(); // Zeile in der die Suche beendet wird
    
    var suchwortZelle="D1"; // Zelle wo sich das Suchwort befindet
    var suchSpaltenIndex=1; // Spalte in der gesucht wird
    
    var markierungsSpaltenIndex=2; // Spalte in der die Markierung gesetzt werden soll
    var markierung="x"; // Markierung die gesetzt werden soll
    
   //In nachfolgender Zeile habe ich den letzten Parameter von getRange auf 1 gesetzt, da hier die Anzahl der Spalten gesetzt werden muss.
   //Da wir nur in einer Spalte suchen müssen sollte hier auch eine 1 stehen.
    var daten=sheet.getRange(startzeile, suchSpaltenIndex, endzeile, 1).getValues(); // Daten holen
    
    var aktuelleZeilennummer=startzeile; // Variable die anzeigt in welcher Zeile wir uns befinden
    
    for(x in daten){
    
        var zellInhalt=daten[x];
        
        if(zellInhalt==sheet.getRange(suchwortZelle).getValue()){
        
            sheet.setActiveRange(sheet.getRange(aktuelleZeilennummer,markierungsSpaltenIndex)).setValue(markierung);
        
        }
        
        aktuelleZeilennummer++;
    
    }

}

Nach einem Datum suchen

Wer versucht ein Datum mit der oberen Funktion zu suchen wird leider scheitern. Ich habe hier die Funktion etwas umgeschrieben. Hiermit lässt sich dann nach einem Datum suchen und das Datum in der Suchzelle mit dem aus den einzelnen Zellen vergleichen.

function markiereZelle(){

    var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tabellenblatt1");
    
    var startzeile=5; // Zeile ab der die Suche beginnen soll
    var endzeile=sheet.getLastRow(); // Zeile in der die Suche beendet wird
    
    var suchdatumZelle=new Date(sheet.getRange("D1").getValue()); // Zelle wo sich das Suchwort befindet
    var suchSpaltenIndex=1; // Spalte in der gesucht wird
    
    var markierungsSpaltenIndex=2; // Spalte in der die Markierung gesetzt werden soll
    var markierung="x"; // Markierung die gesetzt werden soll
    
    var daten=sheet.getRange(startzeile, suchSpaltenIndex, endzeile, suchSpaltenIndex).getValues(); // Daten holen
    
    var aktuelleZeilennummer=startzeile; // Variable die anzeigt in welcher Zeile wir uns befinden
    
    for (x in daten) { // repeat loop
    var fDate = new Date(daten[x]);
    
           if (fDate.getDate() == suchdatumZelle.getDate() && 
              fDate.getMonth() == suchdatumZelle.getMonth() && 
              fDate.getFullYear() == suchdatumZelle.getFullYear()) {             

      sheet.setActiveRange(sheet.getRange(aktuelleZeilennummer,markierungsSpaltenIndex)).setValue(markierung); 
       
    }
     aktuelleZeilennummer++;    
   }  
}

Die Verwendung von diesem Script geschieht wie immer auf eigene Gefahr.

Euch gefällt die Datei und Ihr wollt Euch unbedingt bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] 😉

Das Video-Tutorial "Zellen vergleichen und markieren / FOR-Schleife / Array"

Das könnte Dich auch interessieren

7 Comments

Lisa sagt:

22. Februar 2019 um 12:05 Uhr

Hallo Chris, vielen Dank für das tolle Video.
Kannst du mir einen Tip geben, wie man anhand deines Beispiels, wenn man z.B. 5 eingibt, dass die 5 UND die 15 markiert wird?
Bzw. interessiert es mich eher mit Wörtern/Zeichenketten. Wenn du in einigen Zellen Chris stehen hast und in anderen Christian, dass bei dem Suchbegriff "Chris" jeweils beide Varianten markiert werden. Im folgenden interessiert es mich auch, dass Groß- und Kleinschreibung egal sind.
Über einen Tip wäre ich wirklich dankbar.

Antworten
- Chris sagt:
  
  22. Februar 2019 um 15:15 Uhr
  
  Hi Lisa, hierfür kannst Du zum Beipspiel "Reguläre Ausdrücke" benutzen. Schau Dir zu diesem Thema mal folgendes Tutorial an:
  Textinhalt vergleichen & markieren (Google Tabellen / Spreadsheets) Script
  Das Thema "Reguläre ausdrücke ist sehr sehr Umfangreich und wirkt auch extrem kryptisch. Damit kannst Du aber sehr feine Abfragen erstellen. So etwas wird zum Beispiel verwendet um bei Emailadressen alles zuzulassen was Emailadressen sind. Du kannst Dir vorstellen wie viele Varianten es dort gibt. Mit "Regulären Ausdrücken" kannst Du das aber innerhalb von einer Zeile alles schreiben was herausgefiltert werden soll. Das wird aber etwas dauern bis Du Dich dort eingearbeitet hast … aber es lohnt sich meiner Meinung nach, denn diese werden auch in anderen Programmiersprachen oft verwendet.
  Um mehrere Werte gleichzeitig auszuwählen kannst Du das entweder in den regulären Ausdrücken mit aufnehmen (Ein paar Beispiele für reguläre Ausdrücke direkt vom Google Support) oder in der IF Anweisung zwei Bedingungen mit || trennen. Diese || Zeichen bedeuten so viel wie OR also ODER 😉
  Das ganze könnte dann in etwa so aussehen:
  Für ODER:
  Nur eine der beiden Bedingungen müssen erfüllt sein.
  if (Variable1==5 || Variable2=15)
  {Dein Code}
  Nur eine der beiden Bedingungen müssen erfüllt sein.
  if (Variable1==5 || Variable2=15 || Variable3=25)
  {Dein Code}
  
  Für UND:
  Beide Bedingungen müssen erfüllt sein.
  if (Variable1>=5 && Variable2<=15) {Dein Code} Interessannt könnte für Dich auch dieses Tutorial sein: IF / ELSE / ELSE IF Bedingungen (Google Script) Snippet
  
  Viele Grüße
  Chris
  
  Antworten
Julian sagt:

6. November 2019 um 15:10 Uhr

Hallo,
ich komme auch mit diesem Script nicht an das heran was ich suche. Auf der Seite sind zwar hier und da Teile davon aber ich schaffe es nicht mit einem Script 2 Tabellenblätter zu vergleichen. Bei Ungleicheit soll der Text in Tabellenblatt 1 rot sein / wenn das Feld in Blatt 1 Leer und Blatt 2 befüllt ist einen roten Hintergrund haben. Es ist eine größere Tabelle und es geht immer nur explizit um den Vergleich einzelner Zellen (Batt1!A1 mit Blatt2!A2 usw).
Bedingte Formatierung geht nicht, da ich sonst für jede Zelle eine neue Formel machen müsste. Per Script komme ich teilweise mal ein Stück weit aber dann klappt es auch mit der Schleife nicht bis die ganze Tabelle durch ist.
Es klingt so leicht aber ich bin erst am Anfang mit Google Scripts und bräuchte Hilfe. Falls jemand eine Idee hat würde ich mich freuen 🙂

Antworten
- Ibrahim sagt:
  
  22. Oktober 2021 um 15:13 Uhr
  
  Hallo Julian,
  
  ich benötige genau die selbe Funktion. Hattest Du bereits Erfolg und hast was gefunden?
  Ich danke im Voraus und VG
  
  Antworten
Marika sagt:

16. Dezember 2019 um 21:35 Uhr

Hallo Chris,

ganz lieben Dank für deine Arbeit. Ich bin sehr froh, dass ich deine Seite gefunden habe.

Ich stehe vor der Herausforderung, dass ich eine Zeile mit 34 Spalten habe. Nun habe ich einen String und muss wissen in welcher Spalte dieser String (exakt) vorkommt.

Im Prinzip ist es genau wie hier beschrieben, nur dass ich es eben auf Zeilenebene benötige und nicht das Array aus den Spalten heraus. Kennst du dafür eine Fuktion?

LG Marika

Antworten
Jens Donner sagt:

28. Februar 2021 um 13:28 Uhr

Hallo Chris,
da meine Firma sich dazu entschieden hat sich von Excel zu verabschieden und zu Google zu wechseln, kommt man nicht drum herum seine kleinen Helferlein die in Excel über VBA liefen auf apps script umzubauen.
Ich fange erst langsam an und war auch nie der große Profi in VBA, konnte mir aber für manche Sachen etwas zusammenbauen um große Vergleiche von Hand zu vermeiden und somit Flüchtigkeitsfehler auszuschließen. Ich veruche in das Thema über Java Script Bücher und OnlineKurse und Videos in das Thema reinzukommen, leider ist der fast alles in Englisch abgefasst was das Verstehen nicht unbedingt immer fördert.
Jetzt fange ich hier langsam an, muß erneut viel lesen und viel ausprobieren….
Jetzt aber zum Problem :
Ich versuche grade das "Suchen" und "Vergleichen" umzusetzen und zwar in der Art, das ich einen Bereich von 15 Zeilen a 32 Werte(Spalten) habe, also 480 Werte.
Diese 480 Werte liegen in einer anderen Tabelle ca. 50 mal vor (und es werden ständig mehr). Nun Versuche ich herauszubekommen welche der 50 Varianten am ähnlichsten zum aktuellen Bereich sind, also werden die aktuellen 480 Werte, 50 mal verglichen und anhand eines counters die höchste Ähnlichkeit festgestellt, dann wir eine Auswertung des "am ähnlichsten Bereiches" ausgewertet und die Restunterschiede werden eingefärbt . Unter VBA war das kein Problem und in ca. 60s abgeschlossen, das app script bricht nach 6 min mit einem Max-Zeit Fehler ab und hat in dieser Zeit (durch Log festgehalten) es grade drei mal geschafft die 480 Werte zu vergleichen. Warum ist apps script so langsam ?
in VBA rufe ich genauso oft die unterschiedlichen Tabellen auf und greife auf die Einzelwerte zu, ich vermute das es damit zu tun hat, das alles bei Google in der Wolke liegt und die ständigen abwechselnden Zugriffe in der Wolke so lange dauern ?
Ist es sinnvoll bei einem so großen Vergleich die Tabellen in einen Speicher vor Ort zu laden und dann erst mit dem Vergleich zu beginnen ?
Wie geht man da vor ? Ist ein Laden in Arreys der richtige Weg ? Wie lange bleiben diese Arreys im Hintergrund/ bzw. im temporären Bereich ?
Sollte man diese Dateien beim öffnen der Datei schon im Hintergrund laden ?

Ich weiss, viele Fragen und vielleicht fehlt mir auch noch das Grundverständnis (auch für diesen Hinweis wäre ich dankbar und vieleicht ein Tipp wie man an diesem Zustand etwas verändern kann). Hoffentlich lässt es die Zeit zu auf meine Fragen zu antworten.

Vielen Dank für einen weiteren Tipp/Lösung/Anregung !
Mit freundlichem Gruß
Jens D.

Antworten
Camillo sagt:

2. September 2021 um 10:48 Uhr

Hallo Chris,

vielen Dank für das sehr aufschlussreiche Video.

Ich hätte eine Frage, in wieweit müsste ich das Script verändern, damit nicht nur eine Suchwortzelle abgefragt wird, sondern mehrere Suchwortzellen bzw. eine ganze Spalte mit den einzelnen Werten in den Zellen?
Habe ein sehre unkonventionelle Methode gewählt und für jeden “zellInhalt“ und “if“ eine extra Variable erstellt.
Dadurch wird mir nach 6 min eine max. Ablaufzeit des Scripts angezeigt bei hoher Anzahl an SuchZellen.
Somit würde ich gerne das Script verkürzen.

Danke dir schon mal im Voraus.
Beste grüße
Camillo

Antworten

Zellen vergleichen & markieren / Array (Google Tabellen / Spreadsheets) Script