Wertebereich einer Zelle mit Google Tabellen Script überprüfen

In manchen Fällen kann es recht hilfreich sein zu erfahren ob sich eine Zahl innerhalb von einem bestimmten vorher definierten Wertebereich befindet. Man legt hier einfach die kleinste und größte Zahl fest und lässt sich über das Script den Status sogar noch farblich anzeigen.

Es wird einmal geprüft ob die Zahl innerhalb des Bereichs ist und wenn dies der Fall ist, wird der Hintergrund der Zelle grün eingefärbt. Sollte die Zahl außerhalb des angegebenen Wertebereichs sein, dann wird die Zelle rot eingefärbt. Somit lässt sich das Ergebnis direkt und natürlich auch noch viel schneller wahrnehmen. Bei diesem Beispiel ist es natürlich noch sehr übersichtlich aber wenn man diese Funktion in ein größeres Projekt einbaut mit wesentlich mehr Zahlen und Wertebereichen lässt sich ein Ausreißer schnell aufdecken.

Das Script lässt sich natürlich noch weiter anpassen. Anstatt nur den Hintergrund einzufärben könnte man natürlich noch andere Formatierungen vornehmen.

Das Video – Tutorial speziell für Anfänger befindet sich weiter unten und ist natürlich dementsprechend sehr einfach aufgebaut. Wie das Script Deiner Tabelle hinzugefügt wird erfährst Du natürlich in diesem Video – Tutorial. Die Funktion zum Überprüfen des Wertebereichs wird durch eine Funktion aufgerufen die ausgeführt wird sobald sich in der Tabelle etwas ändert, deshalb muss man hier nicht einmal auf einen Button drücken sondern gibt einfach nur die entsprechenden Zahlen ein. Nachdem man Enter gedrückt hat wird die entsprechende Funktion automatisch aufgerufen. Wenn Ihr Fragen habt oder an einem Punkt nicht weiter kommt scheut Euch nicht mich anzuschreiben.

Das Script "Wertebereich einer Zelle überprüfen" zum kopieren.

function onEdit(e){
 wertPruefen();
}
function wertPruefen(){
 // liest den Wert aus Zelle A2 und weist sie der Variablen zahlZumTesten zu
 var zahlZumTesten=SpreadsheetApp.getActiveSheet().getRange("A2").getValue();

 // liest den Wert aus Zelle D1 und weist sie der Variablen kleine Zahl zu
 var kleineZahl=SpreadsheetApp.getActiveSheet().getRange("D1").getValue();

 // liest den Wert aus Zelle D2 und weist sie der Variablen grosseZahl zu 
 var grosseZahl=SpreadsheetApp.getActiveSheet().getRange("D2").getValue();

 // Dieser if - else Block überprüft ob die Zahl kleiner oder größer ist
 if(zahlZumTesten<kleineZahl || zahlZumTesten>grosseZahl){
   // Dieser if - Teil wird ausgeführt wenn sich die Zahl außerhalb des Wertebereichs befindet
   SpreadsheetApp.getActiveSheet().getRange("B1")
   .setValue("Wert außerhalb vom Bereich")
   .setBackgroundRGB(255, 0, 0);
 }else{
   // Dieser else Teil wird ausgeführt wenn die Zahl innerhalb des Wertebereichs ist
   SpreadsheetApp.getActiveSheet().getRange("B1")
   .setValue("Wert innerhalb des Bereichs")
   .setBackgroundRGB(0,255, 0);
  }
}

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 könnt Ihr direkt hier schauen oder auch bei YouTube. Ich wünsche Euch viel Spaß dabei 😉

Das könnte Dich auch interessieren

4 Comments

Diego sagt:

2. April 2017 um 10:30 Uhr

Hallo Chris,
deine Tutorials sind für Anfänger wie mich perfekt gemacht. Ich habe schon viel daraus gelernt. Danke für deine Mühe.

Für meine aktuelle Aufgabenstellung (zeige mir beim Öffnen des Tabellenblatts nur den Zeitraum von heute bis vor 30 Tagen und blende die restlichen Zeilen aus) fehlt mir noch ein letzter Anhaltspunkt. Wie kann ich eine ganze Spalte (bzw. einen großen Bereich) statt einer einzelnen Zelle prüfen? Angenommen in der Spalte A stehen ab Zeile 3 etliche Datumsangaben. Wie müsste die if-Abfrage formuliert werden? Mein Versuch mit "if(A3:AaktuellesDatum)" führt leider nicht zum Ziel.

Freundliche Grüße

Diego

Antworten
- Chris sagt:
  
  2. April 2017 um 17:38 Uhr
  
  Normalerweise hätte ich solch eine Abfrage entweder über die Funktion Filter oder query versucht.
  Das ganze als Script ist schon etwas tricky. Ich habe selbst gerade versucht da eine Lösung zu finden und mir echt den Kopf zerbrochen aber so einfach ist es dann doch nicht. Für einen Profi-Programmierer vermutlich eine einfache Geschichte aber für einen Hobby-Programmierer wie mich schon eine echte Herausforderung.
  Eine mögliche Lösung wäre z.B die Datumsspalte weiter rechts also in B1 noch einmal zu kopieren (also z.B. mit =A1).
  In meinem Script hatte ich also in Spalte A die normalen Datumsangaben ab Zeile 2.
  In Spalte B hatte ich dann jeweils ab Zeile 2 den Inhalt =A2 (das ganze dann einfach runter kopiert).
  In Zelle C1 habe ich die Formel =now() eingetragen um den momentanen Zeitpunkt zu haben der mit den Datumsangaben verglichen wird. Die Spalte B und die Zelle C1 müssen als Zahl formatiert werden (nicht als Datum sondern wirklich als Zahl)
  Folgendes Script hat bei mir funktioniert:
  
  function zeilenAusblenden(){
  var tabellenBlatt=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var startZeile=2; // Ab welcher Zeile mit der Überprüfung angefangen werden soll
  var stopZeile=tabellenBlatt.getLastRow(); // Bis zur letzten Zeile der Tabelle ausführen
  var datumSpalte="B"; // In welcher Spalte sich das Datum zur Überprüfung befindet
  var aktuellesDatum="C1" // In dieser Zelle steht nur die Formel =now() um den aktuellen Zeitpunkt auszugeben
  var anzahlTage=30; // Anzahl der Tage die gezeigt werden sollen.
  
  for(var zeile=startZeile; zeile<=stopZeile; zeile++){ if(tabellenBlatt.getRange(datumSpalte+zeile).getValue()+anzahlTage<=tabellenBlatt.getRange(aktuellesDatum).getValue()){ tabellenBlatt.hideRows(zeile); } } } Das geht sicher auch direkt ohne Umwege über ein Script allerdings habe ich das nicht lösen können. Versuche über //var datum = new Date(); //var zeitStempelFormat="0"; inklusive setNumberFormat(zeitStempelFormat) haben bei mir nicht funktioniert. Das Problem bei den Scripten ist natürlich auch immer die maximale Zeit die nicht überschritten werden darf. Bei großen Listen könnte das Script wegen Zeitüberschreitung vorzeitig abbrechen. Du kannst mir glauben das ich lange nach einer Lösung gesucht habe und nicht wirklich glücklich mit dem Ergebnis bin. Mein Problem dabei war die Formatierung des Datums als Zahl innerhalb das Scripts um das mit den 30 Tagen vergleichen zu können. Falls jemand ein besseres Ergebnis finden sollte würde ich mich über Feedback freuen. Viele Grüße Chris
  
  Antworten
  - Diego sagt:
    
    3. April 2017 um 6:52 Uhr
    
    Hallo Chris,
    
    danke für deine Mühe. Leider dauert diese Variante bei meiner langen Tabelle wirklich viel zu lange. Der Knackpunkt meiner Überlegung war wohl, dass ich nicht einen Bereich angebe, sondern den Start und das Ende dieses Bereichs einzeln deklariere.
    
    Um zumindest nicht zum aktuellen Datum scrollen zu müssen (Google Tabellen öffnet bei mir stets mit markierter Zelle A1), wäre vielleicht ein Script hilfreich, welches beim Öffnen der Tabelle das aktuelle Datum in der Datumsspalte markiert. Gibt es dazu eine Möglichkeit?
    
    Freundliche Grüße
    
    Diego
    
    Antworten
    - Chris sagt:
      
      3. April 2017 um 12:50 Uhr
      
      Du könntest das ganze auch genau anders herum aufbauen. Die If-Abfrage könnte genau umgekehrt aufgebaut sein und rückwärts arbeiten. Dann kannst Du den ganzen Rest später mit:
      SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().hideRows(StartZeile,AnzahlDerZeilenDieAusgeblendetWerdenSollen);
      AnzahlDerZeilenDieAusgeblendetWerdenSollen wären dann alle Zellen bis auf z.B. 31
      Das ganze funktioniert natürlich dann auch nur wenn in Deiner Liste die Einträge vom Datum her korrekt fortlaufend eingegeben wurden.
      Du könntest das dann so programmieren das gezählt wird wie viele Zeilen vorhanden sind und wenn mehr als z.B. 31 vorhanden sind alle anderen davor ausgeblendet werden.
      Das mit der Markierung der Datumsspalte könntest Du über die bedingte Formatierung machen, das würde bestimmt schneller gehen als jede Zeile wieder durch eine IF-Abfrage auszulesen.
      Um eine Zelle oder einen Zellbereich zu selektieren kannst Du folgendes nutzen:
      setActiveSelection("B2");
      https://developers.google.com/apps-script/reference/spreadsheet/sheet#setactiveselectiona1notation
      
      Viele Grüße
      Chris
      
      Antworten

Wertebereich einer Zelle mit Google Tabellen Script überprüfen