Serienbriefe mit Google Docs/Suite erstellen

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:

Brief in Google Docs (Original)
Brief in Google Slides (Original)

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.

Die Empfänger mit den einzelnen Platzhalterwerten

Drei 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.

/*
Beim start werden neue Menüpunkte zu Sheets hinzugefügt
*/
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Serienbrief')
      .addItem('Erstellen', 'createDocuments')
      .addToUi();
}

/*
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);
  console.log("genfolders", genFolders.length);
  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();
  
  //Durchlaufe alle Objekte und lege die Dokumente an
  data.forEach(data=>{
    createDocument(data, targetFolder);
  });
}

Das ganze in Action

Hier kommt, wie versprochen, noch das Endergebnis als Video:

17 Gedanken zu „Serienbriefe mit Google Docs/Suite erstellen“

  1. Hi!

    super einfach anzuwenden, danke!

    Gibt es eine Möglichkeit einen der Datenpunkte als ein QR Code auf dem GDoc darzustellen?

    VG Jan

    Antworten
  2. 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?

    Antworten
  3. 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

    Antworten
  4. 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

    Antworten
    • 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.

      Antworten
  5. 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

    Antworten
    • 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?

      Antworten
  6. Top Sache, danke fürs teilen. Eine Frage trotzdem. Ist es auch möglich mehrseitige PDF’s zu erstellen?

    Danke und viele Grüße

    Antworten
      • 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.

        Antworten
        • 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.

          Antworten
  7. Alles klar.
    Dann komme ich aktuell so damit klar. Bei Gelegenheit schaue ich mir das Script mal.

    Danke schön.

    Antworten
  8. 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?

    Antworten
    • 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

      Antworten

Spannend? Lass uns Gedanken austauschen!