Formattare uno Spreadsheet automaticamente con Google Apps Script

Sfogliando la documentazione ufficiale relativa agli Spreadsheet, ed in particolare la Classe Sheet e la Classe Range, è facile notare l'esistenza di molti metodi disponibili che consentono di modificare il formato del foglio di calcolo tramite Google Apps Script con l'uso di codice incorporato (bound-script). Possiamo pertanto creare funzioni che agiscono sul layout di pagina personalizzato direttamente all'apertura del file e andare ad intervenire in maniera automatica sul formato celle, le loro dimensioni, i colori del testo e di sfondo, sul numero di righe e di colonne e sui relativi bordi, sull'allinemento del testo, il font e molto altro.

Senza ombra di dubbio un caso pratico comune per il quale queste funzionalità possono tornare utili si ha quando nasce la necessità di creare documenti condivisi con persone che devono compilare i dati in essi richiesti. Senza forne un formato desiderato (secondo i propri standard di lavoro) si rischia di dover perdere tempo in fase successiva a rielaborare il formato di ciascun foglio compilato da diverse persone con la complicazione, in questa fase, di dover far maggiore attenzione a non compromettere i dati compilati.

Un esempio di formattazone automatica di un foglio di calcolo, che viene eseguito ad ogni apertura del file mantenendo i dati inseriti dall'utente nella parte destinata alla loro immissione, lo si può osservare andando ad inserire il il seguente codice nell'editor di script incorporato nello SpreadSheet (per aprire l'editor di script cliccare nella barra dei menù sulla voce 'Tools' e successivamente sull'elemento 'Script editor...'):

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet1');
  ss.toast('Operazioni di layout per il foglio di calcolo in corso...','Attendi quale che secondo...',5);
  var header1 = sh.getRange('A1:G1').mergeAcross().setBackground('green').setValue('Lista iscritti al corso formativo').setFontSize(26);
  var header2 = sh.getRange(2,1,1,6).setBackground('#007fff').setValues([['Nome','Cognome','Email','Data di nascita', 'Altre informazioni', 'Firma']]).setFontSize(12);
  sh.getRange(1,1,2,6).setBorder(true,true,true,true,true,true).setHorizontalAlignment('center')
  .setVerticalAlignment('middle').setFontWeight('bold');
  var columnWidth = [150,150,180,120,400,200];
  for(var n=0; n < columnWidth.length ; n++){
    sh.setColumnWidth(n+1,columnWidth[n]);
  }
  sh.insertColumnAfter(6).deleteColumns(7,sh.getMaxColumns()-6);
  sh.insertRows(sh.getLastRow()+1,20);
  sh.deleteRows(sh.getLastRow()+1, sh.getMaxRows()-sh.getLastRow()-17);
  sh.getRange(3,1,sh.getMaxRows()-2,sh.getLastColumn()).setBorder(false, false, false, false, true, false);
  for(var n=sh.getLastRow() ; n > 3 ; n--){
    if(sh.getRange(n,1,1,6).getValues().toString().replace(/,/g,'')=='') {
      sh.deleteRow(n);
    }
  }
  sh.setFrozenRows(2);
  SpreadsheetApp.flush();
  ss.toast('Il layout è stato formattato correttamente in modo automatico, adesso puoi utilizzare il file.','Operazione effettuata con successo!',5);
}

Questo il risultato a livello visivo, Fig. 1:

Fig.1 - Layout di uno Sheet formattato automaticamente con Google Apps Script

Andiamo ad analizzare il codice di cui sopra per identificare le operazione che, all'atto pratico, sono effettuate in modo automatico dallo script e capire come agiscono sul layout dello Sheet:

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet1');
  ss.toast('Operazioni di layout per il foglio di calcolo in corso...','Attendi quale che secondo...',5);

Recupera l'istanza dello Spreadsheet attivo e del relativo foglio attivo (Sheet1), dopodiché mostra per 5 secondi una notifica in basso a destra, con il metodo toast, che avvisa dell'inizio delle operazioni di formattazione.

  var header1 = sh.getRange('A1:G1').mergeAcross().setBackground('green').setValue('Lista iscritti al corso formativo').setFontSize(26);
  var header2 = sh.getRange(2,1,1,6).setBackground('#007fff').setValues([['Nome','Cognome','Email','Data di nascita', 'Altre informazioni', 'Firma']]).setFontSize(12);
  sh.getRange(1,1,2,6).setBorder(true,true,true,true,true,true).setHorizontalAlignment('center')
  .setVerticalAlignment('middle').setFontWeight('bold');

Unisce il range di celle A1:G1 in un'unica cella e ne imposta il background, il testo e la dimensione del carattere.
Alla seconda riga recuperata le celle interessate passando al metodo getRange la riga, la colonna, il numero di righe ed il numero di colonne da coinvolgere e ne applica il background passando un valore esadecimale per definirne il colore (a differenza della riga precedente dove viene passato direttamente il nome del colore), il nome dei campi sotto forma di array ed anche in questo caso definisce una dimensione del font.

La riga di codice successiva applica un bordo al range di celle selezionato (i parametri del bordo sono definiti nell'ordine: top, left, bottom, right, vertical, horizontal), allinea il testo al centro e lo converte in grassetto.

  var columnWidth = [150,150,180,120,400,200];
  for(var n=0; n < columnWidth.length ; n++){
    sh.setColumnWidth(n+1,columnWidth[n]);
  }

Definisce un array di lunghezze in pixel da applicare alle colonne e tramite un ciclo associa il valore ad ognuna di esse tramite il metodo setColumnWidth.

  sh.insertColumnAfter(6).deleteColumns(7,sh.getMaxColumns()-6);
  sh.insertRows(sh.getLastRow()+1,20);
  sh.deleteRows(sh.getLastRow()+1, sh.getMaxRows()-sh.getLastRow()-17);
  sh.getRange(3,1,sh.getMaxRows()-2,sh.getLastColumn()).setBorder(false, false, false, false, true, false);

Definisce il numero di righe e di colonne da mostrare all'interno del foglio di calcolo e ne elimina tutte le altre in modo da ottenere la griglia di celle solo per i campi interessati.
A questo punto applica il solo bordo verticale a tutte le celle in modo da separare visivamente le colonne l'una dalle altre.

  for(var n=sh.getLastRow() ; n > 3 ; n--){
    if(sh.getRange(n,1,1,6).getValues().toString().replace(/,/g,'')=='') {
      sh.deleteRow(n);
    }
  }

Pulisce il file (sempre all'apertura dello stesso) in modo da eliminare qualsiasi riga non compilata nel caso si trovasse tra due o più righe che contengono informazioni. In questo modo il contenuto del documento viene compattato verso l'alto.

  sh.setFrozenRows(2);
  SpreadsheetApp.flush();
  ss.toast('Il layout è stato formattato correttamente in modo automatico, adesso puoi utilizzare il file.','Operazione effettuata con successo!',5);

Con il metodo setFrozenRows blocca le prime due righe in modo che rimangano fisse anche scorrendo il file verso il basso.
Tramite flush vengono applicate tutte le modifiche rimaste in sospeso.

A questo punto viene visualizzata una notifica, tramite un toast, che avvisa l'utente dell'operazione effettuata con successo, Fig. 2:

Fig. 2 - Notifica in stile toast in uno Spreadshhet

I metodi utilizzati per la formattazione applicata al nostro foglio di calcolo di esempio sono stati molti, abbiamo utilizzato, oltre a quelli già citati, mergeAcross, setBackground, setValue, setFontSize, setHorizontalAlignment, setBorder, setColumnWidth, getMaxRows e non solo. La documentazione ufficiale (vedere link a inizio tutorial) ne offre altrettanti e per ciascuno definisce quanti e quali parametri può accettare in ingresso.

Con le nozioni appena apprese e un po' di fantasia non sarà difficile creare layout accattivanti e soprattutto autoformattanti!