Google Sheets'te IBAN Doğrulama: Formül ve Apps Script

Google E-Tablolar'da (Google Sheets) bir IBAN sütununu doğrulamanın iki sağlam yolu var: yalnızca yerleşik işlevlerle kurulan bir formül ya da Apps Script ile yazılmış özel bir işlev. Formül kurulumu hızlıdır ve dosyayı paylaştığınız herkes için ek izin istemez. Apps Script ise hata nedenini metin olarak yazabilir ve ülke uzunluk tablosunu kodda tutmanıza izin verir. Aşağıdaki iki çözümün de mantığını, sayfaya koymadan önce aynı test IBAN'ları üzerinde çalıştırıp doğruladık.

Hangisini seçerseniz seçin, sonuç biçimsel bir kontroldür. "Geçerli" çıkan bir IBAN'ın açık bir hesaba karşılık geldiğini veya listedeki kişiye ait olduğunu E-Tablolar bilemez. Bu sınırın neden aşılamadığını IBAN'dan hesap sahibi bulunur mu? rehberinde anlattık.

Formül yolu: LET, REDUCE ve LAMBDA

MOD 97 kontrolünün püf noktası, IBAN'dan türeyen 20-30 haneli sayıyı hiç oluşturmamaktır. E-Tablolar da sayıları kayan noktalı olarak tutar ve bu büyüklükte bir tamsayıyı kesin saklayamaz. Yuvarlanmış sayının 97'ye bölümünden kalan ise anlamsızdır. Aynı sorunun Excel tarafındaki ayrıntılı açıklaması Excel'de IBAN doğrulama formülü yazısında var; burada doğrudan çözüme geçiyoruz.

Ham IBAN A2 hücresindeyse şu formülü B2'ye yazın:

=LET(temiz, REGEXREPLACE(UPPER(A2), "[^A-Z0-9]", ""),
 dizi, MID(temiz, 5, LEN(temiz)) & LEFT(temiz, 4),
 kalan, REDUCE(0, SEQUENCE(LEN(dizi)), LAMBDA(acc, no,
   LET(kk, CODE(MID(dizi, no, 1)),
       hv, IF(kk <= 57, kk - 48, kk - 55),
       MOD(acc * IF(hv < 10, 10, 100) + hv, 97)))),
 IFERROR(AND(LEN(temiz) >= 15, LEN(temiz) <= 34,
   CODE(temiz) >= 65, CODE(MID(temiz, 2, 1)) >= 65,
   CODE(MID(temiz, 3, 1)) <= 57, CODE(MID(temiz, 4, 1)) <= 57,
   IF(LEFT(temiz, 2) = "TR", LEN(temiz) = 26, TRUE),
   kalan = 1), FALSE))

Formül dört adımda çalışır. REGEXREPLACE harf ve rakam dışındaki her şeyi siler, böylece boşluklu, tireli veya bölünemez boşluk içeren kopyalar da temizlenir. dizi ilk dört karakteri sona taşır. REDUCE diziyi karakter karakter dolaşır: CODE ile karakter kodunu alır, rakamları 0-9, harfleri 10-35 değerine çevirir ve kalanı her adımda 97'ye göre küçültür. İki haneli bir harf değeri eklenirken birikmiş kalan 100 ile, tek haneli rakamda 10 ile çarpılır; bu, harfleri rakam dizisine yazıp birleştirmekle matematiksel olarak aynı sonucu verir. Ara değer hiçbir zaman 9.635'i geçmez.

Son satırdaki AND kontrolleri, ilk iki karakterin harf ve sonraki ikisinin rakam olmasını, uzunluğun 15-34 aralığında kalmasını ve TR IBAN'ı için tam 26 karakteri şart koşar. Boş hücre ya da tek karakterlik bir değer geldiğinde SEQUENCE(0) hata üretir; IFERROR bunu FALSE'a çevirir.

Bu formülü E-Tablolar'ın kendi motorunu taklit eden bir Python betiğinde çalıştırdık. TR140001005001234567890123 (boşluklu ve küçük harfli yazımları dahil), DE89370400440532013000 ve GB82WEST12345698765432 TRUE; kontrol hanesi bozulmuş TR150001005001234567890123 ile bir hanesi eksik veya fazla TR IBAN'ları FALSE döndü. Rastgele ülkelerden üretilmiş 20 bin geçerli ve bozulmuş örnekte bağımsız referans hesapla fark çıkmadı.

Formülün bilinçli bir hoşgörüsü var: REGEXREPLACE yıldız, eğik çizgi gibi beklenmedik karakterleri de sessizce atar. Site aracımız böyle bir girdiyi "geçersiz karakter" diye işaretler. Siz de katı olmak istiyorsanız ham değerdeki boşlukları sildikten sonra uzunluğu temiz ile karşılaştıran bir koşul ekleyin.

Toplu kullanım: adlandırılmış işlev ve MAP

Formülü aşağı doğru yüzlerce satıra kopyalamak çalışır ama okunmaz. Daha temiz yol, Veri > Adlandırılmış işlevler menüsünden IBAN_GECERLI adında bir işlev tanımlamaktır. Bağımsız değişken yer tutucusuna iban adını verin ve formül tanımında A2 yerine iban yazın. Sonra tek bir hücreye şunu girin:

=MAP(A2:A, LAMBDA(x, IF(x = "", "", IBAN_GECERLI(x))))

MAP A sütunundaki her değeri LAMBDA'ya verir ve sonuçları aşağı doğru yayar. Boş satırlar boş kalır, böylece sütunun sonuna kadar FALSE görmezsiniz. Adlandırılmış işlev dosyaya kaydedilir; dosyada çalışan herkes aynı tanımı kullanır ve formülü değiştirmek tek yerden yapılır. Yayılan sonucun altındaki hücrelere elle bir şey yazarsanız formül #REF! hatasına düşer; o sütunu yalnızca sonuçlara ayırın.

Apps Script yolu: hata nedenini yazan özel işlev

Formül yalnızca TRUE ya da FALSE der. Muhasebe ekibine dönecek bir listede "neden hatalı?" sorusunun cevabı da gerekir. Uzantılar > Apps Script menüsünden açılan editöre şu kodu yapıştırıp kaydedin:

const IBAN_UZUNLUK = { TR: 26, DE: 22, GB: 22, FR: 27, NL: 18, AT: 20, IT: 27, ES: 24 };

function ibanDurum_(deger) {
  const iban = String(deger == null ? "" : deger).replace(/[\s-]/g, "").toUpperCase();
  if (iban === "") return "";
  if (!/^[A-Z]{2}\d{2}[A-Z0-9]{11,30}$/.test(iban)) return "HATALI: biçim";
  const beklenen = IBAN_UZUNLUK[iban.slice(0, 2)];
  if (beklenen && iban.length !== beklenen) return "HATALI: uzunluk";
  const r = iban.slice(4) + iban.slice(0, 4);
  let kalan = 0;
  for (const ch of r) {
    const v = parseInt(ch, 36); // 0-9 -> 0-9, A-Z -> 10-35
    kalan = (kalan * (v < 10 ? 10 : 100) + v) % 97;
  }
  if (kalan !== 1) return "HATALI: kontrol hanesi";
  return beklenen ? "GEÇERLİ BİÇİM" : "GEÇERLİ BİÇİM (uzunluk tablosu yok)";
}

/**
 * IBAN'ı biçimsel olarak kontrol eder (uzunluk + MOD 97).
 * Hesabın var olduğunu veya kime ait olduğunu doğrulamaz.
 *
 * @param {string|Array<Array<string>>} girdi Tek hücre veya tek sütunluk aralık (A2:A500).
 * @return Her satır için durum metni.
 * @customfunction
 */
function IBANKONTROL(girdi) {
  if (Array.isArray(girdi)) {
    return girdi.map((satir) => [ibanDurum_(satir[0])]);
  }
  return ibanDurum_(girdi);
}

Ardından sayfada tek bir hücreye yazın:

=IBANKONTROL(A2:A500)

Kodda üç ayrıntı işe yarar. İlki, yardımcı işlevin adının alt çizgiyle bitmesi. Google'ın belgelerine göre alt çizgiyle biten işlevler Apps Script'te özel kabul edilir ve sayfadan çağrılamaz; böylece formül otomatik tamamlamada yalnızca IBANKONTROL görünür. İkincisi, @customfunction etiketi. Bu JSDoc etiketi, işlevin otomatik tamamlama listesinde açıklamasıyla çıkmasını sağlar.

Üçüncüsü ve en önemlisi, işlevin aralık kabul etmesi. Google, özel işlevin sayfada her kullanımının Apps Script sunucusuna ayrı bir çağrı olduğunu belirtiyor. 500 hücreye ayrı ayrı =IBANKONTROL(A2) yazarsanız 500 çağrı yaparsınız ve sayfa "Yükleniyor..." durumunda uzun süre bekleyebilir. Aralık verdiğinizde işlev iki boyutlu bir dizi alır, tek çağrıda hepsini işler ve yine iki boyutlu dizi döndürür. Belgeye göre bir özel işlev çağrısının 30 saniye içinde bitmesi gerekir; MOD 97 hesabı birkaç bin satır için bu sürenin çok altında kalır, yine de on binlerce satırı parçalara bölmek temkinli olur.

Uzunluk tablosu bilerek kısa tutuldu. Tabloda olmayan bir ülke gelirse işlev MOD 97'yi yine yapar ama sonuca "uzunluk tablosu yok" notunu ekler. Kendi listenizde hangi ülkeler varsa onları ülke IBAN formatları sayfasındaki değerlerle ekleyin. Tabloyu tahminle doldurmayın; yanlış bir uzunluk, doğru IBAN'ları toptan reddettirir.

Hangisini seçmeli?

Dosyayı başkalarıyla paylaşıyorsanız ve kimsenin kod editörü açmasını istemiyorsanız formülü seçin. Adlandırılmış işlev olarak tanımlandığında okunaklıdır, sonuçlar anında hesaplanır ve Apps Script'in çağrı sınırlarıyla uğraşmazsınız. Dezavantajı, sonucun yalnızca TRUE ya da FALSE olması ve ülke uzunluklarının formüle gömülmesinin hantal kalmasıdır.

Hatalı satırları bir başkasına geri gönderecekseniz Apps Script daha iyi. "HATALI: uzunluk" yazan bir satırı düzeltmek, yalnızca FALSE gören birinin numarayı baştan incelemesinden çok daha hızlıdır. Uzunluk tablosunu tek yerde tutmak da kolaydır. Bedeli, dosyaya bağlı bir betik ve sayfa her yeniden hesaplandığında sunucu çağrısıdır.

İkisini birleştirmek de mümkün: sütunda formül hızlı bir ilk eleme yapar, FALSE dönen satırlar için yan sütunda Apps Script nedeni yazar. Sonuç sütununa koşullu biçimlendirme ekleyip FALSE ya da HATALI ile başlayan hücreleri renklendirirseniz uzun bir listede sorunlu satırlar ilk bakışta görünür. Filtre görünümüyle yalnızca bu satırları listelemek, düzeltme turunu hızlandırır.

Hücre biçimi ve gizlilik

Sheets, TR öneki kopmuş bir IBAN'ı sayı olarak algılayabilir. Özel işlev o hücreden sayı alır ve "HATALI: biçim" döndürür, formül ise FALSE der. Bu doğru davranış: son haneler çoktan yuvarlanmış olabilir. IBAN sütununu veri girmeden önce Biçim > Sayı > Düz metin olarak ayarlayın.

Apps Script kodu Google'ın sunucularında çalışır, ama veri zaten aynı hesaptaki E-Tablolar dosyasındadır. Asıl risk, koda sonradan eklenen bir dış istekle IBAN'ların başka bir servise gönderilmesidir. Doğrulama saf bir hesaplama olduğu için bunun hiçbir gerekçesi yok. Dosyayı paylaşırken de IBAN sütununu herkese açmak yerine paylaşılan kopyada maskelemeyi düşünün.

Tek seferlik bir liste için formül ya da kod yazmak zahmetliyse, sütunu kopyalayıp toplu IBAN kontrol aracına yapıştırabilirsiniz. Liste tarayıcınızda işlenir, hatalı satırlar ve bankalar ayrı ayrı gösterilir.