Sei es die Einladung zur Geburtstagsfeier, ein Rundschreiben an die Vereinsmitglieder oder eine andere Marketingaktivität. Serienbriefe ermöglichen eine automatisierte und trotzdem persönliche Ansprache der Empfänger.
In diesem Beitrag möchte ich zeigen, wie man mit Hilfe von Google Sheets und Google Docs auch aufwändig gestaltete Dokumente an eine Vielzahl an Empfängern schicken kann.
Für Lesefaule gibt es am Ende natürlich auch die Videovariante.
Ziel des Projektes
Dieser Beitrag baut auf einem älteren Beitrag von mir auf, in dem ich bereits automatisiert Dokumente erstellt habe. Damals habe ich die Platzhalter einzeln abgefragt. Das war aber recht unpraktikabel, daher möchte ich in diesem Projekt alle Platzhalter aus einem Google Spreadsheet beziehen.
Zunächst einmal die Ziele, die ich mir für diesen Hack gesetzt habe.
- Erstellen einer Serienbriefvorlage mit Platzhaltern (Format {{platzhalter}})
- Google Sheet Tabellendokument mit den Empfängern und den zu ersetzenden Platzhaltern
- Automatisches Erstellen und Ablegen der generierten Briefe als PDF in einem Ordner
Erstellen der Briefvorlage
Ich habe erst angefangen meinen Brief in Google Docs zu schreiben, wie ich es in dem vorhergehenden Beitrag gemacht habe.
Dann ist mir aber eine spannende Idee gekommen!
Google Slides bietet viel mehr Formatierungsoptionen und bietet somit eine clevere Alternative zum erstellen von Dokumenten an. Dort kann man mit Formen und Illustrationen arbeiten, und es ist hier ebenfalls möglich die Platzhalter austauschen zu lassen.
Ich werde als Dokument einfach beides unterstützen:
Erstellen eines Spreadsheets für die Platzhalter
Als letztes erstellen wir unser Spreadsheets in dem sich die einzelnen Empfänger befinden und die Werte für die Platzhalter.
Zwei der Felder werden eine Bedeutung in meiner Logic haben:
- template: Document-Id der Vorlage (so sind wir flexibel)
- filename: Dateiname der erzeugt werden soll (wenn leer, wird keine Datei angelegt)
App-Script Magic
Für alle Nicht-Coder unter euch ist dieser Abschnitt, bis auf den eigentlichen Script, weniger interessant. (Siehe auch GitLab Project)
BATCH_SIZE=50;
/*
Beim start werden neue Menüpunkte zu Sheets hinzugefügt
*/
function onOpen() {
renderMenu();
}
/*
Aktualisiert das Menü
*/
function renderMenu(){
var ui = SpreadsheetApp.getUi();
var progress=PropertiesService.getDocumentProperties().getProperty('progress');
if(progress>0){
ui.createMenu('Serienbrief')
.addItem('Neu starten', 'startOver')
.addItem('Bei Eintrag '+parseInt(progress)+' fortsetzen', 'createDocuments')
.addToUi();
}else{
ui.createMenu('Serienbrief')
.addItem('Erstellen', 'startOver')
.addToUi();
}
}
/*
Startet einen neuen Vorgang
*/
function startOver(){
PropertiesService.getDocumentProperties().setProperty('progress', 0);
createDocuments();
}
/*
Konvertiere die aktuelle Datarange in ein Object-Array und nutze die erste Zeile als Object-Properties
*/
function spreadSheetToObjects(){
//Alle gefüllten Spalten auslesen
var range=SpreadsheetApp.getActiveSheet().getDataRange().getDisplayValues();
//Array für alle Einträge
var data=[];
for(var row=1; row<range.length; row++){ //Durchläuft alle Zeilen bis auf die Überschriften (daher row=1)
var dataObject={};
for (var col=0; col<range[0].length; col++){ //Durchläuft alle Spalten
var key=range[0][col]; //Übernimmt die Überschrift als Object-Property
var value=range[row][col]; //Schreibt den Wert der aktuellen Zeile ins Objekt
dataObject[key]=value; // Fügt den Eintrag zum Array hinzu
}
data.push(dataObject); //Fügt das Datenobjekt in die Liste hinzu
}
return data; //Gibt das Object-Array zurück
}
/*
Zielordner für die erstellten Dokumente.
Wird als "gen" auf gleicher Ebene wie das Spreadsheet angelegt
*/
function getTargetFolder(){
var folderName="gen"; //Name des Zielordners
//Aktuellen Ordner ermitteln:
var folder=DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next();
//Gibt es einen "gen" Ordner?
var genFolders=folder.getFoldersByName(folderName);
if(genFolders.hasNext()){
return genFolders.next();
}else{
//"gen" Ordner erstellen
return folder.createFolder(folderName);
}
}
function createDocument(dataObject, targetFolder){
//Template-File laden
var templateFile=DriveApp.getFileById(dataObject.template);
//Dann eine Kopie im "gen" Ordner erstellen
var document=templateFile.makeCopy(targetFolder);
//Schauen, ob es sich um eine Präsentation handelt:
if(document.getMimeType()=="application/vnd.google-apps.presentation"){
//Dann das Dokument aus der Präsentation erstellen
var slideApp=SlidesApp.openById(document.getId());
//Alle Slides durchlaufen
slideApp.getSlides().forEach(slide=>{
//Alle Platzhalter ersetzen
for(var key in dataObject){
slide.replaceAllText('{{'+key+'}}',dataObject[key]);
}
});
slideApp.saveAndClose();
}else if(document.getMimeType()=="application/vnd.google-apps.document"){
//Dokument ist ein Textdokument -> Offnen und Platzhalter tauschen
var docApp=DocumentApp.openById(document.getId());
for(var key in dataObject){
docApp.getBody().replaceText('{{'+key+'}}', dataObject[key]);
}
docApp.saveAndClose();
}else{
//unknown type - Hier abbrechen
return;
}
//Create pdf export
var pdfFile=getTargetFolder().createFile(
DriveApp.getFileById(document.getId()).getAs(MimeType.PDF),
).setName(dataObject.filename);
//Originaldokument löschen
document.setTrashed(true);
}
/*
Hauptfunktion, die alle Daten aus dem Spreadsheet ausliest und die Dokumente erzeugt
*/
function createDocuments(){
//Die Dokumente in Objekte umwandeln
var data=spreadSheetToObjects();
var targetFolder=getTargetFolder();
var start=parseInt(PropertiesService.getDocumentProperties().getProperty('progress'));
//Durchlaufe alle Objekte und lege die Dokumente an
for(var i=start; i<data.length && i< (start+BATCH_SIZE); i++){
createDocument(data[i], targetFolder);
}
if(i<data.length){
SpreadsheetApp.getUi().alert("Es sind zu viele Einträge für einen Durchlauf. Nutzen Sie die Funktion \"Fortsetzen\" im Menü um die nächsten Dokumente zu erstellen");
PropertiesService.getDocumentProperties().setProperty('progress', i);
renderMenu();
}
}
Das ganze in Action
Hier kommt, wie versprochen, noch das Endergebnis als Video:
Update
Nachdem einige Nutzer in das Problem mit dem Google Script-Timeout gelaufen sind, habe ich den Script so erweitert, dass er nach einer bestimmten Anzahl (BATCH_SIZE) von Dokumenten aufhört und dann an dieser Stelle fortsetzen kann.
Typische Fehlermeldungen:
| Exception: Es konnte kein Element mit der angegebenen ID gefunden werden. Möglicherweise haben Sie das Element nicht bearbeitet oder Sie haben nicht die erforderliche Berechtigung, um darauf zuzugreifen. | Es wurde nicht die richtige Dokument-Id verwendet. Die ID muss von dem Text Dokument oder der Präsentation sein. Nicht vom Scripteditor. |
| Exception: SpreadsheetApp.getUi() kann aus diesem Kontext nicht abgerufen werden. at onOpen(Code:5:27) | Es wurde der falsche Code-Editor verwendet. Der Script muss im Code-Editor von Google Sheets eingetragen werden. |
Hi!
super einfach anzuwenden, danke!
Gibt es eine Möglichkeit einen der Datenpunkte als ein QR Code auf dem GDoc darzustellen?
VG Jan
Witzige Idee! So etwas könnte man entwickeln. Sehe da keine Hürden. Mit dem vorhanden Code aber nicht möglich.
Gute Sache! Leider gibts bei mir diesen Fehler: „Exception: Unexpected error while getting the method or property getFileById on object DriveApp.“ Weisst du woran das liegen könnte?
Wenn du den Script das erste Mal ausführst, sollte eine Berechtigungsanfrage kommen. Der muss auf jeden Fall zugestimmt werden.
Hi Tobias, cooler Code und danke für das Tutorial! Eine Frage: wenn ich statt einem PDF am Ende Word (docx) files haben möchte, was genau muss dann angepasst werden? VG
Hi Tobias, coole Sache und danke für das Tutorial. Wenn ich am Ende anstatt eines PDF ein DOCX und oder Google Docs File raushaben möchte (um es noch nachträglich etwas bearbeiten zu können bevor ich es finalisiere) oder im Zweifel sogar beides PDF und DOCX, wie würde das funktionieren? VG
Hi Yukio,
danke für dein Feedback. Das müsste dann mit
DriveApp.getFileById(document.getId()).getAs(MimeType.MICROSOFT_WORD),funktionieren, habe ich aber noch nicht getestet.Hi Tobias, leider funktioniert das bei mir nicht und ich bekomme einen Error. Sowohl GOOGLE_DOCS (ERROR: Exception: Blob-Objekt muss für diesen Vorgang eine Datenquelle, die nicht null sein darf, aufweisen.) als auch MICROSOFT_WORD (ERROR: Exception: Das Umwandeln von application/vnd.google-apps.document in application/vnd.openxmlformats-officedocument.wordprocessingml.document wird nicht unterstützt.) gehen nicht. Hast du eine Idee, was man sonst noch anpassen müsste? Würde mir super weiterhelfen, danke!
Hallo Yikio,
bist du dir sicher, dass du die richtige Document-Id eingetragen hast. Das die Datenquelle nicht existiert klingt für mich danach.
Nach Word umzuwandeln habe ich noch nie versucht. Wundert mich aber eigentlich das dass nicht geht. Oder ist deine Quelle ein Word-Dokument?
Die Quelle ist ein Google Docs Dokument und wenn ich dein Skript mit PDF belasse, dann funktioniert es auch ganz normal. Insofern sollte es eigentlich nicht an der Quelle liegen, da ändert sich ja nichts. Die einzige Änderung die ich vornehme, ist die Änderung von PDF zu MICROSOFT_WORD bzw. GOOGLE_DOCS. Bei beidem erhalte ich den Error.
Hmm, ok, dass habe ich natprlich nicht getestet.
Hi Yukio, hast du mittlerweile eine Lösung gefunden? Ich habe das gleiche Problem…
Leider nein 🙁
Hallo Tobias, tolle Funktion! Leider bekomme ich beim Ausführen des Scripts folgende Fehlermeldung: Exception: SpreadsheetApp.getUi() kann aus diesem Kontext nicht aufgerufen werden. (Zeile 5, Datei „Code“). Woran kann das liegen? VG Dirk
Hallo Dirk,
hast du die Berechtigungsanfrage bestätigt? Wurde der Code bei einem Spreadsheet in den Code-Editor eingegeben oder über ein eigenständiges App-Script Projekt? Kannst du die Funktion „onOpen“ vielleicht mal direkt im Code-Editor ausführen und gucken ob die Meldung da auch kommt?
ich habe leider dasselbe Problem! Wie kann ich das lösen?
Der Code-Editor wurde direkt in Spreadsheet ausgeführt?
Top Sache, danke fürs teilen. Eine Frage trotzdem. Ist es auch möglich mehrseitige PDF’s zu erstellen?
Danke und viele Grüße
Danke für das Feedback. Mehrseitige PDFs sind absolut kein Problem.
Kannst Du mir einen Tipp geben was ich wo ändern muss, damit ich mehrseitige PDF’s erhalten? Zur Erklärung, ich hätte einen Brief der an x Empfänger gehen und ich hätte gern diese x Briefe in einem PDF.
Achso, es sollen mehrere Dokumente in einem Angelegt werden. Dass ist nur mit einer Script-Erweiterung möglich. Über App Script lassen sich sicherlich mehrere PDFs zu einem Zusammenfassen.
Alles klar.
Dann komme ich aktuell so damit klar. Bei Gelegenheit schaue ich mir das Script mal.
Danke schön.
Hi Tobias, vielen Dank für den hilfreichen Input. Bei mir erscheint die Fehlermeldung: Exception: Invalid argument: id. Ich habe die Doc-ID jetzt schon 3x geprüft. Hast du eine Idee, woran das liegen könnte? Gibt es ein Problem, wenn in einem Dokument nicht alle Platzhalter, die im Sheet angegeben sind, vorhanden sind?
Hat er bei Meldung zufällig noch die Zeilennummer angegeben, an welcher Stelle im Code das Problem entstanden ist? Platzhalter die im Sheet nicht drin sind, sollte er ignorieren.
Wie sieht die Id aus? Gerne können Sie mir auch privat eine Mail schreiben. Dann versuche ich das Problem zu verstehen: mail [at] tobias-sell.com
Vielen Dank für die schnelle Rückmeldung. Ich habe dir eine Mail geschickt.
Hallo Tobias,
danach habe ich gesucht! Danke. Wie können denn alle pdf in dem Ordner dann automatisiert an die einzelnen Empfänger geschickt werden?
Hallo Klaus,
sofern du GMail benutzt, kann der Script um eine Zeile mit der GmailApp-Funktion erweitert werden, die verschickt die Mails und unterstützt auch Anhänge
Hallo, bei mir sagt er „Exception: Das Argument kann nicht null sein: nameDetails“
was kann ich tuen ?
Stell sicher, dass alle Spalten ausgefüllt sind.
Hallo Tobias, vielen Dank für deine tolle Lösung! Ich habe allerdings das Problem, dass nach 34 generierten PDF’s die Fehlermeldung kommt, „Maximale Ausführzeit überschritten“ und dann stoppt. Hast du einen Tipp, wie sich das umgehen/lösen lässt?
Hi Benjamin,
Ja, Mist. Da macht Google dann irgendwann zu. Eine Möglichkeit wäre, dass man nur die markierten Zeilen in PDFs umwandelt und so dann mehrere „Batches“ a 30 pdfs macht.
In
for(var row=1; row würde man dann die ActiveRange nehmen. (Wie) muss ich dazu Zeile 20 abändern?
for(var row=1; row<range.length; row++){ //Durchläuft alle Zeilen bis auf die Überschriften (daher row=1)
Und dann einfach die Spalten in der Tabelle markieren und im Skripteditor auf Create Documents, oder wie ist da der Ablauf?
Nur die Zahl anpassen und es hat funktioniert. Danke nochmals!
Hallo, den Fehler mit der Ausführungszeit kann ich hiernach leider nicht beheben, habe das gleiche Problem. nach ca. 120 von 600 Positionen bricht das Skript ab. Kann ich das Vorgehen einfacher übersetzt bekommen, das zu vermeiden? Bin leider nicht ausreichend bewandert… Dank vorab.
Hallo Klaus,
ich werd die Tage mal schauen, ob ich das in „Batches“ verteilen kann. Kann momentan nichts versprechen.
Tolle Funktion und super erklärt! Leider klappt auch bei mir nicht der import in ein Googel Doc… bei mir kommt folgender Fehler: Exception: Unerwarteter Fehler beim Abrufen der Methode oder Eigenschaft „getFileById“ auf dem Objekt „DriveApp“. Er erstellt dann im Zielordner eine Kopie des Dokuments mit den importierten Daten aber das funktioniert nur für die erste Zeile…
Hi Steffi,
Hast du in der Template Zeile überall eine gültige Template Id? Hast du den Script einmal im Editor ausgeführt und alle Berechtigungen angenommen?
Als letztes kannst du auch das Dokument nochmal neuladen. Manchmal hängt es daran.
Hi Tobi,
ich habe grade alles nochmal neu aufgebaut und getestet. Die pdf’s werden auch wunderbar erzeugt, aber wenn ich dann im Script den Typ auf google_docs ändere, kommt beim Ausführen des Serienbriefs folgender Fehler: „Exception: Blob-Objekt muss für diesen Vorgang eine Datenquelle, die nicht null sein darf, aufweisen.“
Er legt dann eine Kopie des Ursprungsschreibens im gen-Ordner an, aber auch hier nur vom ersten Datensatz…
Hi Steffi, das ist merkwürdig. Kannst mir gerne auch dein Dokument freigeben (oder eine anynymisierte Kopie). Dann kann ich versuchen nachzuvollziehen, woran das liegt. (freigabe an mail at tobias-sell.com)
Das ist total nett, aber das sind geschäftliche bzw. vertrauliche Daten, die kann ich nicht freigeben…
Dann baue ich den Brief doch im Word auf…
Viele Grüße
Steffi
Super Idee und klappt auch toll, aber wie kann ich das ganze per gmail an die Kunden versenden? Danke für die Info
Wenn du Gmail verwendest, kann man die man mit der Gmail-Funktion bei App Scripts das Ganze sehr schnell umsetzen.
Hi Tobias
Echt coole Sache!
Bei mir gibts die Fehlermeldung: Exception: Ungültiges Argument: id. Keinerlei weiteren Details.
ID hab ich kopiert, wo nachfolgend HIER GENOMMEN steht. https://docs.google.com/document/d/HIER GENOMMEN/edit
Kannst du mir weiterhelfen?
LG Nadja
Das ist merkwürdig, das müsste so eigentlich gehen. Ich werd mal schauen, dass ich den Beitrag die Tage mal überarbeite und mögliche Fehlerquellen ausmerze.
Was wenn ich bei mir im Script Editor nur myfunction und nicht Openfunction zur Auswahl habe?
Der Sourcecode im Editor muss komplett ersetzt werden. (Siehe Video)
Hallo, super Script, danke. Gibt es schon eine Lösung, um für markierte Zeilen einen Serienbrief zu erstellen? Ich komme mit ActiveRange nicht klar, bitte um etwas Hilfe.
Hallo Jörg, da bin ich leider bisher nicht zu gekommen.
Vielen Dank für die super hilfreiche Anleitung. Ich bekomme jedes Mal die Fehlermeldung: Exception: Das Argument kann nicht null sein: name.
Ich bin hier etwas überfragt, was das bedeuten soll. Alle Angaben im Sheet sind vorhanden.
Hast du hier vielleicht eine Lösung?
Hi Stephanie,
wichtig ist, das alle Felder ausgefüllt sind.