Google Apps Script Tutorial zum Beherrschen von Makros

Effektive Führungskräfte wissen, dass Zeit der limitierende Faktor ist… Nichts anderes zeichnet erfolgreiche Führungskräfte vielleicht so sehr aus wie ihre zärtliche liebevolle Pflege der Zeit.

Peter Drucker

Zeit ist unsere wertvollste Ressource. Wir wollen es für die effektivsten und wertsteigerndsten Aktivitäten ausgeben, die wir können, nicht nur, weil diese normalerweise den höchsten Geldwert haben, sondern auch, um uns selbst ständig herauszufordern und unsere Arbeitszufriedenheit zu maximieren.

Es gibt viele Möglichkeiten, Ihre Effizienz und Produktivität zu verbessern, um Ihre Zeit besser zu nutzen. In einem früheren Artikel über Google Sheets habe ich ausgeführt, dass die Leistungsfähigkeit der Online-Zusammenarbeit ein solcher Schlüssel zu gesteigerter Produktivität ist.

In einem anderen Artikel habe ich gezeigt, wie die Programmiersprache Python ein leistungsstarkes Analyse- und Aufgabenautomatisierungstool für Finanzexperten sein kann.

Inspiriert davon möchte ich nun ein Google Apps Script Tutorial präsentieren. Mit Google Apps Script können Sie Skripte und Programme in JavaScript schreiben, um die Produkte in Googles G Suite zu automatisieren, zu verbinden und zu erweitern, einschließlich Sheets, Docs, Slides, Gmail, Drive und einige andere. Das Erlernen erfordert Zeit, ebenso wie das Schreiben der Skripte, aber die Produktivität steigt und die zusätzlichen Möglichkeiten, die es eröffnet, sind es wert.

Lassen Sie uns als ersten Schritt mit einem vertrauten Konzept beginnen: Makros.

Aufzeichnen und Verwenden von Makros in Google Sheets

Wenn Sie längere Zeit mit Excel gearbeitet haben, sind Sie bestimmt schon einmal mit der Makroschnittstelle VBA (Visual Basic for Applications) von Excel in Berührung gekommen. Entweder indem Sie sie selbst aufnehmen oder schreiben oder von anderen huckepack nehmen.

Makros sind eine großartige Möglichkeit, sich wiederholende und langwierige Arbeitsabläufe zu automatisieren. VBA ist vielleicht keine Sprache, für deren Erlernen Sie viel Zeit aufgewendet haben, aber das Schöne war, dass Sie es nicht wirklich brauchten, um produktiv zu werden und Ihre eigenen Makros zu erstellen. Sie könnten einfach den Workflow aufzeichnen, den Sie automatisieren möchten, und dann in den Code gehen und alle kleinen Änderungen vornehmen, die erforderlich sind, um das Makro allgemeiner zu machen.

In gewisser Weise ist VBA eine großartige und in Vergessenheit geratene Lektion, wie man technisch nicht versierte Menschen in das Programmieren einführt . Die Art und Weise, wie Sie Aktionen aufzeichnen und den Code dann zur späteren Überprüfung auffüllen lassen, ist in der Tat eine weitaus pragmatischere Art des Lernens als das Lesen von Lehrbüchern und das passive Ansehen von Tutorials.

Dieselbe Aufzeichnungsfunktion von VBA ist in Google Sheets verfügbar. Hier ist ein einfaches Beispiel für die Verwendung:

Beginnen wir mit einigen Beispieldaten und verwenden eine IMPORTHTML-Abfrage zum Importieren einer Tabelle. In diesem Beispiel habe ich eine Liste der 15 größten Hedgefonds der Welt von Wikipedia heruntergeladen. Es versteht sich von selbst, aber dies ist ein willkürliches Beispiel; Die Absicht ist, dass Sie sich mehr auf die Anwendung als auf das Thema konzentrieren.

Die Makroaufzeichnung wird über folgenden Menüpfad gestartet: Extras > Makros > Makro aufzeichnen.

Wir gehen dann durch die Aktionen (PC-Format), die wir aufzeichnen möchten:

1. Wählen Sie die erste Zeile aus
2. Drücken Sie Umschalt + Strg + Abwärtspfeil, um alles auszuwählen
3. Strg + C zum Kopieren
4. Umschalt + F11, um ein neues Blatt zu erstellen
5. Geben Sie dem Blatt einen neuen Namen
6. Drücken Sie Umschalt + Strg + V, um Werte einzufügen

Wenn Sie fertig sind, drücken Sie die Schaltfläche Speichern im Makrofenster unten, geben Sie ihm einen Namen und eine optionale Tastenkombination.

Für einfachere Aktionen, die genau durch dieselben Schritte repliziert werden können, würde der Prozess hier enden und Sie können Ihr Makro sofort verwenden. In diesem Fall müssen wir jedoch einige Änderungen vornehmen, bevor der Code verwendet werden kann. Beispielsweise muss das Blatt, in das wir kopieren, jedes Mal einen anderen Namen haben. Mal sehen, wie das geht.

Google Apps-Skript manuell schreiben

Jetzt sehen wir zum ersten Mal die Knochen von Google Apps Script; die Programmierplattform, die auf den Servern von Google läuft. Dies treibt unsere Makros an und ermöglicht es Ihnen, sehr komplexe Workflows und sogar Add-Ons für die Anwendungen selbst zu erstellen. Es kann verwendet werden, um nicht nur die Arbeit mit Tabellenkalkulationen zu automatisieren, sondern eigentlich fast alles, was mit Googles G Suite verbunden ist.

Die Programmiersprache von Apps Script ist JavaScript , eine der beliebtesten Programmiersprachen, was bedeutet, dass es eine Fülle von Ressourcen für alle gibt, die ausführlich lernen möchten. Aber genau wie bei VBA müssen Sie das nicht wirklich: Sie können die gleiche Aufzeichnungsfunktion verwenden und einfach die Schritte ausführen, die Sie automatisch wiederholen möchten. Die Ausgabe der Aufnahme sieht vielleicht grob aus und passt höchstwahrscheinlich nicht perfekt zu dem, was Sie erledigen möchten, aber sie bietet einen ausreichend soliden Ausgangspunkt. Lassen Sie es uns jetzt für das Skript tun, das wir gerade aufgenommen haben.

Bei der Aufnahme ist es sinnvoll, darauf zu achten, nicht versehentlich zusätzliche Schritte aufzuzeichnen, die nicht in der endgültigen Aufnahme erfasst werden sollen, aber manchmal schwer zu vermeiden ist: etwas so Einfaches wie das Auswählen einer anderen Zelle vor dem Drücken der Stopptaste Die Aufnahmeschaltfläche wird erfasst und anschließend jedes Mal wiederholt, wenn Sie das Skript ausführen. Der erste Schritt beim Bearbeiten unseres Skripts wäre, es zu bereinigen und solche Schritte zu entfernen. Lassen Sie uns eintauchen, indem Sie im Dateimenü zu Extras> Skripteditor gehen.

Wenn Sie JavaScript kennen, werden Sie dies sofort erkennen, und Sie werden vielleicht auch überrascht sein, das Schlüsselwort „var“ anstelle von „let“ oder „const“ zu sehen, wie Sie es in modernem JavaScript sehen würden. Dies spiegelt die Tatsache wider, dass die JavaScript-Version in Apps Script ziemlich alt ist und viele der neueren Funktionen der Sprache nicht unterstützt. Gegen Ende werde ich jedoch einen Workaround für diejenigen vorstellen, die die neuesten Sprachfunktionen verwenden möchten.

Wenn Sie das Skript zum ersten Mal ausführen, werden Sie nach einer Autorisierung gefragt, was sinnvoll ist, da Skripte alle Ihre Daten ändern (und möglicherweise löschen) können. Sie werden den Autorisierungsprozess höchstwahrscheinlich von anderen Google-Produkten kennen.

Jetzt können wir beginnen, den Code zu ändern. Die Änderungen, die wir vornehmen müssen, sind geringfügig, aber wenn Sie dies zum ersten Mal tun, ist möglicherweise noch ein kurzes Durchsuchen der Sheets Apps Script-Dokumentation und/oder ein schnelles Nachschlagen eines JavaScript-Konzepts erforderlich, z. B. das Arbeiten mit Datumsangaben. Dabei kommt der Tatsache zugute, dass JavaScript eine so weit verbreitete Sprache ist: Eine Lösung für jedes Problem oder jede Funktionalität, die einem in den Sinn kommt, lässt sich meist schnell finden, wenn man seinen Suchbegriff einfach formuliert.

Die Änderungen, die in dieser Version des Skripts gegenüber der aufgezeichneten Originalversion vorgenommen wurden, bestehen darin, dass wir anstelle des hartcodierten Namens für das neue Blatt, das wir erstellen, es jetzt stattdessen mit dem heutigen Datum benennen. Außerdem ändern wir auch den Kopierpfad am Ende, um auf dieses neue Blatt zu verweisen. Die letzten vier Zeilen zeigen auch, wie Sie einige Formatierungsvorgänge ausführen, wie z. B. das Ändern des Werts einer Zelle, das Ändern der Spaltengröße und das Ausblenden von Gitternetzlinien.

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

Wenn Sie das Skript jetzt ausführen, wird angezeigt, dass das neue Blatt tatsächlich mit dem heutigen Datum benannt ist und die Informationen enthält, die als Werte (keine Formeln) aus dem Hauptblatt kopiert wurden.

Diagrammvisualisierungen können jetzt mit demselben Aufzeichnungsprozess hinzugefügt werden. Ich habe dies verwendet, um drei einfache Diagramme zu erstellen.

Das Aufräumen des Codes für jeden sieht ungefähr so ​​​​aus:

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

Machen Sie sich auch hier keine Sorgen, wenn einige der Optionen verwirrend aussehen: Dies wird alles automatisch generiert, Sie müssen nur genug verstehen, um die unnötigen Schritte zu entfernen und später vielleicht kleine Änderungen vorzunehmen.

Fortgeschrittene Google Apps-Skriptbeispiele: Verbinden von Blättern mit Google Drive und Google Präsentationen

Alles nimmt jetzt Gestalt an, aber was ist, wenn die eigentliche Ausgabe, die wir wollen, keine Tabelle, sondern eine Präsentation ist? Wenn dies der Fall ist, ist die meiste Arbeit von hier aus möglicherweise immer noch manuell, und wir haben nicht viel Zeit gespart, wenn wir dies regelmäßig tun müssen.

Lassen Sie uns nun untersuchen, wie es aussehen könnte, die Erstellung einer Präsentation anhand der Beispieldaten aus unserer Tabelle zu automatisieren.

Diese Übung wird nun aus zwei Gründen fortgeschrittener:

1. Wir müssen uns mit der Arbeit mit Google Slides (und Google Drive) zusätzlich zu Sheets vertraut machen.
2. In Präsentationen oder beim Arbeiten zwischen G Suite-Apps im Allgemeinen ist keine „Makro aufzeichnen“-Funktion verfügbar. Das bedeutet, dass Sie genug über Apps Script wissen müssen (und bequem in der Dokumentation für jedes der G Suite-Produkte navigieren können), um Skripte von Grund auf neu zu schreiben.

Dieses nächste Beispiel soll einige grundlegende Bausteine ​​liefern, um Ihnen den Einstieg und die Einarbeitung zu erleichtern.

Lassen Sie uns zunächst eine Vorlage erstellen, die wir später mithilfe unseres Skripts mit Inhalten füllen möchten. Hier sind zwei einfache Präsentationsfolien, die ich zusammengestellt habe:

Als nächstes müssen Sie die ID dieser Vorlage abrufen, da Sie in Ihrem Skript darauf verweisen müssen. Unterbewusst werden Sie diese ID schon oft gesehen haben, denn es handelt sich tatsächlich um die zufällig aussehende Folge von Buchstaben und Zahlen, die Sie in der URL Ihres Browsers sehen:

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p.

Jetzt müssen wir unserem ursprünglichen Skript die folgenden Zeilen hinzufügen. Dies fordert erneut zur Autorisierung auf, diesmal für den Zugriff auf Ihr Google Drive.

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

Sie werden kein unmittelbares visuelles Feedback sehen, wenn Sie dieses Code-Snippet ausführen, aber wenn Sie in den Ordner Ihres Google Drive schauen, in dem Sie die Vorlage gespeichert haben, werden Sie feststellen, dass tatsächlich eine Kopie davon erstellt wurde, und zwar die heutige Datum im Dateinamen. Wir sind gut gestartet!

Lassen Sie uns jetzt mehr Bausteine ​​verwenden, um es mit Inhalten zu füllen, programmgesteuert statt von Hand. Fügen Sie der gleichen Funktion die folgenden Zeilen hinzu:

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

Jetzt wird es etwas interessanter, da wir die erste Seite um das heutige Datum ergänzt haben. In Slides, wie in Sheets, arbeiten Sie mit Objekten (dargestellt durch Klassen), die jeweils Eigenschaften und Methoden (dh angehängte Funktionalität) haben. Diese sind in einer Hierarchie organisiert, wobei SpreadsheetsApp, DriveApp oder SlidesApp das Objekt der obersten Ebene ist. Im obigen Codeausschnitt müssen wir uns Schritt für Schritt durch diese Hierarchie bewegen, um zu dem Element zu gelangen, das wir bearbeiten möchten, in diesem Fall: Der Text in einem Textfeld. Praktisch bedeutet dies, sich durch Presentation-, Slide-, PageElement- und Shape-Objekte zu bewegen, bis wir schließlich zu dem TextRange-Objekt gelangen, das wir bearbeiten möchten.

Den Überblick darüber zu behalten, mit welcher Art von Objekt Sie es zu tun haben, kann verwirrend sein, und die Fehler, die sich aus dem Versuch ergeben, eine Operation auf das falsche Objekt anzuwenden, können schwer zu lösen sein. Leider bieten die Hilfefunktionen und Fehlermeldungen im Skripteditor selbst hier nicht immer viel Orientierung, der Silberstreif am Horizont ist, dass eine solche Aufmerksamkeit zumindest Ihre Qualitätskontrollpraktiken verbessern wird.

Nachdem Sie die Präsentation erstellt und den Titel aktualisiert haben, ist es nun an der Zeit, eines unserer neuen Diagramme darin einzufügen. Unter Berücksichtigung der Objekthierarchie sollte der folgende Code nun sinnvoll sein:

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

Wenn Sie das vollständige Skript ausführen, sollte die Ausgabepräsentation in etwa so aussehen:

Hoffentlich veranschaulicht dieses Beispiel die Prinzipien und bietet Inspiration für den Einstieg in eigene Experimente. Wenn Sie darüber nachdenken, finden Sie sicherlich zumindest einige Beispiele für manuelle Arbeiten, die heute in Ihrem Unternehmen ausgeführt werden und auf diese Weise wirklich automatisiert werden sollten. Dies dient dazu, Zeit zum Nachdenken, Analysieren und Urteilen zu gewinnen, anstatt Daten mechanisch von einem Format und / oder Ort zu einem anderen zu verschieben. Verbesserung der Entwicklungserfahrung Wie bereits erwähnt, ist die in Google Apps Script unterstützte JavaScript-Version alt und die Funktionalität des Online-Script-Editors sehr eingeschränkt. Wenn Sie nur ein Makro aufzeichnen oder ein paar Dutzend Zeilen schreiben, werden Sie es nicht wirklich bemerken. Wenn Sie jedoch ehrgeizige Pläne haben, alle Aspekte Ihrer wöchentlichen oder monatlichen Berichterstattung zu automatisieren, oder Plugins erstellen möchten, werden Sie froh sein zu wissen, dass es ein Befehlszeilentool gibt, mit dem Sie mit Ihrer bevorzugten Entwicklungsumgebung entwickeln können .

Wenn Sie sich auf einem solchen Niveau befinden, möchten Sie wahrscheinlich auch die neuesten Funktionen nutzen, die JavaScript zu bieten hat, und möglicherweise sogar noch mehr, da Sie mit dem Befehlszeilentool auch in TypeScript entwickeln können.

Verwenden von Python für die Google Sheets-Programmierung

Wenn Sie feststellen, dass die Arbeit mit Apps Script nicht Ihr Ding ist, gibt es je nach Anwendungsfall andere Möglichkeiten. Wenn Sie fortgeschrittenere Zahlenverarbeitung betreiben, sich mit APIs oder Datenbanken verbinden oder einfach die Programmiersprache Python gegenüber JavaScript bevorzugen, dann ist Googles Colaboratory ein unbezahlbares Produkt. Es bietet Ihnen ein Jupyter-Notebook, das auf den Servern von Google ausgeführt wird und mit dem Sie Python-Skripte schreiben können, die sich nahtlos in Ihre Google Drive-Dateien integrieren lassen, und das durch die „gspread“-Bibliothek die Arbeit mit Ihren Tabellenkalkulationsdaten erleichtert.

Ich habe viele der Vorteile von Python in einem Artikel über die Verwendung für Finanzfunktionen beschrieben, der auch als sanfte Einführung in die Arbeit mit Python- und Jupyter-Notebooks in einem geschäftlichen und finanziellen Kontext dient. Ein sehr wichtiger Vorteil für mich ist, dass das Python-Notebook in Colaboratory im Gegensatz zu Apps Script interaktiv ist, sodass Sie die Ergebnisse (oder Fehlermeldungen) nach der Ausführung jeder Zeile oder jedes kleinen Codeblocks sehen.

Automatisierung macht süchtig

Dieses Google Apps Script-Tutorial zeigte einen Einblick in die Möglichkeiten der Programmiersprache von Google. Die Möglichkeiten sind nahezu unbegrenzt. Wenn Sie jedoch kein technisches Hintergrundwissen haben, sehen die Codebeispiele möglicherweise abschreckend aus und Sie denken vielleicht, dass die Produktivitätssteigerungen durch das Erlernen von Google Apps Script möglicherweise nicht ausreichen, um die erhebliche Zeitinvestition aufzuwiegen um es zu lernen.

Dies hängt natürlich von vielen Faktoren ab, einschließlich der Art der Rolle, die Sie in der Zukunft haben oder voraussichtlich haben werden. Aber selbst wenn Sie nicht erwarten, etwas Ähnliches wie die hier gezeigten Beispiele zu tun, kann das Verständnis dessen, was möglich ist und wie viel Aufwand die Umsetzung erfordern würde, Gedanken und Ideen auslösen, wie Sie die Produktivität in Ihrem Unternehmen verbessern können, z Ihre Kunden oder Sie selbst.

Persönlich kann ich die Befriedigung bestätigen, sich zurückzulehnen und einen Knopf zu drücken, der eine Stunde mühsamer manueller Arbeit in weniger als einer Minute erledigt. Nachdem Sie dies zum 50. Mal getan haben, werden Sie für die paar Stunden dankbar sein, die Sie damit verbracht haben, alles zusammenzuschustern, was letztendlich dazu beigetragen hat, Ihre Zeit für wertschöpfendere Beschäftigungen freizugeben. Nach einer Weile machen diese Skalierbarkeitsvorteile süchtig.