Alle prese con il caricamento collettivo degli utenti in Google Workspace, ci si imbatte spesso nel problema di gestire gli username duplicati che derivano dalle omonimie. E sappiamo che la console interpreta questa situazione non come un errore, ma come una nostra volontà di aggiornare l’utente in questione, piuttosto che crearne uno nuovo. Talvolta questo ci fa risparmiare molto tempo – se vogliamo aggiornare massivamente tutti o un gruppo di utenti. Altre volte invece, per una ‘svista’, crea problemi inattesi che dobbiamo poi risolvere manualmente.

Abbiamo già affrontato il problema tempo fa, proponendo una soluzione semi-automatizzata, che richiede comunque di lavorare su più fogli di lavoro. Ho ripreso in mano il tutto, e preparato un foglio che semplifica la procedura, riunendo le varie fasi in un’unica operazione.

La nuova procedura in sintesi

Questa, in sintesi, la novità. Nel nuovo foglio il controllo di eventuali duplicati (omonimi già presenti in piattaforma) avviene prima della generazione del file csv.

L’admin – o chi per lui – inserisce nel foglio nome e cognome degli utenti (volendo anche altri dati come plesso e classe, vedi sotto). Le formule nel foglio generano automaticamente tutti i dati necessari per il csv. A questo punto una funzione nel foglio fa partire la generazione del csv. Lo script scorre ogni username generato dalle formule, e verifica se esiste già in piattaforma. In caso affermativo, modifica lo username aggiungendo il suffisso numerico .02 (volendo si può modificare lo standard). Quindi ripete la verifica per il nuovo username: se trova nuovamente un duplicato, incrementa il suffisso numerico fino a trovare uno username libero.

Solo quando non ci sono più potenziali conflitti vengono generati i dati definitivi per il csv, e scritti nel foglio dedicato. Non resta che scaricare i dati nel formato csv e caricarli in piattaforma, senza ulteriori problemi.

Istruzioni veloci

Se non hai bisogno di indicazioni dettagliate, che trovi sotto, ecco in sintesi cosa c’è da fare per utilizzare il foglio:

• crea una copia del fogli nel tuo Drive (vedi link più in basso)
• dal menu personalizzato Genera CSV scegli Impostazioni primo utilizzo, e concedi le autorizzazioni
• personalizza le formule, in particolare la struttura dello username in H2 e la struttura delle unità organizzative in J2, e copia le formule per tutto il foglio (per altre modifiche, vedi le avvertenze più in basso)
• inserisci i dati nel foglio generazione utenti (usa le prime 5 colonne, o eventualmente solo le prime 2)
• nel menu Genera CSV scegli genera dati CSV
• vai nel foglio per csv, scarica i dati in formato .csv e caricali in console di amministrazione

Preparazione del foglio

Se ti è sufficiente utilizzare il foglio, e non ti interessa approfondire o modificare lo script che ne gestisce il funzionamento, puoi seguire queste prime indicazioni e sei pronto per partire.

Questo foglio è pensato specificamente per gli alunni. Per loro la creazione dell’unità organizzativa può essere leggermente più articolata. Con poche modifiche puoi adattarlo anche per la creazione degli altri utenti della tua scuola.

Copia del file ed impostazioni iniziali

Copia il foglio di lavoro nel tuo Drive, cliccando su questo link (assicurati di usare un browser in cui hai fatto l’accesso a Google con il tuo account Workspace di amministratore):

Creazione csv caricamento utenti – con controllo preliminare duplicati

Nella scheda che si apre, scegli Crea una copia. Eventualmente, potrai poi rinominare il file.

Attendi che accanto alla voce Guida compaia il nuovo menu Genera CSV, quindi scegli Impostazioni primo utilizzo. Alla richiesta di impostare le autorizzazioni clicca su Continua.

Scegli ora l’account di amministratore, ed acconsenti alle richieste. Nel dubbio se hai già impostato le autorizzazioni o meno, puoi ripetere l’operazione senza problemi. Se lo hai già fatto in precedenza, non ti verranno chieste nuovamente le autorizzazioni.

Nel primo foglio di lavoro trovi indicazioni dettagliate sia per quanto visto finora, sia per i prossimi passaggi.

Personalizza il formato del nome utente

Prima di tutto devi modificare la formula per la generazione del nome utente, che si trova nella cella H2. Nel foglio viene utilizzato il formato nome.cognome@dominioscuola.it: se questo formato va bene, sostituisci solamente il testo all’interno delle virgolette “dominioscuola.it” con il dominio della tua scuola. Se invece utilizzate un formato diverso, modifica la parte di formula che concatena i dati in base alle tue esigenze. Fai attenzione a modificare solamente la parte all’interno della parentesi della funzione replaceDiacritics:

replaceDiacritics(F2&"."&G2&"@dominioscuola.it")

Sostituisci F2&”.”&G2&”@dominioscuola.it” con quello che ti serve. Non toccare il resto della formula, perché compie operazioni utili di normalizzazione del nome utente:

• sostituisce le accentate con le corrispondenti non accentate
• elimina gli spazi
• elimina gli apostrofi (presenti ad esempio in alcuni cognomi)
• riduce tutto a caratteri minuscoli
• verifica che siano presenti sia nome che cognome, prima di generare lo username

Quando hai personalizzato la formula, testala, poi riportala per le righe successive. Il foglio ha formule per la creazione di 100 utenti (quindi fino a riga 101), estendile tu a piacere se ti serve.

Personalizza il formato del percorso unità organizzativa

Nella cella J2 viene generato il percorso dell’unità organizzativa. Questo dato in realtà è molto variabile, e diversificato a seconda della gerarchia delle UO che ogni scuola ha strutturato nel tempo. Qui viene proposta una struttura standard per il primo ciclo (normalmente più articolato), in cui gli utenti alunni si posizionano in una unità organizzativa dalla struttura tipo /Alunni/OrdineScuola/Plesso/Classe. La formula di J2 concatena i dati inseriti nelle colonne del foglio relative per ottenere il percorso dell’UO in automatico.

Adatta questa formula alla struttura delle UO della tua piattaforma. In alcuni casi potresti preferire un inserimento diretto del dato invece della formula. Alcune scuole inseriscono tutti gli alunni nella stessa UO, o li diversificano solo per ordine di scuola: in questi casi la formula è superflua. Se non usi i dati di ordine di scuola, plesso e classe puoi tranquillamente eliminare tutte (o alcune di) queste colonne.

Al contrario, se ti servono dati diversi, o più colonne, puoi modificare le intestazioni di queste 3 colonne o aggiungerne quante vuoi. Tiene presente che per il corretto funzionamento dello script:

• le prime due colonne devono rimanere Nome e Cognome
• le colonne dalla F in poi (intestazione nome csv) devono rimanere per ultime, in questo ordine e con quelle precise intestazioni

Se quindi vuoi aggiungere, modificare o eliminare colonne, devi farlo in quello spazio tra la colonna Cognome e la colonna nome csv.

Inserisci i dati degli utenti

Inserisci i dati utente nelle colonne relative del foglio generazione utenti. Se usi la struttura proposta, completa le prime 5 colonne; diversamente, inserisci i dati a seconda delle modifiche che hai apportato al foglio.

Puoi inserire nome e cognome con la combinazione di maiuscole/minuscole che preferisci. Le formule del foglio normalizzeranno questi dati in modo che nome e cognome visualizzati abbiano solo l’iniziale maiuscola. Questo ti permette di fare copia/incolla di nome e cognome dalle liste che la segreteria, il gestionale o SIDI ti forniscono – e capita che siano tutte in maiuscolo – senza doverci mettere mano. Se utilizzi le colonne Ordine scuola, Plesso e Classe (o gli analoghi da te impostati), fai attenzione ad inserire maiuscole, minuscole e spazi. Devono corrispondere esattamente alla grafia adottata in console per nominare le relative unità e sotto-unità organizzative. Diversamente riceverai un errore in fase di caricamento del file in console.

Non scrivere direttamente nelle altre celle del foglio: contengono le formule per la generazione automatica dei dati, se le sovrascrivi smetteranno di funzionare. Ciò non vale ovviamente per le modifiche intenzionali indicate sopra (ad esempio UO impostata a mano, anziché con formula).

Una nota sulla password

La cella password utilizza una funzione definita all’interno dello script. Genera una password casuale di 10 caratteri, presi dalla lista della variabile caratteriPassword (la trovi anche più sotto, nel codice alla riga 34).

var caratteriPassword = "123456789ABCDEFGHJKLMNPQRSTUVWXTZabcdefghikmnopqrstuvwxyz?!-#";

Se hai necessità di farlo, la lista è modificabile senza conseguenze per lo script.

Nota che:

• sono stati eliminati alcuni caratteri di facile confusione per gli utenti (‘i’ maiuscola ed ‘elle’ minuscola, ‘o’ maiuscola e zero…)
• è presente un set limitato di caratteri speciali, più semplici da digitare sulla maggior parte delle tastiere
• la casualità assoluta per un computer non esiste, ma questa funzione è una buona approssimazione, più che adeguata allo scopo di creare una password temporanea

Genera i dati per il file .csv

Quando il foglio generazione utenti è pronto, clicca sul menu Genera CSV, e scegli genera dati CSV. In pochi secondi il foglio per csv verrà completato con i dati per il caricamento in piattaforma. Sostanzialmente trovi gli stessi dati del foglio precedente, ad eccezione del nome utente.

Quando avvii la funzione, lo script scorre ogni username generato dalle formule del primo foglio, e verifica se è già in uso per un altro utente sulla piattaforma. Se non lo è, viene mantenuto immutato. Se invece è già utilizzato, lo script modifica lo username aggiungendo un suffisso numerico, in modo che diventi del tipo nome.cognome.02@dominioscuola.it. A questo punto lo script ripete la verifica, e se di nuovo lo username è in uso (raro, ma possibile) incrementa il suffisso numerico di 1; così via fino a che viene trovato un nome utente libero.

Tutto questo avviene senza il tuo intervento: nel csv vedrai direttamente gli username con suffisso in caso di omonimi già esistenti, e tu non dovrai rinominare manualmente nessun utente. Questo meccanismo progressivo, tra l’altro, economizza gli username progressivi anche per il futuro. Immaginiamo che per uno username della tua scuola siate arrivati alla numerazione 05. L’anno prossimo è in uscita l’alunno senza suffisso numerico, oppure quello con il suffisso 02. Tra due anni un eventuale ulteriore username omonimo non prenderà il suffisso 06, ma riprenderà lo username senza suffissi (primo caso), oppure quello con suffisso 02 (secondo caso).

Utilizza il csv generato

Ora tutto è pronto. Posizionati nel foglio per csv, clicca su File > Scarica > Valori separati da virgola (.csv, foglio corrente). Carica il file in console di amministrazione senza fare altro, e gli utenti verranno creati. In caso di errore, scarica il log (icona della clessidra in alto a destra), e cerca il tipo di errore. Molto spesso potrai rimediare con un piccolo intervento sui dati del foglio, o sulle formule che li generano.

Il codice

Se sei interessato al codice che gestisce il funzionamento del foglio, puoi aprirlo dal menu Strumenti > Editor di script. Attenzione, ogni modifica anche minima al codice potrebbe compromettere il funzionamento di tutto il foglio: modifica solo con consapevolezza.

Per comodità, riporto anche qui sotto il codice (in caso di modifiche o cancellazioni accidentali, puoi fare copia/incolla da qui per riportare lo script allo stato originale).

// crea il menu personalizzato nel foglio di lavoro
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Genera CSV').addItem('Impostazioni primo utilizzo', 'primoUtilizzo').addItem('Genera dati CSV', 'generaCsv').addToUi();
}


// sostituisce le accentate con le corrispondenti non accentate
// viene utilizzata direttamente nelle celle del foglio, all'interno della formula per creare lo username
function replaceDiacritics(rng) {
  var ACCENTED = 'ÀÁÂÃÄÅàáâãäåÒÓÔÕÕÖØòóôõöøÈÉÊËèéêëðÇçÐÌÍÎÏìíîïÙÚÛÜùúûüÑñŠšŸÿýŽžęłćń诹żŁ';
  var REGULAR = 'AAAAAAaaaaaaOOOOOOOooooooEEEEeeeeeCcDIIIIiiiiUUUUuuuuNnSsYyyZzelcncZazL';
  var REGEXP = new RegExp('[' + ACCENTED + ']', 'g');
  function replace(match) {
    var p = ACCENTED.indexOf(match);
    return REGULAR[p];
  }

  if (typeof rng === 'object' && rng.length !== undefined) { // if rng is an array
    return rng.map(function (cell) { return replaceDiacritics(cell); });
  }
  else {
    return rng.replace(REGEXP, replace);
  }
}


// genera una password casuale di 10 caratteri, presi dalla lista della variabile caratteriPassword
// la lista è modificabile senza conseguenze per lo script
// sono stati eliminati alcuni caratteri di facile confusione per gli utenti ('i' maiuscola ed 'elle' minuscola, 'o' maiuscola e zero...)
// è presente un set limitato di caratteri speciali, più semplici da digitare sulla maggior parte delle tastiere
function generaPassword() {
  var pw = '';
  var caratteriPassword = "123456789ABCDEFGHJKLMNPQRSTUVWXTZabcdefghikmnopqrstuvwxyz?!-#";
  for (var j = 0; j < 10; j++) {
    var posizioneCasuale = Math.floor(Math.random() * caratteriPassword.length);
    pw += caratteriPassword.substring(posizioneCasuale, posizioneCasuale + 1);
  }
  return pw;
}


// genera i dati per il csv e li scrive nel foglio relativo
// verifica eventuali username duplicati, e nel caso apporta le modifiche necessarie
function generaCsv() {

  var foglio = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('generazione utenti');
  var datiIntestazione = foglio.getRange(1, 1, 1, foglio.getLastColumn()).getValues();
  var colonnaIniziale;
  for (c = 0; c < datiIntestazione[0].length; c++) {
    if (datiIntestazione[0][c] === 'nome csv') {
      colonnaIniziale = c + 1;
    }
  }

  var datiFoglio = foglio.getRange(2, colonnaIniziale, foglio.getLastRow() + 1, 5).getValues().filter(x => x[0] !== '').map(y => y.concat("TRUE"));

  for (i = 0; i < datiFoglio.length; i++) {
    let username = datiFoglio[i][2];
    var duplicato;
    var nuovoUsername = username;

    // verifica se lo username generato dalle formule è già utilizzato in piattaforma
    do {
      Logger.log(nuovoUsername);
      // se è username duplicato, aggiunge suffisso numerico ed effettua nuovamente la verifica
      // se di nuovo username duplicato, incrementa il suffisso numerico fino a trovare uno username libero
      try {
        duplicato = AdminDirectory.Users.get(nuovoUsername);
        let usernameParziale = nuovoUsername.split("@")[0];
        if (usernameParziale.split(".")[2]) {
          let nuovoProgressivo = ('0' + (parseInt(usernameParziale.split(".")[2]) + 1)).substring(-2);
          nuovoUsername = `${usernameParziale.slice(0, -3)}.${nuovoProgressivo}@${nuovoUsername.split('@')[1]}`;
        } else {
          nuovoUsername = `${usernameParziale}.02@${nuovoUsername.split('@')[1]}`;
        }
      }
      // se non viene trovato duplicato, interrompe il ciclo e continua con il resto dello script
      catch (e) {
        duplicato = false;

      }

    } while (duplicato)

    Logger.log(nuovoUsername);

    if (nuovoUsername !== username) {
      datiFoglio[i][2] = nuovoUsername;
    }

  }

  // scrive i dati sul foglio
  var foglioCsv = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('per csv');
  var intestazioni = foglioCsv.getRange(1, 1, 1, foglioCsv.getLastColumn()).getValues();
  foglioCsv.clear();
  SpreadsheetApp.flush();
  foglioCsv.getRange(1, 1, 1, intestazioni[0].length).setValues(intestazioni);
  foglioCsv.getRange(2, 1, datiFoglio.length, datiFoglio[0].length).setValues(datiFoglio);

}

// funzione di impostazione autorizzazioni
// la funzione è vuota, in quanto serve solo a richiamare la finestra delle autorizzazioni
function primoUtilizzo() {

}

Per agevolare le operazioni di cambio anno scolastico, sto ultimando un foglio per la rimozione degli utenti filtrati per gruppo di appartenenza. Questa operazione, non direttamente possibile da console di amministrazione, può risultare particolarmente utile per eliminare gli alunni delle classi in uscita, se non sono già inseriti un unità organizzative che ricalcano le classi.
Per le scuole che invece inseriscono gli alunni in unità organizzative per classe, può risultare un doppio lavoro gestire ingressi e uscite dei singoli alunni modificando ogni volta sia l’unità organizzativa che il gruppo. Per questo scenario sto preparando uno script che simula i gruppi dinamici, attualmente disponibili sono per le versioni di Google Workspace a pagamento. Attivando lo script, basterà aggiungere o togliere gli alunni dalle unità organizzative classe, e di conseguenza verranno aggiunti o rimossi anche dai rispettivi gruppi classe.

Pubblicherò qui i due lavori non appena pronti. Se vuoi rimanere aggiornato, puoi iscriverti alla Newsletter.