Wie schreibe ich ein Skript für Google Tabellen?

Inhaltsverzeichnis

In diesem Artikel zeigen wir anhand von zwei/drei konkreten Beispielen wie Sie Ihr erstes Skript schreiben können. Wenn Sie der Anleitung folgen werden Sie in der Lage sein, Daten in eine bestimmte Zelle zu schreiben, Daten von einer Tabelle in eine andere Tabelle zu kopieren. Für erfahrenere Anwender haben wir ein Beispiel erstellt mit welcher Sie eine Schleife einbauen können. Am Besten einfach ausprobieren, es ist viel einfacher als es auf den ersten Blick aussieht.

Was ist ein Google Apps Script?

Google Apps Script ist eine cloudbasierte JavaScript-Skriptsprache zum Erweitern der Funktionalität von Google Apps und zum Erstellen einfacher Cloud-basierter Anwendungen. Sie können sich wiederholende Aufgaben in Google Tabellen automatisieren, genau wie in Microsoft Excel mit VBA.

Es gibt zwei Arten von Skripts, welche Sie in Google nutzen können:

Standalone scripts

Ein eigenständiges Skript ist jedes Skript, das nicht an eine Google Sheets-, Docs-, Slides- oder Forms-Datei oder Google Seite gebunden ist. Diese Skripte erscheinen unter Ihren Dateien in Google Drive.

Bound scripts

Ein Bound Skript ist an eine Google Sheets-, Docs-, Slides- oder Forms-Datei gebunden, wenn es aus diesem Dokument erstellt wurde und nicht als eigenständiges Skript. Sie führen bestimmte Aktionen direkt für diese bestimmte Datei aus. Die Datei, an die ein gebundenes Skript angehängt ist, wird als „Container“ bezeichnet.

In diesem Artikel fokussieren wir uns auf die „bound scripts„.

Die ersten Schritte im Script Editor

Um zu starten, gehen Sie auf die Seite sheets.google.com und erstellen Sie dort eine neue Tabelle. Unter dem Menüpunkt “ Erweiterungen “ wählen Sie bitte “ Apps Script „

Dies öffnet eine neue Seite: den Skripteditor.

Jetzt ist es an der Zeit, ein wenig Code zu schreiben. Wir werden 3 grundlegende Konzepte behandeln – einfaches Lesen und Schreiben in Zellen, bedingte Anweisungen und Schleifen.

Lesen und Schreiben in Zellen

Lassen Sie uns Daten mit dem Google-Skript automatisch in die Tabelle schreiben.

Im folgenden Beispiel werden wir Daten in Zeile 7 und Zeile 8 mit zwei verschiedenen Ansätzen einfügen.

function insertDataToCells() {

//declare variables
var app= SpreadsheetApp; // application to access
var spreadsheet= app.getActiveSpreadsheet(); // access the currently active spreadsheet in the application
var activesheet= spreadsheet.getActiveSheet(); // access the currently active sheet in the spreadsheet

//insert values to range A7,B7,C7 (using A1 Notation)
activesheet.getRange("A7").setValue("Ford");
activesheet.getRange("B7").setValue("Ford Everest");
activesheet.getRange("C7").setValue("2019");

//insert values to range A8,B8,C8 (using the given coordinates: row and column)
activesheet.getRange(8,1).setValue("Kia Motors");
activesheet.getRange(8,2).setValue("Kia Motors Carnival");
activesheet.getRange(8,3).setValue("2020");

}

In den Zeilen 9-11 des Skripteditors: Für das Einfügen von Daten in Zeile 7 von Datasheet_Temp verwenden wir die A1-Schreibweise. Spalten werden als Buchstaben dargestellt und Zeilen als Zahlen.

In Zeile 14-16 des Skripteditors: Einfügen von Daten in Zeile 8 von Datasheet_Temp , stellen wir den Spalten- und Zeilenindex als Koordinaten bereit. Die Spalten- und Zeilenindizierung beginnt bei 1.

Nach der Ausführung dieses Skripts sollte das Datasheet_Temp-Blatt so aussehen:

Beachten Sie, dass wir uns im vorherigen Beispiel nur auf das aktive Blatt beziehen. Was ist, wenn Sie auf ein bestimmtes Blatt verweisen möchten? Wir zeigen Ihnen Optionen, wie Sie auf ein bestimmtes Blatt verweisen können, und zeigen Ihnen gleichzeitig, wie Sie den Wert aus einer Zelle lesen oder darauf zugreifen.

In diesem Beispiel speichern wir die Werte einfach in Zeile 2 des Quellblatts und fügen sie dann in ein anderes Blatt ein.

function copyDataToSeparateSheet() {

//declare variables
var app= SpreadsheetApp; // application to access
var spreadsheet= app.getActiveSpreadsheet(); // access the currently active spreadsheet in the application
var src= spreadsheet.getSheetByName("Datasheet_Temp"); // access the sheet named "Datasheet_Temp" in the spreadsheet
var destination= spreadsheet.getSheets()[2]; // access the sheet indexed 2 in the spreadsheet

//store values from source sheet into variable
var carBrand=src.getRange(2,1).getValue();
var carModel=src.getRange(2,2).getValue();
var yearModel=src.getRange(2,3).getValue();

//insert the stored values to destination sheet
destination.getRange("A2").setValue(carBrand);
destination.getRange("B2").setValue(carModel);
destination.getRange("C2").setValue(yearModel);

}

In Zeile 6 des Skripteditors verwenden wir die Methode getSheetByName(name), die das Blatt mit dem angegebenen Namen „Datasheet_Temp“ zurückgibt. Wenn mehrere Blätter denselben Namen haben, wird das am weitesten links stehende zurückgegeben. Null wird zurück gegeben, wenn kein Blatt mit dem angegebenen Namen vorhanden ist.

In Zeile 7 des Skripteditors: Nehmen wir an, wir kennen den Namen des Blatts nicht, dann können wir mit einem Workaround getSheets()[index] verwenden, um ein bestimmtes Blatt über seinen Index zu referenzieren. getSheets() gibt das Array aller Blätter in der Tabelle zurück, und dann können wir auf das benötigte zugreifen, indem wir den Index angeben (Wenn Sie die Reihenfolge der Blätter kennen und würde dies funktionieren).

Für den Zugriff auf die Werte haben wir die Methode getValue() verwendet, um den Wert der angegebenen Zellen in den Zeilen 10-12 des Skripteditors zurückzugeben. Der Wert kann je nach Wert der Zelle vom Typ Number, Boolean, Date oder String sein.

Nach dem Ausführen des Skripts sollte das Blatt Datasheet_Temp2 so aussehen:

Bedingte Anweisungen

Bedingte Anweisungen werden verwendet, damit Ihr Programm nur dann etwas tut, wenn die Bedingung erfüllt ist. Es gibt verschiedene Arten von bedingten Anweisungen:

if Anweisung

Die IF-Anweisung prüft zunächst, ob eine von Ihnen angegebene Bedingung erfüllt ist. Wenn die Bedingung erfüllt ist, wird der Code in geschweiften Klammern { und } ausgeführt. Andernfalls wird es NICHT ausgeführt.

if-else Anweisung

Die IF-ELSE-Anweisung hat zwei Codeblöcke. Der IF-Block und der ELSE-Block. Der IF-Block wird ausgeführt, wenn die Bedingung erfüllt ist. Andernfalls wird der ELSE-Block ausgeführt.

if-else if Anweisung

Die if-else if-Anweisung kann verwendet werden, um eine Reihe von bedingten Anweisungen zu verketten. Es wird verwendet, um die Bedingungen in sequentieller Reihenfolge zu überprüfen. Wenn eine der Bedingungen zuerst zutrifft, führt der Compiler diese Anweisung aus. Es kommt dann aus dem bedingten Anweisungsblock.

Im folgenden Skript verwenden wir die if-else if-Bedingungsanweisung, um den Status der Daten in Zeile 2 in Datasheet_Temp zu bestimmen.

Conditional Statements
function conditionalStatement() {

var app= SpreadsheetApp;
var spreadsheet= app.getActiveSpreadsheet();
var activesheet= spreadsheet.getSheetByName("Datasheet_Temp");

var yearModel=activesheet.getRange(2,3).getValue();

if(yearModel <= 2010){
activesheet.getRange(2,4).setValue("Old");
} else if(yearModel >= 2011 && yearModel <= 2021){
activesheet.getRange(2,4).setValue("Current");
}  else{
activesheet.getRange(2,4).setValue("N/A");
}

}

Nach dem Ausführen des Skripts sollte das Blatt Datasheet_temp Zeile 2 so aussehen:

Looping Through Cells

Eine Schleife ist ein Stück Code, das eine Reihe von Anweisungen mehrmals ausführt, und jede Ausführung wird als Iteration bezeichnet. Stellen Sie sich vor, wir haben Hunderte von Daten zu verarbeiten; Es ist ineffizient, für alle Daten denselben Code zu schreiben, deshalb verwenden wir eine Schleife.

Es gibt zwei gängige Arten von Schleifen in Google Apps Script.

For loop: Wird verwendet, um eine Reihe von Anweisungen eine bestimmte Anzahl von Malen auszuführen, normalerweise wenn wir die Anzahl der Iterationen im Voraus kennen, bevor die Schleife beginnt.

For (initialization; condition; increment/decrement)

{

//statement

}

While loop: Wird verwendet, um eine Reihe von Anweisungen so lange auszuführen, wie eine Bedingung erfüllt ist. Eine While-Schleife wird in Situationen verwendet, in denen Sie die Anzahl der Iterationen im Voraus nicht kennen.

While (condition)

{

//statement

}

Fügen wir eine Schleifenanweisung hinzu und ändern Sie ein wenig von unserem vorherigen Beispiel.

Im Blatt Datasheet_Temp führen wir eine Schleife von Zeile 2 bis Zeile 8 durch und bestimmen ihren Status basierend auf den vorherigen Bedingungen, die wir verwendet haben.

For Loop
function forLoop() {

var app= SpreadsheetApp;
var spreadsheet= app.getActiveSpreadsheet();
var activesheet= spreadsheet.getSheetByName("Datasheet_Temp");

for (var i=2; i<=8; i++) {
if(activesheet.getRange(i,3).getValue() < 2015){
activesheet.getRange(i,4).setValue("Old");
}
else if(activesheet.getRange(i,3).getValue() >= 2015 && activesheet.getRange(i,3).getValue() <= 2021) {
activesheet.getRange(i,4).setValue("Current");
}
else {
activesheet.getRange(i,4).setValue("N/A");
}
}
}

In Zeile 7 des Skripteditors definieren wir zunächst den Anfangswert unserer Variablen i, der dann als Zähler für die Iteration verwendet wird. Wir haben „2“ geliefert, weil wir mit Reihe 2 beginnen möchten.

Dann fügen wir die zu prüfende Bedingung hinzu. In diesem Fall prüfen wir, ob der Zähler kleiner oder gleich 8 ist, was die letzte Zeile (die Daten enthält) von Datasheet_Temp ist.

Zuletzt der Inkrementierungs-/Dekrementierungsausdruck (++ oder –). Wir haben den Operator ++ (Inkrement) verwendet, da wir wollten, dass er von Zeile 2 bis 8 inkrementiert wird.

Nach dem Ausführen des Skripts sollte das Blatt Datasheet_temp so aussehen:

Lassen Sie uns nun eine While-Schleife verwenden, um jede Zeile ab Zeile 2 zu durchlaufen, vorausgesetzt, wir wissen nicht, was die letzte Zeile ist, und wir sollen nur bis zur letzten Zeile durchlaufen, deren Status nicht leer ist. Dann ändern wir die Schriftfarbe in Grün, wenn der Status „Aktuell“ ist.

While Loop
function whileLoop() {

var app= SpreadsheetApp;
var spreadsheet= app.getActiveSpreadsheet();
var activesheet= spreadsheet.getSheetByName("Datasheet_Temp");

var i=2;

while (activesheet.getRange(i,4).getValue() != "") {
if (activesheet.getRange(i,4).getValue()== "Current") {
activesheet.getRange(i,4).setFontColor("Green");
}
i++;
}
}

Die Statusspalte in Datasheet_Temp sieht nach der Ausführung des Skripts wie folgt aus:

In diesem Artikel haben wir behandelt, wie Sie auf Google App Scripts zugreifen, einfache Funktionen erstellen und die 3 Grundkonzepte kennen. Wir hoffen, Sie finden es nützlich. Google App Script ist eine leistungsstarke Plattform, auf der Sie viel tun und noch viel mehr lernen können.

Benötigen Sie Excel-Hilfe?

Sind Sie an weiterenen Tipps interessiert? Informationen zum Queries in Google Tabellen finden Sie hier und wie Sie mit Zeiten in Excel rechnen können hier.

Haben Sie ein Problem mit Ihrer Excel-Datei? Für nur 49 Fr. analysieren wir Ihre Datei und erstellen einen konkreten Vorschlag. Weitere Infos dazu auf: www.exact-construct.ch