Google Sheets - Formula e funzione personalizzata per la verifica del Codice Fiscale
Sto sperimentando le applicazioni Google e la possibilità di automatizzare delle funzioni con l'uso di Google Apps Script.Dopo un po' di tribolazioni sono riuscito con un a certa soddisfazione, a personalizzare il testo di una slide di Google in base ai dati di un fogli di Google Sheets, trasformarla in un file PDF, allegarlo a un messaggio email in HTML, personalizzato anch'esso e inviarlo via email in automatico dall'account Gmail o da uno di quelli per i quali questo account è abilitato a inviare messaggi.
Ho trovato degli interessantissimi tutorial (in inglese) su Youtube, che mi hanno consentito di realizzare cose interessanti, qui: https://www.youtube.com/channel/UC8p19gUXJYTsUPEpusHgteQ
Ora però voglio trattare di come verificare la validità di un codice fiscale in Google Sheets, dato che non ho trovato molto in giro.
In un foglio di Google ho una anagrafica con i codici fiscali in colonna G e volevo fare un controllo su eventuali errori.
Sul funzionamento del Codice Fiscale si possono trovare trovano dei dettagli in Wikipedia: https://it.wikipedia.org/wiki/Codice_fiscale e anche sul sito di Marco Filocamo, indicato più sotto.
Un modo che rileva già molte incongruenze è l'uso di una espressione regolare (regex) che verifica che ci siano lettere e numeri nei posti giusti e nella quantità giusta, tipo quella che ho trovato qui: http://gcalisse.blogspot.com/2019/05/espressione-regolare-per-il-controllo.html
^[a-zA-Z]{6}[0-9]{2}[abcdehlmprstABCDEHLMPRST]{1}[0-9]{2}([a-zA-Z]{1}[0-9]{3})[a-zA-Z]{1}$
Io l'ho ridotta per farle accettare solo lettere maiuscole. Quando usata in una cella di Google Sheets, per controllare il codice fiscale nella cella G5, appare così:
Questa vale per CF con lettere maiuscole e riporta VERO o FALSO.
L'espressione così composta non considera validi i codici fiscali modificati per "omocodia". Quando infatti persone nate nello stesso giorno, nello stesso luogo e aventi lo stesso nome, si ritrovano ad avere lo stesso CF, l'Agenzia delle Entrate modifica uno dei due codici sostituendo uno o più di quelli che normalmente sono numeri con delle specifiche lettere.
Mi è accaduto di trovare una persona della Romania la cui penultima cifra, che di norma è un numero, era una "V". la "V" sostituisce il numero 9. Dato che per i Paesi esteri il codice della località non indica una città, ma una intera nazione, la possibilità che nascano due persone nello stesso giorno, con lo stesso nome non è tanto remota.
Per allargare il filtro in modo che accetti anche le possibili lettere (LMNPQRSTUV), la formula diventa:
La formula può essere usata anche in una sola cella e valere per tutte quelle sottostanti, con l'uso di ARRAYFORMULA().
In Google Sheets, come pure in Excel, è infatti possibile avere delle formule che, messe in una cella, popolano una intera colonna o anche un'area di più righe e più colonne.
La formula dell'esempio seguente è composta da un array che ha un primo elemento fisso, il testo "TestCF", che verrà a trovarsi nella cella dove sta la formula, e una funzione ARRAYFORMULA() che crea a sua volta un array che andrà a popolare le celle sottostanti con i risultati di ciò che viene posto tra le parentesi.
Circa ARRAYFORMULA occorre dire che funziona solo se all'interno vengono usate funzioni che a loro volta sono in grado di operare con array. Non ho trovato indicazioni esplicite su questo argomento ma in diversi casi ho potuto usare ARRAYFORMULA () risparmiandomi di dover duplicare la stessa formula in una colonna.
L'esempio seguente controlla tutti i CF della colonna G (da G5 in giù) e se la verifica i soli numeri fallisce, ne fa una seconda, che considera anche le possibili lettere che possono sostituire i numeri.
Ma questo non garantisce che il CF sia valido perché è possibile modificare largamente le lettere e i numeri e per la formula andrà sempre bene.
Una garanzia molto maggiore che il CF sia corretto la si ottiene grazie alla verifica del "carattere di controllo", che è l'ultimo. una lettera tra A e Z, calcolata in base ai valori dei caratteri precedenti. Se uno qualsiasi dei caratteri viene modificato, questo sistema di controllo rileva l'incoerenza dei dati.
Con parecchio ingegno si può costruire un sistema di formule per verificare un CF solo con le formule native del foglio elettronico, come dimostrato qui: https://www.marcofilocamo.it/calcolo-codice-fiscale-excel/
In alternativa, e rendendo anche possibile il controllo di una intera lista di codici usando ARRAYFORMULA(), si può usare una funzione personalizzata tipo la seguente, da inserire usando il menu "Estensioni > Apps Script" del foglio di Google Sheets:
=REGEXMATCH(G5; "^[A-Z]{6}[0-9]{2}[ABCDEHLMPRST]{1}[0-9]{2}([A-Z]{1}[0-9]{3})[A-Z]{1}$")
L'espressione così composta non considera validi i codici fiscali modificati per "omocodia". Quando infatti persone nate nello stesso giorno, nello stesso luogo e aventi lo stesso nome, si ritrovano ad avere lo stesso CF, l'Agenzia delle Entrate modifica uno dei due codici sostituendo uno o più di quelli che normalmente sono numeri con delle specifiche lettere.
Mi è accaduto di trovare una persona della Romania la cui penultima cifra, che di norma è un numero, era una "V". la "V" sostituisce il numero 9. Dato che per i Paesi esteri il codice della località non indica una città, ma una intera nazione, la possibilità che nascano due persone nello stesso giorno, con lo stesso nome non è tanto remota.
Per allargare il filtro in modo che accetti anche le possibili lettere (LMNPQRSTUV), la formula diventa:
=REGEXMATCH(G5; "^[A-Z]{6}[0-9LMNPQRSTUV]{2}[ABCDEHLMPRST]{1}[0-9LMNPQRSTUV]{2}([A-Z]{1}[0-9LMNPQRSTUV]{3})[A-Z]{1}$")
In Google Sheets, come pure in Excel, è infatti possibile avere delle formule che, messe in una cella, popolano una intera colonna o anche un'area di più righe e più colonne.
La formula dell'esempio seguente è composta da un array che ha un primo elemento fisso, il testo "TestCF", che verrà a trovarsi nella cella dove sta la formula, e una funzione ARRAYFORMULA() che crea a sua volta un array che andrà a popolare le celle sottostanti con i risultati di ciò che viene posto tra le parentesi.
Circa ARRAYFORMULA occorre dire che funziona solo se all'interno vengono usate funzioni che a loro volta sono in grado di operare con array. Non ho trovato indicazioni esplicite su questo argomento ma in diversi casi ho potuto usare ARRAYFORMULA () risparmiandomi di dover duplicare la stessa formula in una colonna.
L'esempio seguente controlla tutti i CF della colonna G (da G5 in giù) e se la verifica i soli numeri fallisce, ne fa una seconda, che considera anche le possibili lettere che possono sostituire i numeri.
={"TestCF"; ARRAYFORMULA(SE(VAL.TESTO(G5:G); SE(REGEXMATCH(G5:G; "^[A-Z]{6}[0-9]{2}[ABCDEHLMPRST]{1}[0-9]{2}([A-Z]{1}[0-9]{3})[A-Z]{1}$"); "ok"; SE(REGEXMATCH(G5:G; "^[A-Z]{6}[0-9LMNPQRSTUV]{2}[ABCDEHLMPRST]{1}[0-9LMNPQRSTUV]{2}([A-Z]{1}[0-9LMNPQRSTUV]{3})[A-Z]{1}$"); "omocodia?"; "NO")); ))}
Una garanzia molto maggiore che il CF sia corretto la si ottiene grazie alla verifica del "carattere di controllo", che è l'ultimo. una lettera tra A e Z, calcolata in base ai valori dei caratteri precedenti. Se uno qualsiasi dei caratteri viene modificato, questo sistema di controllo rileva l'incoerenza dei dati.
Con parecchio ingegno si può costruire un sistema di formule per verificare un CF solo con le formule native del foglio elettronico, come dimostrato qui: https://www.marcofilocamo.it/calcolo-codice-fiscale-excel/
In alternativa, e rendendo anche possibile il controllo di una intera lista di codici usando ARRAYFORMULA(), si può usare una funzione personalizzata tipo la seguente, da inserire usando il menu "Estensioni > Apps Script" del foglio di Google Sheets:
[Aggiornamento marzo 2022 - Ho aggiunto un test "typeof cf !== 'string'" nelle prime righe della procedura seguente in quanto se il contanuto della cella era un numero, cf.length valeva "undefined" e non veniva eseguito il return}
/**
* Controlla checksum del Codice Fiscale
*
* Si "pesano i primi 15 caratteri.
* I caratteri dispari (primo, terzo, ...) vengono "pesati" in base a
* dispariObj{}, mentre i pari (secondo, quarto, ...) vengono pesati
* in base a pariObj{}.
* Alla somma viene applicata la funzione modulo, che dà un valore tra 0 e 25.
* Il valore indica una delle 26 lettere dell'alfabeto, che dovrà
* corrispondere con il 16° carattere del Codice Fiscale.
*
* @param {cf} il Codice fiscale, in lettere maiuscole
* @return {boolean} true o false
*
* @customfunction
*
*/
function testCF(cf) {
const dispariObj = {
'0': 1 , '1': 0 , '2': 5 , '3': 7 , '4': 9 , '5': 13, '6': 15, '7': 17, '8': 19, '9': 21,
'A': 1 , 'B': 0 , 'C': 5 , 'D': 7 , 'E': 9 , 'F': 13, 'G': 15, 'H': 17, 'I': 19, 'J': 21,
'K': 2 , 'L': 4 , 'M': 18, 'N': 20, 'O': 11, 'P': 3 , 'Q': 6 , 'R': 8 , 'S': 12, 'T': 14,
'U': 16, 'V': 10, 'W': 22, 'X': 25, 'Y': 24, 'Z': 23
}
pariObj= {
'0': 0 , '1': 1 , '2': 2 , '3': 3 , '4': 4 , '5': 5 , '6': 6 , '7': 7 , '8': 8 , '9': 9 ,
'A': 0 , 'B': 1 , 'C': 2 , 'D': 3 , 'E': 4 , 'F': 5 , 'G': 6 , 'H': 7 , 'I': 8 , 'J': 9 ,
'K': 10, 'L': 11, 'M': 12, 'N': 13, 'O': 14, 'P': 15, 'Q': 16, 'R': 17, 'S': 18, 'T': 19,
'U': 20, 'V': 21, 'W': 22, 'X': 23, 'Y': 24, 'Z': 25,
}
const str_0_25 = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
result = false
if (typeof cf !== 'string' || 16 < cf.length || cf.length < 16) {
return result
}
let sum = 0
for (i = 0; i < 15; i++) {
let ch = cf.substr(i,1);
let val = 0
if (i % 2 === 0) {
// carattere dispari
val = dispariObj[ch]
} else {
// carattere pari
val = pariObj[ch]
}
sum = sum + val;
}
const lastCh = str_0_25.substr(sum % 26, 1)
if (cf.substr(15, 1) === lastCh) {
result = true;
}
return result
}
Ma così com'è serve per verificare il contenuto di una sola, inserendo in un'altra cella la formula:
=testCFArray(G5)
/**
* #param {cf} input può essere elemento singolo, vettore o matrice e viene
* risolto ricorsivamente.
* #return riporta l'elemento o il vettore con i risultati.
* @customfunction
*/
function testCFArray(cf) {
if (cf.map) {
let resArray = []
cf.map((cf1) => {resArray.push(testCFArray(cf1))})
return resArray;
} else {
return testCF(cf);
}
}
={"TestCFArray"; Arrayformula(SE(VAL.TESTO(G5:G); testCFArray(G5:G); ))}
Verifica del codice di Partita IVA
Allo stesso modo ho creato una function per la partita IVA. Pare funzionare.Anche qui, su Wikipedia (https://it.wikipedia.org/wiki/Partita_IVA) si trovano indicazioni, con anche i codici degli uffici provinciali.
La prima procedura richiama in modo ricorsivo quella che controlla un singolo codice. Si può usare la stessa sia per celle singole che in modo array
/**
* #param {pi} input può essere elemento singolo, vettore o matrice e viene
* risolto ricorsivamente.
* #return riporta l'elemento o il vettore con i risultati.
* @customfunction
*/
function testPIVAArray(pi) {
if (pi.map) {
let resArray = []
pi.map((pi1) => {resArray.push(testCFArray(pi1))})
return resArray;
} else {
return testCF(pi);
}
}
/**
*
* La P.IVA è formata da 11 cifre numeriche.
* Le prime 7 cifre indicano il n. di matricola
* le tre cifre successive indicano l'ufficio provinciale
* l'ultima cifra è quella di controllo (secondo la
* Formula di Luhn)
*
* L'ultima cifra è calcolata in base alla prime 10:
* Si sommano le prime 5 cifre in posizione dispari e
* il doppio mod 9 delle prime 5 cifre in posizione pari.
* Alla somma si applica ancora:
* - Modulo 10 (resta 0..9)
* - Complemento a 10 (resta 10..1)
* - Modulo 10 (il 10 diventa 0)
*
* @param {cf} il Codice fiscale, in lettere maiuscole
* @return {boolean} true o false
*
* @customfunction
*
*/
function testPIVA(pi) {
// se pi è numero lo trasforno in stringa di 11 car, con zeri iniziali
let pis = pi.toString().padStart(11, "0");
result = false
let sum = 0
for (i = 0; i < 10; i++) {
let val = parseInt(pis.substr(i,1));
if (i % 2 === 0) {
// carattere dispari
sum = sum + val;
} else {
// carattere pari
sum = sum + ((val * 2) % 9)
}
}
let lastNum = ((10 - (sum % 10)) % 10)
if (pis.substr(11, 1) === lastNum) {
result = true;
}
return result
}
