Google Sheets ist ein leistungsstarkes Cloud-basiertes Tabellenkalkulationsprogramm, mit dem Sie fast alles tun können, was Sie in Microsoft Excel tun können. Die eigentliche Stärke von Google Sheets ist jedoch die Google Scripting-Funktion.
Google Apps Scripting ist ein Hintergrund-Scripting-Tool, das nicht nur in Google Sheets, sondern auch Google Text & Tabellen, Google Mail, unterstützt , Google Analytics und fast alle anderen Google Cloud-Dienste. Sie können diese einzelnen Apps automatisieren und diese Apps miteinander integrieren.
In diesem Artikel erfahren Sie, wie Sie mit Google Apps-Skripten beginnen, ein grundlegendes Skript in Google Sheets zum Lesen und Schreiben von Zelldaten erstellen und die effektivsten erweiterten Google Sheets Skriptfunktionen.
So erstellen Sie ein Google Apps-Skript:
Sie können gleich mit dem Erstellen Ihres ersten Google Apps-Skripts in Google Sheets beginnen.
Wählen Sie dazu im Menü Extrasund dann Skript-Editor.
Dies öffnet das Skripteditorfenster und verwendet standardmäßig die Funktion myfunction (). Hier können Sie Ihr Google Script erstellen und testen.
In_content_1 all: [300x250] / dfp: [640x360]->Versuchen Sie, eine Google Sheets-Skriptfunktion zu erstellen, mit der Daten aus einer Zelle gelesen, berechnet und die Datenmenge in eine andere Zelle ausgegeben werden.
Die Funktion zum Abrufen von Daten aus einer Zelle sind die Funktionen getRange ()und getValue (). Sie können die Zelle anhand von Zeile und Spalte identifizieren. Wenn Sie also einen Wert in Zeile 2 und Spalte 1 (Spalte A) haben, sieht der erste Teil Ihres Skripts folgendermaßen aus:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var row = 2;
var col = 1;
var data = sheet.getRange(row, col).getValue();
}
Damit wird der Wert von diesem gespeichert Zelle in der Variablen data. Sie können eine Berechnung für die Daten durchführen und diese Daten dann in eine andere Zelle schreiben. Der letzte Teil dieser Funktion lautet also:
var results = data * 100;sheet.getRange(row, col+1).setValue(results); }
Wenn Sie mit dem Schreiben Ihrer Funktion fertig sind, wählen Sie das zu speichernde Laufwerkssymbol aus.
Beim ersten Start Für eine solche neue Google Sheets-Skriptfunktion (durch Auswahl des Ausführungssymbols) müssen Sie die Autorisierung für die Ausführung des Skripts in Ihrem Google-Konto bereitstellen.
Berechtigungen dürfen fortgesetzt werden. Sobald Ihr Skript ausgeführt wird, werden Sie feststellen, dass das Skript die Berechnungsergebnisse in die Zielzelle geschrieben hat.
Nachdem Sie nun wissen, wie Sie eine grundlegende Google Apps-Skriptfunktion schreiben, werfen wir einen Blick auf einige erweiterte Funktionen.
Verwenden Sie getValues zum Laden von Arrays.
Mit Arrays können Sie das Konzept der Berechnung von Daten in Ihrer Tabelle mit Skripten auf eine neue Ebene heben. Wenn Sie eine Variable mit getValues in ein Google Apps-Skript laden, kann die Variable mehrere Werte aus dem Arbeitsblatt laden.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
Die Datenvariable ist mehrdimensional Array, das alle Daten aus dem Blatt enthält. Um eine Berechnung für die Daten durchzuführen, verwenden Sie eine for-Schleife. Der Zähler der for-Schleife durchläuft jede Zeile und die Spalte bleibt basierend auf der Spalte, in die Sie die Daten ziehen möchten, konstant.
In unserer Beispieltabelle können Sie Berechnungen für die drei Zeilen durchführen
for (var i = 1; i < data.length; i++) {
var result = data[i][0] * 100;
sheet.getRange(i+1, 2).setValue(result);
}
}
Speichern Sie dieses Skript und führen Sie es wie oben beschrieben aus. Sie werden sehen, dass alle Ergebnisse in Spalte 2 Ihrer Tabelle angezeigt werden.
Sie werden feststellen, dass sich das Verweisen auf eine Zelle und eine Zeile in einer Array-Variablen von der Funktion getRange unterscheidet.
data [i] [0]bezieht sich auf die Array-Dimensionen, wobei die erste Dimension die Zeile und die zweite die Spalte ist. Beide beginnen bei Null.
getRange (i + 1, 2)bezieht sich auf die zweite Zeile, wenn i = 1 ist (da Zeile 1 der Header ist), und 2 ist Die zweite Spalte, in der die Ergebnisse gespeichert werden.
Verwenden Sie appendRow, um Ergebnisse zu schreiben.
Was ist, wenn Sie eine Tabelle haben, in die Sie Daten in eine neue schreiben möchten Zeile anstelle einer neuen Spalte?
Dies ist mit der Funktion appendRowganz einfach möglich. Diese Funktion stört keine auf dem Blatt vorhandenen Daten. Es wird lediglich eine neue Zeile an das vorhandene Blatt angehängt.
Erstellen Sie als Beispiel eine Funktion, die von 1 bis 10 zählt, und zeigen Sie einen Zähler mit einem Vielfachen von 2 in einem Zählercolumn.
Diese Funktion würde folgendermaßen aussehen:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<11; i++) {
var result = i * 2;
sheet.appendRow([i,result]);
}
}
Hier sind die Ergebnisse, wenn Sie diese Funktion ausführen.
RSS-Feeds mit URLFetchApp verarbeiten
Sie können die vorherige Google Sheets-Skriptfunktion und die URLFetchAppkombinieren, um den RSS-Feed von jeder Website abzurufen und für jeden kürzlich auf dieser Website veröffentlichten Artikel eine Zeile in eine Tabelle zu schreiben
Dies ist im Grunde eine DIY-Methode, um Ihre eigene RSS-Feed-Reader-Tabelle zu erstellen.
Das Skript dafür ist auch nicht allzu kompliziert.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var item, date, title, link, desc;
var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
var doc = Xml.parse(txt, false);
title = doc.getElement().getElement("channel").getElement("title").getText();
var items = doc.getElement().getElement("channel").getElements("item");
// Parsing single items in the RSS Feed
for (var i in items) {
item = items[i];
title = item.getElement("title").getText();
link = item.getElement("link").getText();
date = item.getElement("pubDate").getText();
desc = item.getElement("description").getText();
sheet.appendRow([title,link,date,desc]);
}
}
Wie Sie sehen, zieht Xml.parsejedes Element aus dem RSS-Feed und trennt jede Zeile in Titel, Link, Datum und Beschreibung.
Mit der Funktion appendRowkönnen Sie diese Elemente für jedes einzelne Element im RSS-Feed in die entsprechenden Spalten einfügen.
Die Ausgabe in Ihrem Arbeitsblatt wird angezeigt ungefähr so:
Stattdessen Wenn Sie die RSS-Feed-URL in das Skript einbetten, können Sie in Ihrem Arbeitsblatt ein Feld mit der URL und anschließend mehrere Arbeitsblätter angeben - eines für jede Website, die Sie überwachen möchten.
Verketten von Zeichenfolgen und Hinzufügen eines Zeilenumbruchs
Sie können die RSS-Tabelle weiterentwickeln, indem Sie einige Textbearbeitungsfunktionen hinzufügen und anschließend E-Mail-Funktionen verwenden, um sich eine E-Mail mit einer Zusammenfassung aller neuen Beiträge zu senden im RSS-Feed der Site.
Fügen Sie zu diesem Zweck unter dem Skript, das Sie im vorherigen Abschnitt erstellt haben, einige Skripts hinzu, mit denen alle Informationen in der Tabelle extrahiert werden.
Sie möchten die Betreffzeile und den E-Mail-Textkörper erstellen, indem Sie alle Informationen aus demselben Array "Elemente" analysieren, mit dem Sie die RSS-Daten in die Tabelle geschrieben haben.
Initialisieren Sie dazu den Betreff und die Nachricht, indem Sie die folgenden Zeilen vor die For-Schleife "items" setzen.
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
Anschließend am Ende von Fügen Sie in der Schleife „items“ (direkt nach der Funktion appendRow) die folgende Zeile ein:
message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';
Das Symbol „+“ verkettet alle vier Elemente, gefolgt von „\ n“ ”Für einen Wagenrücklauf nach jeder Zeile. Am Ende jedes Titeldatenblocks möchten Sie zwei Zeilenumbrüche für einen gut formatierten E-Mail-Text.
Sobald alle Zeilen verarbeitet wurden, enthält die Variable "body" die gesamte E-Mail-Nachrichtenzeichenfolge. Jetzt können Sie die E-Mail senden!
So senden Sie E-Mails im Google Apps-Skript
Der nächste Abschnitt in Ihrem Google-Skript ist das Senden der "Betreff" und der "Körper" per E-Mail. Dies in Google Script zu tun ist sehr einfach.
var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);
Die MailApp ist eine sehr praktische Klasse in Google Apps-Skripten, mit der Sie auf den E-Mail-Service Ihres Google-Kontos zugreifen können, um E-Mails zu senden oder zu empfangen E-Mails. Dank dieser Funktion können Sie in der einzelnen Zeile mit sendEmail Senden Sie eine E-Mail nur die E-Mail-Adresse, die Betreffzeile und den Nachrichtentext eingeben.
So sieht die resultierende E-Mail aus .
Kombinieren der Extraktionsfähigkeit Mit dem RSS-Feed einer Website, dem Speichern in einem Google Sheet und dem Senden an sich selbst, einschließlich URL-Links, können Sie die neuesten Inhalte für jede Website bequem verfolgen.
Dies ist nur ein Beispiel für die Leistungsfähigkeit Diese Funktion ist in Google Apps-Skripts verfügbar, um Aktionen zu automatisieren und mehrere Cloud-Dienste zu integrieren.