Serienbriefe oder auch personalisierte Fortbildungsbestätigungen per Hand zu erstellen ist sehr zeitraubend. Mit Hilfe von Word kann man Serienbriefe erstellen, doch sind die Anpassungsmöglichkeiten begrenzt.

Da ich viele Fortbildungsbestätigungen erstellen und dann an Teilnehmern schicken muss, habe ich eine Alternative gesucht und diese bei Google gefunden.

Die Office Suite von Google (Google Docs, Tabellen, Präsentationen) kann mittels „AppScript“ fast unbegrenzt angepasst werden. Dies bietet auch die Möglichkeit Serienbriefe zu erstellen.

Der grobe Ablauf

Die Daten der Fortbildung werden mittels Skript aus einer Google Tabelle gelesen und Zeile für Zeile in ein neues Google Docs gespeichert. Die erstellten Dokumenten werden daraufhin in PDFs umgewandelt und mit einer passenden E-Mail an die Teilnehmer der Fortbildung gesandt. Für diesen Ablauf habe ich folgende Ordnerstruktur in Google Drive erstellt:

Die befüllten Templates werden im Ordner „tempDocs“ gespeichert. Die daraus erstellten PDFs im Ordner „finalPDFs„. Auf diese Struktur bezieht sich der Code im AppScript .

Google Docs Template erstellen

Zuerst benötigt man ein Google Docs Dokument mit Platzhaltern (hier mit geschweifter Klammer dargestellt):

Google Tabelle mit Daten erstellen

Um das Google Docs Dokument zu befüllen, werden Daten aus einer Tabelle benötigt. Diese könnte folgenden Aufbau haben:

Die Spalten werden für die Platzhalter im Google Docs Dokument genutzt, um die Fortbildungsbestätigung zu individualisieren, damit nur ein „Template“ für alle verschiedenen Fortbildungen genutzt werden kann.

AppScript

AppScript heißt die Skriptsprache, welche innerhalb von Google Drive und der Google Office Suite genutzt werden kann. Mittels Code kann man so das Verhalten von z.B.: Google Docs verändern.

Für das Serienbriefbeispiel habe ich ein Skript in die Google Tabelle mit den Daten der Fortbildung erstellt. Den Skripteditor öffnet man innerhalb der offenen Tabelle:

Dann kann folgendes Skript eingefügt werden:

const tempDocsFolder = DriveApp.getFolderById('1b0Bbx67xh6YHnjHyAU6tpQSdvtH8GobO'); 
const finalPDFsFolder= DriveApp.getFolderById('1h3eJ-btrHsh1Qp4e2OXUFYMnMRFeKprK'); 

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Fortbildungen')
      .addItem('Erstelle & Sende Fortbildungen', 'getDataFromCurrentSheet')
      .addToUi();
}

function getDataFromCurrentSheet() {
  
  deleteFilesInFolder(tempDocsFolder);
  deleteFilesInFolder(finalPDFsFolder);
  
  const currentSheetRows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
  const jsonObject = getJsonArrayFromData(currentSheetRows);

  // iteriert durch alle vorhandenen Zeilen des aktuell ausgewählten Tabellenblattes - die Headerzeile wird vorher entfernt
  // wurde bereits eine Mail an den Teilnehmer versandt (Spalte 0 = x)?
  jsonObject.forEach(function(row){
    if(row['versandt'] != 'x'){ 
      fillTemplateDocument(row);      
    }
  });

}

function fillTemplateDocument(tempRow){

  const templateFortbildungDoc = DriveApp.getFileById('1gu4uCXoroR632ZWAUlhUdPUxEKRmfwqWw_m_SMFcmms');
  const newFortbildungDoc = templateFortbildungDoc.makeCopy(tempDocsFolder);

  const formattetDate = tempRow['fortbildung-datum'].replace(/\./g, ''); // regex \. , {datum}
  const formattetTitle = tempRow['fortbildung-titel'].replace(/\s/g,'_'); // regex \s , {titel}
  newFortbildungDoc.setName(formattetDate + '_' + formattetTitle);

  const openDoc = DocumentApp.openById(newFortbildungDoc.getId());
  const body = openDoc.getBody();
  body.replaceText('{vorname}', tempRow['vorname']);  
  body.replaceText('{nachname}', tempRow['nachname']); 
  body.replaceText('{fortbildung-datum}', tempRow['fortbildung-datum']); 
  body.replaceText('{fortbildung-zeit}', tempRow['fortbildung-zeit']); 
  body.replaceText('{fortbildung-titel}', tempRow['fortbildung-titel']);  
  body.replaceText('{fortbildung-inhalte}', tempRow['fortbildung-inhalte']); 
  body.replaceText('{datum-aus}', tempRow['datum-ausstellung']);
  openDoc.saveAndClose();
  
  //erstellt ein PDF aus dem Doc
  const blobPDF = newFortbildungDoc.getAs("application/pdf");
  const newPdfFile = finalPDFsFolder.createFile(blobPDF);

  //nun wird das PDF als Mail versandt
  sendEmail(newPdfFile, tempRow);
  
}

function sendEmail(pdfFile, tempRow){
  const template = HtmlService.createTemplateFromFile('email-template');
  template.vorname = tempRow['vorname'];
  template.nachname = tempRow['nachname'];
  template.fortbildung = tempRow['fortbildung-titel'];
  var message = template.evaluate().getContent();

  MailApp.sendEmail({
    to: tempRow['email'],
    subject: "Fortbildungsbestätigung: " + tempRow['fortbildung-titel'],
    htmlBody: message,
    attachments: [pdfFile]
  });
}

function deleteFilesInFolder(tempFolder){
  const filesInFolder = tempFolder.getFiles();

  while(filesInFolder.hasNext()){
    filesInFolder.next().setTrashed(true);
  }

}

function getJsonArrayFromData(data)
{
  var obj = {};
  var result = [];
  var headers = data[0];
  var cols = headers.length;
  var row = [];

  for (var i = 1; i < data.length; i++){
    // get a row to fill the object
    row = data[i];
    // clear object
    obj = {};
    for (var col = 0; col < cols; col++){
      // fill object with new values
      obj[headers[col]] = row[col];    
    }
    // add object in a final result
    result.push(obj);  
  }
  return result;  
}

Das Skript besteht aus folgenden Funktionen:

• onOpen()
• getDataFromCurrentSheet()
• fillTemplateDocument()
• sendEmail()
• deleteFilesInFolder()
• getJsonArrayFromData()

Die Funktion „onOpen()“ wird aufgerufen, wenn die Google Tabelle geöffnet wird und es wird ein neuer Menüpunkt hinzugefügt:

Klickt man auf „Erstelle & Sende Fortbildungen“ wird die nächste Funktion „getDataFromCurrentSheet()“ aufgerufen. Diese liest alle Zeilen des aktuellen Tabellenblatts aus, wandelt diese mit der Funktion „getJsonArrayFromData()“ in ein JsonObject um und gibt dieses weiter an die Funktion „fillTemplateDocument()„.

Hier werden alle Daten in das Template Dokument geschrieben, dann in eine PDF umgewandelt und am Ende mit der Funktion „sendEmail()“ an die E-Mailadressen versandt.

Das Aussehen der E-Mail kann man mittels HTML Datei im Skripteditor anpassen. Dazu habe ich die Datei „email-template.html“ mit folgendem Inhalt erstellt:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    
    <p>Hallo <?= vorname ?> <?= nachname ?>,</p>
    <p>im Anhang befindet sich die Fortbildungsbestätigung zur Fortbildung: <?= fortbildung ?> </p>
    <p>Viele Grüße</p>
    <br>
    <div>
      <span>Richard Scheglmann, Beratungsrektor</span><br>
      <span>Informationstechnischer Berater digitale Bildung (Schulamt Landkreis München)</span><br><br>
      <span>+49 (0)176 600 20 787</span><br>
      <span><a href="mailto::r.scheglmann@msush.de">r.scheglmann@msush.de</a></span><br>    
      <span><a href="https://www.unsere-schule.org">www.unsere-schule.org</a></span><br>
      <span style="color:#7DB545; font-size:0.8em;">Think before you print.</span>
    </div>
    
  </body>
</html>

In der Funktion „sendEmail()“ wird dieses email-template geladen und auch mit passenden Daten (Vorname, Nachname, Titel der Fortbildung) befüllt und dann versendet.

Die gesendete Email sieht so aus:

Die Fortbildungsbestätigung im PDF Format sieht am Ende dann so aus:

Sollten Sie weitere Fragen haben, können Sie mir gerne eine Mail an r.scheglmann@gmail.com schreiben.

Links

• Google Apps Script Homepage