Im beruflichen und privaten Umfeld nutze ich viele Produkte aus dem Google Workspace. Für den Digitaltag 2021 habe ich zum ersten Mal Google Apps Script verwendet, um die Funktionalitäten von Google Sheets zu erweitern für die Durchführung einer Open Data Rallye durchgeführt von Code for Bielefeld. Alle Infos dazu findet ihr auch in unserem Github Repository dazu.

Google Apps Script ist eine Skripting-Plattform basierend auf Javascript, die über den Google Account im gdrive zugänglich ist. Es ist ein Low Code Ansatz, um Erweiterungen zu ermöglichen, ohne auf professionelle Entwickler angewiesen zu sein.

In der Anwendung für den Digitaltag 2021 habe ich die folgenden Funktionen genutzt, die bei einem kleinen Datenprojekt hilfreich sein können:

Event- oder Zeitgetriggert automatisiert Daten in Google Sheets zusammenzuführen

Dafür wird im Google Drive eine neue Google Table angelegt, in der Daten zusammengeführt werden sollen. Hier waren es Quiz-Antworten die über Google Forms abgegeben worden sind. Unsere einzelnen Stationen waren QR Codes, die auf das jeweilige Quiz-Google Forms Formular gezeigt hatte. Über Tools -> Skripteditor kann dann ein Google Apps Script eingefügt werden, dass alle Antworten aus den anderen Tabellen zusammenführt.

// Daten aus Tabelle, Tabelle wird mit ihrer URL angegeben
function get_data(url){
    // Tabelle
	var sXApp = SpreadsheetApp.openByUrl(url);
    // Tabellenblatt ansprechen
	var sXSheet = sXApp.getSheetByName("Formularantworten 1");
    // Werte von dem Tabellenblatt erste bis vierte Spalte, erste bis letzte Zeile
	var sXvalues = sXSheet.getRange(1,1,sXSheet.getLastRow(),4).getValues();
        return(sXvalues)
}

function combineSheets() {
  // Daten werden in aktueller Tabelle, in der das Google Apps Script ausgeführt wird, zusammengeführt
  // daher hier das ActiveSpreadsheet holen	
  var mApp= SpreadsheetApp.getActiveSpreadsheet();
  
  // die jeweiligen Tabellenblaetter, in denen Daten eingefuegt werden sollen, verfuegbar machen
  var m1= mApp.getSheetByName("Station 1");
  var m2= mApp.getSheetByName("Station 2");
  
  
  // get data mit oben definierter Funktion holen
  s1values=get_data("https://docs.google.com/spreadsheets/XXX")#url angeben
  s2values=get_data("https://docs.google.com/spreadsheets/XXX")#url angeben
  
 // set values
  m1.getRange(1,1,s1values.length,4).setValues(s1values);
  m2.getRange(1,1,s2values.length,4).setValues(s2values);
}

Um das Datenzusammenführen zeitlich oder Event-basiert zu triggern, stehen in den Google Apps Script Trigger zur verfügung, die je nach Bedarf hinzugefügt werden können. Im Rahmen des Digitaltags wurde ein zeitlicher Trigger gewählt, um regelmäßig auf neue Teilnehmer der Open Data Rallye zu prüfen und bei Abschluss der Open Data Rallye eine Erfolgs-Email zu verschicken.

Damit das Google Apps Script ausgeführt werden kann, ist einmal eine Autorisierung erforderlich, damit das Skript auf die Daten zugreifen darf.

Versenden E-Mails aus Google Sheets heraus

Das versenden der E-Mails basierend auf Daten aus Google Sheets wurde in einem eigenen Google Apps Script-Projekt im gdrive separat angelegt. Dort konnte dann Code angelegt werden für die Ausführung und die E-Mail als html Dokument. Es wurden verschiedene Bedingungen geprüft, bevor eine E-Mail versendet wurde, z.B. auch, ob bereits eine E-Mail an die jeweilige Adresse gesendet worden ist. Um das Versenden der Mails dafür zu dokumentieren, musste mit Google App Scripts auch in Tabelle geschrieben und nicht nur gelesen werden. Das gelang mit setValue()-Methoden, z.B.:

// Funktion um E-Mail Versand zu dokumentieren
function document_email(email){
  // mit URL die Tabelle angeben
  var ss_email = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/XXX");
  //das Tabellenblatt aufrufen
  var sent_email = ss_email.getSheetByName("Tabellenblatt1");
  
  // Range of cells - dafür zuerst die letzte Zeile mit Inhalt bestimmen
  var last_row = sent_email.getLastRow()
  // Eine Zelle weiter gehen, um die erste leere Zelle zu erhalten
  last_row = last_row + 1
  last_row = String(last_row)
  
  // in Spalte A wird die Email stehen
  var range_email = sent_email.getRange("A" + last_row);
  // in Spalte B wird der Zeitstempel stehen
  var range_timestamp = sent_email.getRange("B" + last_row);
  
  // Aktuellen timestamp 
  var now = new Date();
  
  // Aufruf setValues method in der oben definierten Range + Übergabe der Werte Email+Timestamp
  range_email.setValue(email);
  range_timestamp.setValue(now);
}

Das Versenden der Emails erfolgt dann mit folgenden Code-Zeilen. Dabei baut die erste Funktion die HTML-Email mit personalisierter Punktzahl zusammen. Die Email-Vorlage liegt in Google Apps Script als email.html vor. Die Variable punkte wird dabei mit dem Platzhalter %punkte im HTML-Code ersetzt. Die zweite Funktion versendet die E-Mail an die mitgegebene E-Mail Adresse mit dem Betreff „Deine Teilnahme an der Open Data Ralley // Digitaltag 2021“. Auch hier kann ein zeitlicher oder eventgetriebener Trigger hinzugefügt werden. 

// Function to build html body for the email
function getMessage(punkte) {
  var htmlOutput = HtmlService.createHtmlOutputFromFile('email'); // email is the name of the HTML file
  var message = htmlOutput.getContent();
  // insert Punktezahl into message body
  //message = message.replace("%punkte", punkte);
  return message;
}

// Function to send the email with result
function send_email(email, punkte) {
  var subject = "Deine Teilnahme an der Open Data Ralley // Digitaltag 2021";
  var htmlbody = getMessage(punkte);
  MailApp.sendEmail(email, subject, htmlbody, {htmlBody : htmlbody});
}