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.

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.

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). Die Document-ID findet man in der Adressleiste des Browsers, wenn man die Google Docs-Vorlage aufruft.
• filename: Dateiname, der erzeugt werden soll (wenn leer, wird keine Datei angelegt) z. B. Brief01.pdf, Brief02.pdf…
• Der Rest sind die Platzhalter wie Name, Adresse, Datum, Anrede, die man dann als {{platzhalter}} ersetzt.

App-Script Magic

In Google Sheets im Bereich Erweiterungen >>> Apps Script aufrufen und den Code einfügen (zuvor alles clean machen). Das Projekt benennen z. B. Serienbrief und speichern. Nach dem Speichern die Funktion onOpen per Pfeiltaste ausführen.

Google möchte dann noch eine Verifizierung (unsicher verifizieren).

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();
  }
}