22 juni 2013

Voeg velden uit een formulier samen in een PDF document en verstuur dat als E-mail

Het is een vreemde lange titel voor een bericht, maar ik kan hem eigenlijk niet korter maken om uit te leggen waar dit over gaat. Al eerder ben ik bezig geweest met Google Scripts om ervoor te zorgen dat iemand die een Google formulier invult een mailtje krijgt met een kopie van de gegevens die zijn ingevuld. Nu heb ik een script ontdekt dat nog een stapje verder gaat!

De basis vormt een Google template document met een tekst zoals jij hem wilt en een aantal variabelen op de plaats waarvan de gegevens uit het formulier gaan komen. Google Script zorgt ervoor dat er van het template een tijdelijk document wordt gemaakt en vult de variabelen met de door de gebruiker ingevulde gegevens. Daarna wordt hier een PDF van gemaakt en deze wordt als bijlage van een mailtje verstuurd naar de invuller en zo je wilt naar jezelf en/of nog iemand anders. Daarnaast kun je nog een tweede (PDF) document als bijlage toevoegen!

Stel dat iemand zich opgeeft voor een conferentie, dan kan hij dus een PDF ontvangen met zijn aanmeldgegevens. Daarnaast kun je dan nog een ander document meesturen met bijvoorbeeld het programma van de conferentie of andere zaken. Zo zijn er allerlei voorbeelden te bedenken!

Update 7-12-2015:
Inmiddels is er de add-on autocrat die dit hele proces veel gemakkelijker maakt zonder zelf in een script te hoeven duiken. Lees daarvoor dit bericht.
Als voorbeeld heb ik dit formulier gemaakt met om het simpel te houden slechts een paar velden. Daarnaast heb ik dit template gemaakt. De velden die gevuld worden vanuit het formulier herken je doordat er "key" voor staat. Ik heb er een plaatje ingeplakt om daarmee aan te geven dat je het template volledig naar je eigen huisstijl kunt aanpassen. Ook de tekst kan veel langer zijn en eventueel meerdere pagina's bevatten. Tenslotte nog een tweede document dat ook van alles kan bevatten uiteraard.

En dan natuurlijk het Google Script dat ervoor zorgt dat dit allemaal gaat werken! Het oorspronkelijke script heb ik hier en daar vertaald en aangepast zodat je een tweede document kunt meesturen en met nog wat extra's zoals copy, carboncopy, naam afzender en E-mail adres afzender.

Dat ziet er zo uit:

// Oorspronkelijk idee: TJ Houston tjhouston.com (tj@tjhouston.com)
// Vertaald en uitgebreid door Willem Karssenberg trendmatcher.nl (info@trendmatcher.nl)
// Inhoud van formulier via template als PDF attachment per mail versturen

function onFormSubmit(e) 
{
// Het ID van de template ophalen en een naam geven
var docTemplate = "17dgIUBWN6xyVefAMzg1QArCvho8I8giU7ByYCywbSf0";
var docName = "Aanmeldingsformulier";

// Gegevens uit het formulier die moeten worden overgenomen
var naam = e.values[1];
var organisatie = e.values[2];
var mailadres = e.values[3];
var aanwezig = e.values[4];
var opmerkingen = e.values[5];

// Onderwerpregel voor het te sturen mailtje
var subject = "Aanmeldingsformulier";

// Dit komt in het mailtje te staan
var body = "Beste " + naam + ",<br /><br />Hierbij als bijlage het ingevulde aanmeldingsformulier.<br />Neem het mee en laat het zien bij de ingang.<br /><br />Met vriendelijke groet,<br /><br />de afzender"; 
  
// Optioneel extra velden zoals carbon copy, black carbon copy en aangepaste naam afzender
var carbonCopy = "";
var bcarbonCopy = "";
var naamAfzender = "Afzender";
var emailAfzender = "";

// ID van het tweede document dat meegestuurd wordt
var docTemplate2 = "1ICir1lx9o3Y3KFPWOuenjKUiA62G6JdbMw0jFR6mDmI";
var pdf2 = DriveApp.getFileById(docTemplate2).getAs("application/pdf");
  
// Haal het template op, maak er een kopie van en bewaar daarvan het id
var copyId = DriveApp.getFileById(docTemplate)
.makeCopy(docName+' for '+naam)
.getId();

// Open het tijdelijke document
var copyDoc = DocumentApp.openById(copyId);

// Haal de inhoud van het document op
var copyBody = copyDoc.getActiveSection();

// Vervang de variabelen door de velden uit het formulier
copyBody.replaceText('keyNaam', naam);
copyBody.replaceText('keyOrganisatie', organisatie);
copyBody.replaceText('keyMailadres', mailadres);
copyBody.replaceText('keyAanwezig', aanwezig);
copyBody.replaceText('keyOpmerkingen', opmerkingen);

// Tijdelijk document opslaan en sluiten
copyDoc.saveAndClose();

// Converteer het document naar een PDF bestand
var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");
  
var bestanden = [pdf,pdf2]

// Verstuur het mailtje en voeg de PDF bestanden toe
MailApp.sendEmail(mailadres, subject, body, {
  cc: carbonCopy,
  bcc: bcarbonCopy,
  name: naamAfzender,
  replyTo: emailAfzender,
  htmlBody: body,
  attachments: bestanden
});

// Verwijder het tijdelijke bestand
DriveApp.getFileById(copyId).setTrashed(true);
}
De commentregels leggen uit wat er stap voor stap gebeurt. Helemaal aan het eind zie je dat het tijdelijke bestand ook weer verwijderd wordt. Je zou ervoor kunnen kiezen om dat niet te doen, maar hou er dan wel rekening mee dat je Google Drive na verloop van tijd vol loopt met dit soort PDF bestanden.

Om dit werkend te krijgen in je spreadsheet moet je tegenwoordig ook een trigger instellen in de ScriptEditor van Google. Als je nu niet weet waar ik het over heb kun je hier beter niet aan beginnen of even iemand om ondersteuning vragen. Wanneer er veel vraag naar is kan ik er een screencast van maken, maar ik kan helaas niet op iedere individuele (maatwerk)vraag ingaan. Zie je nog andere gebruikstoepassingen, dan hoor ik dat graag in de comments!

Wil je kijken wat je terugkrijgt als je mijn formulier invult? Ga je gang en test het uit!

Update 28-6-2015:
Ik heb iets moeten aanpassen aan het script omdat de opdracht DocsList door Google deprecated is.
Ik heb het vervangen door DriveApp en nu werkt de routine weer...

Update 7-12-2015:
Inmiddels is er de add-on autocrat die dit hele proces veel gemakkelijker maakt zonder zelf in een script te hoeven duiken. Lees daarvoor dit bericht.

8 opmerkingen:

  1. Mooi Willem,
    Werkt perfect :)
    Groet,
    Carry

    BeantwoordenVerwijderen
  2. Top, scheelt weer tussenstappen voor eindgebruiker. Voor een zeker niet nader te benoemen 28ste conferentie, onlangs ook weer gemerkt. ;)
    De bevestigingsmail 'print' ik dan naar PDF (en die sla ik weer op in Skydrive, voor toegang op phone/tablet). Dan zou ik dat printen naar PDF niet hoeven doen.

    BeantwoordenVerwijderen
  3. @Joël,
    Klopt helemaal. Wat ik gevonden heb gaan we dus ook zeker meenemen de volgende keer bij saMBO-ICT!

    BeantwoordenVerwijderen
  4. Beste Willem,

    Een mooie script die ik graag wil gebruiken. Ik heb het script gekopieerd en een test formulier en document gemaakt met 2 vragen (naam en email) nu krijg ik in regel 12 een foutmelding

    "TypeError: Kan eigenschap 'values' niet lezen uit undefined. (regel 12, bestand 'Code')".

    graag uw hulp

    BeantwoordenVerwijderen
  5. Hoi Willem,

    Kan het kloppen dat deze script niet meer werkt met de vernieuwde versie van Spreadsheet?

    BeantwoordenVerwijderen
  6. Beste,

    De gegevens komen niet in de template die ik aangemaakt heb. Ik krijg in de pdf die in de mail zit dan de volgende tekst:
    Naam: ‘keynaam’
    Organisatie: ‘keyorganisatie’
    E-mail adres: ‘keymailadres’
    Aanwezig op: ‘keyaanwezig’
    Hier zou dus eigenlijk de ingevulde informatie moeten staan... Wat doe ik verkeerd...
    Script is als volgt:

    // Oorspronkelijk idee: TJ Houston tjhouston.com (tj@tjhouston.com)
    // Vertaald en uitgebreid door Willem Karssenberg trendmatcher.nl (info@trendmatcher.nl)
    // Inhoud van formulier via template als PDF attachment per mail versturen
    function onFormSubmit(e)
    {
    // Het ID van de template ophalen en een naam geven
    var docTemplate = "13BIsPIKAA4DdkjHVK7vVglhTUAFzCTZDXP4IY2o1V1M";
    var docName = "test";
    // Gegevens uit het formulier die moeten worden overgenomen
    var naam = e.values[1];
    var organisatie = e.values[2];
    var mailadres = e.values[3];
    var aanwezig = e.values[4];
    var opmerkingen = e.values[5];
    // Onderwerpregel voor het te sturen mailtje
    var subject = "Test";
    // Dit komt in het mailtje te staan
    var body = "Beste " + naam + ",

    Hierbij een overzicht van de door u bestelde materialen.
    Voor wijzigingen graag binnen 24 uur een nieuw formulier invullen, de oude zal dan overschreven worden!

    Hopende u hiermee voldoende geïnformeerd te hebben.

    Met vriendelijke groet,

    Erwin";

    // Optioneel extra velden zoals carbon copy, black carbon copy en aangepaste naam afzender
    var carbonCopy = "";
    var bcarbonCopy = "";
    var naamAfzender = "Erwin";
    var emailAfzender = "";
    // ID van het tweede document dat meegestuurd wordt
    var docTemplate2 = "1ICir1lx9o3Y3KFPWOuenjKUiA62G6JdbMw0jFR6mDmI";
    var pdf2 = DocsList.getFileById(docTemplate2).getAs("application/pdf");

    var docTemplate3 = "1ICir1lx9o3Y3KFPWOuenjKUiA62G6JdbMw0jFR6mDmI";
    var pdf3 = DocsList.getFileById(docTemplate3).getAs("application/pdf");

    // Haal het template op, maak er een kopie van en bewaar daarvan het id
    var copyId = DocsList.getFileById(docTemplate)
    .makeCopy(docName+' for '+naam)
    .getId();
    // Open het tijdelijke document
    var copyDoc = DocumentApp.openById(copyId);
    // Haal de inhoud van het document op
    var copyBody = copyDoc.getActiveSection();
    // Vervang de variabelen door de velden uit het formulier
    copyBody.replaceText('keyNaam', naam);
    copyBody.replaceText('keyOrganisatie', organisatie);
    copyBody.replaceText('keyMailadres', mailadres);
    copyBody.replaceText('keyAanwezig', aanwezig);
    copyBody.replaceText('keyOpmerkingen', opmerkingen);
    // Tijdelijk document opslaan en sluiten
    copyDoc.saveAndClose();
    // Converteer het document naar een PDF bestand
    var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

    var bestanden = [pdf,pdf2,pdf3]
    // Verstuur het mailtje en voeg de PDF bestanden toe
    MailApp.sendEmail(mailadres, subject, body, {
    cc: carbonCopy,
    bcc: bcarbonCopy,
    name: naamAfzender,
    replyTo: emailAfzender,
    htmlBody: body,
    attachments: bestanden
    });
    // Verwijder het tijdelijke bestand
    DocsList.getFileById(copyId).setTrashed(false);
    }

    Het zou geweldig zijn als de script probleemloos werkt :-)

    groet, Erwin

    BeantwoordenVerwijderen
  7. wie kan me helpen? Ik krijg deze melding:
    TypeError: Kan eigenschap 'values' niet lezen uit undefined. (regel 8, bestand '')


    BeantwoordenVerwijderen
  8. @mc de B
    Inmiddels is er de add-on autocrat die dit hele proces veel gemakkelijker maakt zonder zelf in een script te hoeven duiken.

    BeantwoordenVerwijderen