Google Workspace Leerling Sync

Met deze Google Apps Script kan je leerling gegevens uit Somt0day of Parnasys gesynchroniseerd met de Google Workspace for Education omgeving voor het maken en bewerken van leerling accounts. Het gebruik van scripts zijn voor eigen risico, de broncode is vrij te gebruiken onder een GNU General Public License.

De Google Apps Script komt in een Google Drive map staat onder een (admin) gebruiker die voldoende rechten heeft om in de Google Workspace Admin Organisatie-eenheden en gebruikers te bewerken.

Als het CVS bestand in geupload dan worden de gegevens in een JSON formaat opgeslagen in een Google Drive map. Middels een Timed Trigger die is ingesteld wordt het JSON bestand op een later moment verwerkt.

Leerling accounts worden op basis van het leerlingnummer uit Somtoday of Parnasys aangemaakt. bv 123456@schooldomain.nl en is ook een versie waarbij de voorletter.achternaam gebruikt wordt in het mailadres. Maar om de privacy van leerlingen beter te beschermen heeft een leerlingnummer de voorkeur.

In de Google Admin Workspace komen de gebruikers in een vaste Organisatie structuur te staan.

{schooldomain.nl} -> Leerlingen -> {leerjaar} -> {groepnaam}

De leerlingen worden onder een sub organisatie unit te zetten en dan on het het huidige leerjaar en de groep zoals die in Somt0day of Parnasys staat.

BV: schooldomain.nl -> Leerlingen -> 2022-2023 -> 1D

Bij het nieuwe schooljaar worden de bestaande leerlingen verplaatst naar het nieuwe leerjaar en leerlingen die dan nog in het oude leerjaar zitten zijn leerlingen die van school af zijn gegaan. Hiervoor bestaat ook een Google Apps Script die deze leerlingen verplaats naar een Quarantaine organisatie unit waarin de meeste diensten zijn geblokkeerd.

In de onderstaande Google Apps Scripts staan variabelen die moeten worden aangepast naar de inrichting van de van de school. Mocht je ondersteuning willen hebben bij de implementatie kan je me bereiken op het mailadres onderaan deze pagina.

Het gebruik van deze scripts is op volledig eigen risico!
De broncode is vrij te gebruiken onder een GNU General Public License.

Google Apps Script

Hieronder vindt je de broncode van de Google Apps Script.

Code.gs

**

* Sync Google Workspace accounts from a .CSV File.

* Code and design by Jeff Schilders <jeff@schilders.com>

* Birthday of this script: 24 Maart 2011

* YOU NEED TO CHANGE THIS SCRIPT BEFORE YOU CAN USE IT!!!!

*

* Google Directory OU Setup:

* domain/Education/Studens/schoolyear

*

* Input Format: CSV (export from Parnasys or SomToDay)

* Roepnaam, Tussenvoegstel,Achternaam,BSN,Groepnaam,Leerlingnummer

* See / change field array below.

*

* Use studentnumer@var(emaildomain) for privacy legislation.

*

* Contact me <jeff@schilders.com>, if you need help to change this script to the needs of your school,

*

* This program is free software: you can redistribute it and/or modify

* it under the terms of the GNU General Public License as published by

* the Free Software Foundation, either version 3 of the License, or

* any later version.

*

* This program is distributed in the hope that it will be useful,

* but WITHOUT ANY WARRANTY; without even the implied warranty of

* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

* GNU General Public License for more details.

*

* You should have received a copy of the GNU General Public License

* along with this program. If not, see <https://www.gnu.org/licenses/>.

*

**/

var DEBUG = false;

var emailDomain="schooldomain.nl"; // School domain to make accounts

var baseOrgUnit="Root OU Path"; // Root OU path

var defaultpassword="welkom01";

var schooljaar;

var leerlingnummer=-1;

var roepnaam=-1;

var voorvoegsel=-1;

var achternaam=-1;

var stamgroep=-1;

var mailBody="<h2>G-Suite Sync Status Raport:</h2><br/>";

var mailInsert="";

var mailUpdate="";

var mailError="";

var mailOU="";

var FolderId="112rSyamQOEhJNaKtBrPz1V3gOxIYUByu";

var BackupFolderId="1YNTVh2_tPjuuWFHXAbttvTmpQGu74xCP";

function doGet() {

var user = Session.getActiveUser().getEmail();

/** Onderstaande gebruikers mogen uploaden **/

if (user == 'j.schilders@schilders.com' ||

user == 'admin-js@schilders.com' ) {

var template = HtmlService.createTemplateFromFile('Index');

} else {

var template = HtmlService.createTemplateFromFile('noaccess');

}

return template.evaluate()

.setTitle('Sync Accounts')

.setSandboxMode(HtmlService.SandboxMode.IFRAME);

}

function saveFile(e) {

var leerlingnummer = 0;

var json=[];

var controle=0

var Folder = DriveApp.getFolderById(FolderId);

var fileName = "students.json";

var files = Folder.getFilesByName(fileName);

if (files.hasNext()) {

return {error:"Er staat al een bestand klaar !!, probeer het later nog eens"}

}

var fileBlob = Utilities.newBlob(e.bytes, e.mimeType, e.filename);

var filedata=Utilities.newBlob(e.bytes).getDataAsString();

var csvdata = Utilities.parseCsv(filedata, ';');

for (var i = 0; i < csvdata[0].length; i++) {

if (csvdata[0][i] == "Leerlingnummer"){leerlingnummer = i;controle=controle+1;}

if (csvdata[0][i] == "Roepnaam"){roepnaam = i;controle=controle+1;}

if (csvdata[0][i] == "Voorvoegsel"){voorvoegsel = i;controle=controle+1;}

if (csvdata[0][i] == "Achternaam"){achternaam = i;controle=controle+1;}

if (csvdata[0][i] == "Stamgroep"){stamgroep = i;controle=controle+1;}

if (csvdata[0][i] == "Email verzorger 1"){email1 = i;controle=controle+1;}

if (csvdata[0][i] == "Email verzorger 2"){email2 = i;controle=controle+1;}

}

if(leerlingnummer < 0 || roepnaam < 0 || voorvoegsel < 0 || achternaam < 0 || stamgroep < 0 ) {

Logger.log("Wrong format");

Logger.log(csvdata[0][0])

Logger.log(csvdata[0][1])

return {error:'Verkeerde bestandsindeling'};

}

for (var i = 1; i < csvdata.length; i++) {

var groepOU=csvdata[i][stamgroep];

if(groepOU == ''){groepOU="NAN"};

var email = csvdata[i][leerlingnummer]+'@'+emailDomain;

var voornaam = csvdata[i][roepnaam];

if( csvdata[i][voorvoegsel] != '' ){

var famnaam = csvdata[i][voorvoegsel]+' '+csvdata[i][achternaam];

} else {

var famnaam = csvdata[i][achternaam];

}

json.push({nummer:csvdata[i][leerlingnummer],voornaam:voornaam,achternaam:famnaam,email:email,groep:groepOU});

}

var myJSON = JSON.stringify({leerlingen:json});

var Folder = DriveApp.getFolderById(FolderId);

var file = Folder.createFile('students.json',myJSON,MimeType.PLAIN_TEXT);

return {leerlingen:json};

}

/** Run by Timed Trigger **/

function syncAccounts(){

schooljaar = getCurrentSchoolYear_();

Logger.log(schooljaar);

var Folder = DriveApp.getFolderById(FolderId);

var fileName = "students.json";

var files = Folder.getFilesByName(fileName);

if (files.hasNext()) {

var file = files.next();

var content = file.getBlob().getDataAsString();

var data = JSON.parse(content);

} else {

Logger.log('No File');

return;

}

Logger.log(data.leerlingen.length);

if (data.leerlingen.length > 1 ) {

data.leerlingen.forEach(function(item) {

var leerlingnummer = (item.hasOwnProperty("nummer")) ? item.nummer : "unknown";

var roepnaam = (item.hasOwnProperty("voornaam")) ? item.voornaam : "unknown";

var achternaam = (item.hasOwnProperty("achternaam")) ? item.achternaam : "unknown";

var email = (item.hasOwnProperty("email")) ? item.email : "unknown";

var groep = (item.hasOwnProperty("groep")) ? item.groep : "unknown";

var AccountorgUnit=checkOrgUnit_(groep,schooljaar);

Logger.log(AccountorgUnit);

var checkuser=checkUserKey_(email);

Logger.log(checkuser);

if( checkuser == "INSERT"){

var UserParam = {

primaryEmail:email,

name: {

familyName:achternaam,

givenName:roepnaam,

},

password:defaultpassword,

externalIds: [

{

type: "organization",

value:groep,

}

],

organizations: [

{

customType: '',

description: 'leerling',

primary:true

}

],

changePasswordAtNextLogin: true,

orgUnitPath:'/'+AccountorgUnit,

includeInGlobalAddressList: true

};

insertUser_(UserParam,email);

}

if (checkuser == "UPDATE"){

var UserParam = {

primaryEmail:email,

externalIds: [

{

type: "organization",

value:groep,

}

],

changePasswordAtNextLogin: false,

orgUnitPath:'/'+AccountorgUnit

};

updateUser_(UserParam,email);

}

});

mailBody="<b>Update</b><br/>"+mailUpdate+"<br/><b>Insert</b><br/>"+mailInsert+"<br/><b>Error</b><br/>"+mailError+"<br/><b>OU</b><br/>"+mailOU;

MailApp.sendEmail({

to: Session.getActiveUser().getEmail(),

subject: 'GSuite_Sync Status - ' + new Date(),

htmlBody: mailBody,

});

Logger.log('End Script');

}

// Move file to backup

Logger.log(file.getId());

var timeZone = Session.getScriptTimeZone();

var datestr=Utilities.formatDate(new Date(), timeZone, "ddMMyyyyHHmmss");

Logger.log('students'+'-'+datestr+'.json');

file.setName('students'+'-'+datestr+'.json');

moveFiles_(file.getId(),BackupFolderId);

}

function moveFiles_(sourceFileId, targetFolderId) {

var mfile = DriveApp.getFileById(sourceFileId);

mfile.getParents().next().removeFile(mfile);

DriveApp.getFolderById(targetFolderId).addFile(mfile);

}

function getCurrentSchoolYear_(){

var date = new Date();

var mt = date.getMonth();

var ye = date.getFullYear();

if (mt >= 7) { // Aug

var y = ye + 1;

var schoolyear = ye+"-"+y;

} else {

var y = ye - 1;

var schoolyear = y+"-"+ye;

}

var ParentOrg="/"+baseOrgUnit+"/Leerlingen";

var CheckOrgPath=baseOrgUnit+"/Leerlingen/"+schoolyear;

Logger.log(CheckOrgPath);

try {

var groupInfo=AdminDirectory.Orgunits.get('my_customer', CheckOrgPath);

/** OU Bestaat al **/

} catch (e) {

var NewOrgUnit = {

name:schoolyear,

parentOrgUnitPath:ParentOrg

};

try{

if(!DEBUG){

AdminDirectory.Orgunits.insert(NewOrgUnit, "my_customer");

mailOU=mailOU+'Created OU '+ NewOrgUnit+"<br/>";

} else {

Logger.log('Insert OU:'+CheckOrgPath);

}

} catch(e) {

Logger.log('Error nieuwe OU');

CheckOrgPath="error";

}

return schoolyear;

}

function insertUser_(UserParam,email){

Logger.log('Insert '+email);

try {

var user = AdminDirectory.Users.insert(UserParam);

Logger.log('User %s created with ID %s.', user.primaryEmail, user.id);

mailInsert=mailInsert+" Aangemaakt ("+user.primaryEmail+") <br/>";

} catch (e) {

Logger.log('Error:'+e.message);

mailError=mailError+" Insert ("+e.message+")";

}

return;

}

function updateUser_(UserParam,email){

Logger.log('Update '+email);

try {

var user = AdminDirectory.Users.update(UserParam,email);

Logger.log('Update %s with ID %s.', user.primaryEmail, user.id);

mailUpdate=mailUpdate+"Update:("+user.primaryEmail+") <br/>";

} catch (e) {

console.log('Error:'+e.message);

mailError=mailError+" Update ("+e.message+")";

}

return;

}

function checkOrgUnit_(key,schooljaar){

var ParentOrg="/"+baseOrgUnit+"/Leerlingen/"+schooljaar;

var CheckOrgPath=baseOrgUnit+"/Leerlingen/"+schooljaar+"/"+key;

try {

var groupInfo=AdminDirectory.Orgunits.get('my_customer', CheckOrgPath);

/** OU Bestaat al **/

} catch (e) {

/** Nieuw OU **/

var NewOrgUnit = {

name:key,

parentOrgUnitPath:ParentOrg

};

try{

if(!DEBUG){

AdminDirectory.Orgunits.insert(NewOrgUnit, "my_customer");

mailOU=mailOU+'Created OU '+ NewOrgUnit+"<br/>";

} else {

Logger.log('Insert OU:'+CheckOrgPath);

}

} catch(e) {

Logger.log('Error nieuwe OU');

mailError=mailError+" Create OU ("+e.message+")";

CheckOrgPath="error";

}

return CheckOrgPath;

}

function checkUserKey_(email){

Logger.log('Check Key:'+email);

var result="";

var findData='email:'+email;

var dataquery = {

customer:'my_customer',

domain:emailDomain,

query:findData

};

try {

var users=AdminDirectory.Users.list(dataquery);

var email=users.users[0].primaryEmail;

Logger.log('Found '+users.users[0].primaryEmail);

var result="UPDATE";

} catch(e) {

var result="INSERT";

}

return result;

}

Index.html

<!DOCTYPE html>

<html>

<head>

integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">

</head>

<body>

<h3>Sync G-Suite Accounts met SOM V0.7</h3>

<div>

Upload een CSV bestand die uit SOMtoday komt. De indeling van het CSV bestand moet er als volgt uitzien.<br/>

<tr>

<td><b>Leerlingnummer</b>;</td><td><b>Roepnaam</b>;</td>

<td><b>Voorvoegsel</b>;</td><td><b>Achternaam</b>;</td>
<td><b>Stamgroep</b></td>

</tr>

</table>

</div>

<p>

 Opnieuw instellen wachtwoord voor bestaande accounts

</p>

<p>Upload het CSV bestand uit SOM</p>

</div>

De gegevens worden verwerkt.

</div>

</div>

<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<?!= HtmlService.createHtmlOutputFromFile('JavaScript').getContent(); ?>

</body>

</html>

Javascript.html

function saveFile(f) {

$('#mainForm').hide();

$('#spinner').show();

const file = f.files[0];

const fr = new FileReader();

fr.onload = function(e) {

const obj = {

filename: file.name,

mimeType: file.type,

bytes: [...new Int8Array(e.target.result)]

};

google.script.run.withSuccessHandler(uploadResponse).saveFile(obj);

};

fr.readAsArrayBuffer(file);

}

function uploadResponse(e) {

if (e.error) {

$('#spinner').hide();

$('#result').html(e.error);

$('#output').show();

return

} else {

$('#spinner').hide();

$('#result').html("Bestand is geupload en wordt vanavond verwerkt.");

$('#output').show();

}

</script>

Stylesheets.html

<style>

.hidden {

display: none;

}

.loader {

border: 16px solid #f3f3f3; /* Light grey */

border-top: 16px solid #3498db; /* Blue */

border-radius: 50%;

width: 120px;

height: 120px;

animation: spin 2s linear infinite;

margin: 0px auto;

}

@keyframes spin {

0% { transform: rotate(0deg); }

100% { transform: rotate(360deg); }

}

</style>

Noaccess.html

<!DOCTYPE html>

<html>

<head>

</head>

<body>

<h2>Error:25</h2>

</body>

</html>

Google Workspace Leerling Sync

Google Apps Script

Code.gs

Index.html

Javascript.html

Stylesheets.html

Noaccess.html

Heb je vragen?