Java-Script: Google Tabellen - Email Reminder

[InhaltDerNacht]

Hey zusammen,

ich bin leider was "Programmieren" angeht eine absolute Null, benötige aber eine kleine Hilfestellung für einen E-Mail-Reminder in JavaScript für Google-Tabellen. (Das Addon "Add Reminder" funktioniert leider nicht für eine schon bestehende Tabelle/Blatt).

Ich führe eine Google-Tabelle in welcher ich alle Verträge aufgeführt habe, das sind um die 2.000 Stück. Diese sind mit einer individuellen ID, Name des Vertragsnehmer, Datum Unterschrift bzw. Beginn der Laufzeit, Enddatum, Art und einigen mehr Punkten in Spalten unterteilt. Durch den Umfang würde ich gerne am Ende eine Spalte "Gekündigt zum" einfügen, wo ich dann nur noch ein Datum (gerne mit Uhrzeit) angebe und dass dann zu diesem Zeitpunkt eine E-Mail versendet wird an einen Emailverteiler zur Erinnerung, dass der Vertrag gekündigt und jetzt aufgelöst werden muss. E-Mail würde ausreichen, dass drin steht ""ID des Vertrages" bitte auflösen, da gekündigt"

Ich habe verschiedene Scripte gefunden und eines wohl gefunden, dass zu meinen Anforderungen passen würde, allerdings scheitert es am eintragen, zumindest funktioniert es leider nicht.


Die IDs der Verträge steht in "A2:A1987", das Kündigungsdatum in "BI2:BI1987".



"function sendEmail() {
try{
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow()-1; // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();

for (i in data) {
var row = data;
//Logger.log(sheetDate);
var Sdate = Utilities.formatDate(new Date(),'GMT-0500','yyyy:MM:dd')
var SsheetDate = Utilities.formatDate(new Date(row[1]),'GMT+0200', 'yyyy:MM:dd')
Logger.log(Sdate+' =? '+SsheetDate)
if (Sdate == SsheetDate){
var emailAddress = row[0]; // First column
var message = row[1]; // Second column
var subject = "It's time to practice!" +message;
MailApp.sendEmail(emailAddress, subject, message);
//Logger.log('SENT :'+emailAddress+' '+subject+' '+message)
}
}
}catch(err){
Logger.log(err.lineNumber + ' - ' + err);
}
}"


Kann mir jemand bitte netterweise weiterhelfen, wie ich das hier eingetragen bekommen, damit es auch korrekt funktioniert? An diesem Schritt hapert es leider schon...

Vielen, vielen Dank im Voraus. Lade den/die Helfer gerne auf eine Runde Bier in Berlin ein

Viele Grüße
Jay

 

[Alkor35]

Hallo,

erstelle mal bitte eine Beispielzeile (es müssen keine echt Daten sein, nur das Format ist wichtig), da es viele verschiedene Formate gibt

z.B.

ID	Datum	Unterschrift	Gekündigt am
12546-1	2016-12-1	Andre	07.12.2016 11:30
123	01-12-2016	Andre	07.12.2016 11_30

Gruß
André

 

[InhaltDerNacht]

Hey André,

wenn es für dich einfacher ist, kann ich dich auch gerne zu einer Beispiel-Tabelle einladen, vielleicht ist das einfacher für dich? Benötige dann nur eine E-Mail-Adresse von dir.


Ich habe dir mal einen Screenshot einer gekürzten Tabelle beigefügt (habe die Google Tabelle mal in Excel gezogen). Wichtig ist eben nur, dass eine E-Mail an den Verteiler zum Zeitpunkt "Kündigungsdatum" in Spalte AA mit der Vertrag ID aus Spalte "B" mit der Message "Bitte auflösen" o.ä. versendet wird. Wenn es einfacher ist, reicht es natürlich auch vollkommen aus, wenn das Datum erreicht wurde, dass dann eine Mail um 0:00 Uhr versendet wird, das wäre überhaupt kein Problem.

In der richtigen Tabelle steht das Kündigungsdatum unter der Spalte "BI".




Vielen, vielen Dank im Voraus!

 

[Alkor35]

Hallo Jay,

leider kann ich Dir nicht sagen, wie dieser Script Zeit gesteuert aufgerufen werden kann. Aber zum manuelle sollte es klappen.
Unter "Tools\Script Editor" folgenden Text eintragen:

function myFunction()
{
var emailAddress = "andre@test.de" //Hier die Email Adresse eintragen
var TabelleID = 1; //Spaltennr Eintragen von "Vertrag ID" A = 0, B = 1, C = 2 usw.
var TabelleGekuendigAm = 26; //Spaltennr von "Kündigungsdatum"
var TabelleMailVersendet = 3; //Bitte noch ein Zusätzliches Feld für "Email versendet"

var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var numRows = sheet.getLastRow()-1;
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
var data = dataRange.getValues();
for (i in data)
{
var row = data;
var Sdate = Utilities.formatDate(new Date(),'GMT+0100','yyMMddhhmm')
var GekuendigAm = row[TabelleGekuendigAm];
if (GekuendigAm == "")
{
GekuendigAm = "31.12.9999 23:59";
}
var Jahr = "00" + GekuendigAm.substring(6,10);
var Monat = "00" + GekuendigAm.substring(3,5);
var Tag = "00" + GekuendigAm.substring(0,2);
var Stunde = "00" + GekuendigAm.substring(12,14);
var Minute = "00" + GekuendigAm.substring(15,17);
Jahr = Jahr.substring(Jahr.length-2);
Monat = Monat.substring(Monat.length-2);
Tag = Tag.substring(Tag.length-2);
Stunde = Stunde.substring(Stunde.length-2);
Minute = Minute.substring(Minute.length-2);
var Vergleichsdatum = Jahr + "" +Monat + "" + Tag + "" + Stunde + "" + Minute;
if (Sdate >= Vergleichsdatum && row[TabelleMailVersendet] != "Ja")
{
var subject = "Vertrag Kündigen mit der ID: " + row[TabelleID];
var message = "ID des Vertrages "" + row[TabelleID] + "" bitte auflösen, da gekündigt.";
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + (i * 1), TabelleMailVersendet + 1).setValue("Ja");
SpreadsheetApp.flush();
}
}
}



WICHTIG
Die Datum Felder müssen immer wie in Deinem Beispiel formatiert sein.
Wenn ein Email versendet wurde, trägt er in der Spalte "TabelleMailVersendet" ein "Ja" ein, wenn "Ja" drin steht, wird keine weitere Email versendet.


Nach dem Einfügen den Pfeil nach rechts drücken und die Berechtigung bestätigen.


PS: Gerade noch was gefunden

Unter "Resources\Current projekts triggers" kannst Du einen Timer hinter legen

 

[InhaltDerNacht]

Genial, vielen, vielen Dank dafür und vorallem für deine Mühe! Ich werde das nachher gleich mal testen!

Eine Frage:

In der Zeile 36 meckert er, dass ein ";"-Zeichen vor der Anweisung fehlt, weißt du wo ich das einsetzen muss?

var message = "ID des Vertrages "" + row[TabelleID] + "" bitte auflösen, da gekündigt.";

 

[Alkor35]

ach, Computerbase hat das eine Zeichen interpretiert

in der Zeile gibt es 2x ein "" beim ersten muss ein Backsplash (das Zeichen, was von links oben nach rechts unten geht) und hinten vor dem zweiten". Hier mal ein Scrrenshot



Der Script versendet pro Vertrag eine Mail, falls nur eine Email pro Durchlauf gewünscht ist, sag kurz bescheid.

 

[InhaltDerNacht]

Perfekt, vielen Dank!

Jetzt spuckt er aber einen anderen Fehler aus und zwar habe ich gerade als Test ein Datum eingegeben um die Uhrzeit und er gibt folgenden Fehler
aus in der Zeile 22: "TypeError: Funktion substring in Objekt Thu Dec 08 2016 09:40:00 GMT+0100 (MEZ) nicht gefunden (Zeile 22, Datei "Code")"

var Jahr = "00" + GekuendigAm.substring(6,10);


Habe den Eintrag mal als Screenshot beigefügt, weißt du, was hier gefordert ist?

 

[Alkor35]

In meiner Liste, war es kein Datumsfeld sondern ein String,

hier der Code für ein Datumsfeld und nicht vergessen Zeile 37 wieder anpassen

function myFunction()
{
var emailAddress = "andre@test.de" //Hier die Email Adresse eintragen
var TabelleID = 1; //Spaltennr Eintragen von "Vertrag ID" A = 0, B = 1, C = 2 usw.
var TabelleGekuendigAm = 26; //Spaltennr von "Kündigungsdatum"
var TabelleMailVersendet = 3; //Bitte noch ein Zusätzliches Feld für "Email versendet"

var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var numRows = sheet.getLastRow()-1;
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
var data = dataRange.getValues();
for (i in data)
{
var row = data;
var Sdate = Utilities.formatDate(new Date(),'GMT+0100','yyyyMMddhhmm')
var GekuendigAm = row[TabelleGekuendigAm];
if (GekuendigAm == undefined)
{
GekuendigAm = "31.12.9999 23:59";
}
var Jahr = GekuendigAm.getFullYear();
var Monat = "00" + (GekuendigAm.getMonth() + 1);
var Tag = "00" + GekuendigAm.getDate();
var Stunde = "00" + GekuendigAm.getHours();
var Minute = "00" + GekuendigAm.getMinutes();

Monat = Monat.substring(Monat.length-2);
Tag = Tag.substring(Tag.length-2);
Stunde = Stunde.substring(Stunde.length-2);
Minute = Minute.substring(Minute.length-2);
var Vergleichsdatum = Jahr + "" +Monat + "" + Tag + "" + Stunde + "" + Minute;
if (Sdate >= Vergleichsdatum && row[TabelleMailVersendet] != "Ja")
{
var subject = "Vertrag Kündigen mit der ID: " + row[TabelleID];
var message = "ID des Vertrages "" + row[TabelleID] + "" bitte auflösen, da gekündigt.";
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + (i * 1), TabelleMailVersendet + 1).setValue("Ja");
SpreadsheetApp.flush();
}
}
}

 

[InhaltDerNacht]

Vielen Dank! Leider kommt jetzt in Zeile 22 folgender Fehler:

TypeError: Funktion getFullYear in Objekt nicht gefunden (Zeile 22, Datei "Code")

Zeile 22: var Jahr = GekuendigAm.getFullYear();


//Edit: Habe mal die Zeile mit dem Jahr ganz entfernt, dann meckert er am Monat rum mit dem gleichen Fehler "...in Objekt nicht gefunden"