Serienbrief mit Google AppScript erstellen 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 unsere-schule.org × Serienbrief mit Google AppScript erstellen Code: Infos: unsere-schule Codes