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

Een uiterst krachtige functie van GAM is het genereren van afspraken in de agenda van een gebruiker. Ik gebruik dit binnen onze school o.a. om lessen afstandsonderwijs en oudercontacten in te plannen

Afspraken met gasten hebben ook Google Meet!

Dat is dé krachtpatser van GAM voor Google Calendar. Niet alleen krijgen de gasten die je aan een event koppelt een e-mail, maar ze worden ook automatisch uitgenodigd voor een unieke Google Meet. Bijkomend voordeel is dat jij de eigenaar van het item bepaalt. Die heeft dus ook de volledige controle over de gegenereerde Google Meet (micro muren, externen toelaten, stoorzenders verwijderen, opnames maken, …)

De vereiste parameters:
– calendar het e-mailadres van de host
– addevent maak een event aan
– summary hoe wil je dat het event heet?
– location waar vindt het event plaats?
– description beschrijving van het event
– attendee wie wil je uitnodigen (individueel)?
– notifyattendees verstuur een uitnodiging via mail
– hangoutsmeet voeg een Meet toe
– start en end begin- en einddatum en uur.

UTC en RFC3339

De klok van je Google Cloud Shell staat in UTC. Die loopt dus (vandaag in wintertijd alvast) 1u achter op Belgische tijd. Je kan dit makkelijk checken in je terminal door even het commando date uit te voeren. Je ziet dan “hoe laat het in je Cloud is”.
Het vereiste datumformaat in GAM is RFC3339. Het ziet er als volgt uit:

YYYY-MM-DDThh:mm:ss.000Z

3 minuten na middag op 1 december 2020 wordt dus: 2020-12-01T12:03:00.000Z
Hou rekening met Belgische tijd vs. UTC, dus het wordt dan: 2020-12-01T13:03:00.000Z

Samengevat: Maak een afspraak in de agenda van John en nodig de andere Beatles uit om 16u in de namiddag van 3 december 2020 wordt in GAM:

gam calendar john.lennon@uwdomein.com addevent summary "Eénmalige reünie Beatles" location "Saville Row, London, UK" description "Breng a.u.b. je instrument mee voor dit event" attendee George.harrison@uwdomein.com attendee ringo.starr@uwdomein.com attendee Paul.mccartney@uwdomein.com notifyattendees hangoutsmeet start 2020-12-03T17:00:00.000Z end 2020-12-03T20:00:00.000Z

Je leest dit als:

gam calendar john.lennon@uwdomein.com addevent Maak een event in de agenda van John
summary "Eénmalige reünie Beatles" Noem het Eénmalige reünie Beatles
location "Saville Row, London, UK" Stel de locatie in op Savile Row in London (link naar Google Maps wordt gemaakt!)
description "Breng a.u.b. je instrument mee voor dit event" Voeg ook een notitie toe
attendee George.harrison@uwdomein.com attendee ringo.starr@uwdomein.com attendee Paul.mccartney@uwdomein.com notifyattendees Nodig gasten uit en stuur ze een e-mail
hangoutsmeet Genereer een unieke code voor Google Meet
start 2020-12-03T17:00:00.000Z end 2020-12-03T20:00:00.000Z Definieer start en einde van de afspraak (beide zijn vereist!)

 

Oudercontacten?

In een volgende tutorial toon ik hoe je met een Google Form ouders kan laten intekenen voor een oudercontact en met bulk operaties de afspraken mét unieke Google Meet-codes kan aanmaken voor leerkrachten en ouders. Uiteraard met e-mails en herinneringen.

Niemand doet graag “papegaaienwerk”

Niks frustrerender dan repetitieve taken doen als IT’er. In de admin-console van je Google-omgeving kan je werken met spreadsheets, maar dat kan je dus ook met GAM. In deze tutorial zullen we in navolging van de vorige “bulkoperaties” uitvoeren. Dat wil zeggen dat je met één commando meerdere acties onderneemt.

Werken met een csv-bestand

Csv staat voor “Comma Separated Values”. Dit is het makkelijkst te vergelijken met een werkblad van een spreadsheet: de rijen van de spreadsheet worden regels in de csv en de kolommen worden ipv door lijnen gescheiden door een komma.
De eerste lijn bevat de kolomnamen.
Een csv-bestand van de 3 resterende Beatles zou er dan als volgt uitzien:

e-mail,firstname,lastname,OU
paul.mccartney@uwdomein.be,Paul,McCartney,Beatles
george.harrison@uwdomein.be,George,Harrison,Beatles
ringo.starr@uwdomein.be,Ringo,Starr,Beatles

 

Gebruikers aanmaken in bulk

GAM CREATE USER

We gaan bovenstaande csv gebruiken om de 3 resterende Beatles in GAM aan te maken en in de juiste OU te stoppen.
Het commando om een csv te gebruiken in GAM is:

gam csv <bestandsnaam> gam <commando>

Ik prefereer om de csv in de Shell Editor te plaatsen. Maak een nieuwe file en noem hem beatles.csv
Bekijk hier hoe je dat doet:

De code die we gaan gebruiken, zal het volgende doen:

Open de csv genaamd beatles.csv en doe met elke lijn het volgende:
Maak de gebruiker aan met e-mail, voornaam en achternaam. Die informatie staat in de volgorde e-mail, firstname, lastname

Door in het commando met een ~ te werken, weet GAM dat wat na de ~ komt, overeenstemt met de ‘kolomnaam’ uit de eerste lijn.

De code wordt dus:

gam csv beatles.csv gam create user ~e-mail firstname ~firstname lastname ~lastname

Proberen maar!

Wil je checken of de gebruikers aangemaakt zijn? Dat kan ook met GAM. Gebruik het commando:

gam print users todrive

In jouw Google Drive wordt een spreadsheet aangemaakt met alle gebruikers. De link wordt in je terminal getoond.

GAM UPDATE USER

De gebruikers zijn nu aangemaakt. We gaan ze nu in de juiste OU stoppen.
We gebruiken daarvoor dezelfde csv-file

gam csv beatles.csv gam update org ~ou move users ~e-mail

Opletten met spaties!

De Beatles hebben gelukkig allen namen zonder spaties, maar in de echte wereld ga je natuurlijk wel namen hebben met spaties. Rondom die termen zet je aanhalingstekens.

gam create user pieter.van.den.broecke@uwdomein.com firstname Pieter lastname "Van Den Broecke"

Welke commando’s zijn beschikbaar?

Op de GitHub-pagina van GAM staat de volledige documentatie, maar persoonlijk verkies is de Gam Cheat Sheet. Hierin krijg je een overzicht van de meeste commando’s die je kan gebruiken in GAM. We starten met enkele makkelijke.

Een nieuwe gebruiker aanmaken

Het commando gam create user maakt een nieuwe gebruiker aan. Uiteraard moet je de nodige parameters meegeven. Enkel het e-mailadres is verplicht. Andere kan je in de adminconsole later toevoegen of via GAM met het commando gam update user
In de Cheat Sheet vind je dit terug:

De user is het e-mailadres (dat uiteraard binnen jouw domein ligt). Verder geef je bij voorbeeld firstname, lastname, password (eenmalig wachtwoord) als parameters mee. Het volledige commando wordt dan bij voorbeeld

gam create user john.lennon@uwdomein.com firstname John lastname Lennon password Liverp00l

Een organizational unit aanmaken en gebruikers toevoegen

Binnen je Google Directory (de verzameling van al je gebruikers) kan je Organizational Units (OU) aanmaken om je gebruikers te rangschikken. Je kan deze o.a. gebruiken om bepaalde apps en diensten al dan niet toe te staan binnen je organisatie.

Onze gebruiker John Lennon willen we in een OU ‘Beatles’ hebben. Die moet uiteraard nog aangemaakt worden.

gam create org Beatles

Vervolgens verplaatsen we John er naartoe

gam update org Beatles move users john.lennon@uwdomein.com

Volgende stap?

In de volgende tutorial zullen we de andere Beatles in bulk aanmaken in GAM!

Vereisten

1. Een computer met internetverbinding
2. Een Google-account met adminrechten binnen je domein.

Een machine in de cloud

Binnen je Google-domein kan je op cloud.google.com een computer met command line in de cloud gebruiken. Het gebruik van de Shell Editor is bovendien gratis. Je kan deze vinden op https://shell.cloud.google.com

De installatie

Open de Shell Editor en meld aan met jouw Google-account.

Installeer GAM via de command line. Hiervoor gebruik je de terminal in de onderste helft van je scherm. Kopieer onderstaande code en plak in de terminal

bash <(curl -s -S -L https://git.io/install-gam)

Ik ga de installatie hier niet helemaal beschrijven. Je kan ze beter volgen in dit uitstekende filmpje op YouTube. Het plakken en uitvoeren van bovenstaand commando gebeurt op 1:40.
Belangrijk! Volg de instructies in de Shell en de videohandleiding correct. Je zal een aantal keren moeten overschakelen naar een webpagina om permissies te geven. Geef in dit proces niet op als er enkele keren FAIL verschijnt bij het toegang geven aan de API’s.

Test of GAM werkt

Voer volgend commando in de Terminal in:
gam info domain
GAM toont je nu een samenvatting jouw Google-domein