Wie houdt er nu niet van spreadsheets?

Om GAM nog nuttiger te maken, wil je nog straffere dingen in bulk doen. Om dat te bereiken, ga je veel informatie willen gestructureerd aanroepen. Hiervoor kan je in een ideale wereld een database gebruiken. Is die drempel wat hoog of “schiet je daarmee met een mug op een olifant”? Dan is een spreadsheet een mooi compromis.
Enige kennis van functies in een spreadsheet is voor deze tutorial aangewezen. Ik gebruik hier en daar functies zoals =tekst.samenvoegen en =substitueren. Voor de meeste IT-admins mag dat geen probleem vormen.
Bijkomend voordeel van een spreadsheet? Google Forms kan jouw spreadsheet vullen. Denk hierbij aan afspraken inplannen in een agenda, gegenereerde mails op basis van een formulier, …

In deze tutorial zullen we een voorbeeld maken van een oudercontact.
Ouders kunnen registreren voor een oudercontact met de klastitularis en een voorkeursperiode opgeven. Dat is de manier waarop wij op onze school trouwens ook de oudercontacten inplannen: een medewerker van het secretariaat optimaliseert zo de puzzel voor ouders en leerkrachten.
Hier kan je een voorbeeld van zo’n formulier kan je hier zien.

In het formulier vraag je dus naar
– De naam van de leerling
– Welke leerkracht willen ze spreken (meerkeuze)?
– Voorkeursmoment (meerkeuze)?
– Mailadres ouder (om de uitnodiging te versturen

Dit zijn de antwoorden die in het formulier verzameld werden:

Nadat de inschrijvingen afgerond zijn, voeg je een tabblad toe.
Om de hele zaak overzichtelijk te houden voeg je enkele kolommen toe
– 1 nieuwe kolom A (de meest links dus)
– 6 kolommen tussen Voorkeur en MailOuder
1. Dag
2. StartUur
3. StartUurGecorrigeerd
4. EindUurGecorrigeerd
5. GAM_start
6. GAM_eind

Dag

Dit is de datum waarop het oudercontact plaats zal vinden in formaat YYYY-mm-dd (4 karakters voor jaar, 2 voor maand, 2 voor dag)

StartUur

In deze kolom noteert je het uur dat je zal toekennen in formaat hh:mm (2 karakters voor uur, 2 voor minuten in 24-uursindeling)

StartUurGecorrigeerd

Hier vang je het verschil tussen UTC en CET op. De formule is
=[StartUur]-TIJD(0;60;0) Vervang [StartUur] door de cel waarin StartUur staat.

EindUurGecorrigeerd

Bepaal wanneer de afspraak eindigt. Dit is niet erg van belang, de Meet wordt niet ongeldig wanneer de tijd verstreken is. Ik neem meestal 20m. De formule is
=[StartUurGecorrigeerd]+TIJD(0;[duur];0) Vervang [duur] door bv. 20 om een afspraak van 20 minuten te maken staat.

GAM_start

Hierin bundelen we Dag en StartUurGecorrigeerd tot het datumformaat dat GAM lust. Dit is de formule:

=TEKST.SAMENVOEGEN(TEKST([Dag];"YYYY-mm-dd");"T";TEKST([StartUurGecorrigeerd];"hh:mm:ss");".000Z")

Ik kleed even de formule uit:
– We gebruiken TEKST.SAMENVOEGEN omdat we uiteindelijk 1 string (karakters in volgorde) moeten hebben die het beginmoment voorstelt.
– TEKST([Dag];"YYYY-mm-dd")zet de datumnotatie van [Dag] om in platte tekst in het formaat “YYYY-mm-dd”.
– Tussen de dag en het uur moet een letter T staan.
– TEKST([StartUurGecorrigeerd];"h:mm:ss") zet de tijdnotatie van [StartUurGecorrigeerd] om in platte tekst in het formaat “YYYY-mm-dd”.
– Na het beginuur moet .000Z staan.

GAM_eind

Deze wordt naar analogie van GAM_start opgebouwd, maar met EindUurGecorrigeerd.

De eerste kolom

In deze kolom gaan we met TEKST.SAMENVOEGEN het GAM-commando opstellen. Het moet er uiteindelijk als volgt uitzien:

gam calendar [e-mail leerkracht] addevent summary [Titel afspraak] attendee [e-mail ouder] hangoutsmeet notifyattendees start [GAM_start] end [GAM_eind]

In mijn werkblad heb ik de volgende kolommen:
– A: Hier komt de formule voor GAM
– B: Naam leerling
– C: Naam leerkracht
– D: Voorkeur uit formulier
– E: Dag
– F: StartUur
– G: StartUurGecorrigeerd
– H: EindUurGecorrigeerd
– I: GAM_start
– J: GAM_eind
– K: E-mail ouder

Dit is de formule:

=TEKST.SAMENVOEGEN("gam calendar ";TEKST.SAMENVOEGEN(SUBSTITUEREN(C2;" ";"");"@uwdomein.com");" addevent summary ";TEKEN(34);"Oudercontact ";B2;TEKEN(34);" attendee ";L2;" hangoutsmeet notifyattendees start ";J2;" end ";K2)

Even toegelicht:
– Met TEKST.SAMENVOEGEN bekomen we het uiteindelijke GAM-commando. De stukken tekst die samengevoegd dienen te worden, worden gescheiden door een puntkomma. Letterlijke tekst staat tussen dubbele aanhalingstekens.
– TEKST.SAMENVOEGEN(SUBSTITUEREN(C2;" ";"");"@uwdomein.com") maakt van de naam van de leerkracht het e-mailadres van de leerkracht. In dit geval is dat opgebouwd als voornaamnaam@domein.com Worden de e-mailadress opgebouwd als voornaam.naam@, dan wordt de formule TEKST.SAMENVOEGEN(SUBSTITUEREN(C2;" ";".");"@uwdomein.com")
– TEKEN(34) is de formule die je nodig hebt om dubbele aanhalingstekens in een formule op te nemen zonder dat die invloed hebben op de formule. We hebben dat hier nodig omdat er een spatie staat in de titel (summary) van de afspraak tussen “Oudercontact” en de naam van de leerling. We zetten de naam van de leerling in de titel zodat de leerkracht in zijn Google agenda een oplijsting van de leerlingen heeft.

Over naar GAM

Selecteer en kopieer de inhoud van kolom A
Maak een nieuw bestand aan in je Shell Editor (zie Bulk operaties) en noem het oudercontact.csv

Plak het klembord in de file en genereer alle afspraken met volgend commando:

gam batch oudercontact.csv