Mengatasi Header Hilang pada Formula Array LET Google Sheets
Iwan Efendi2 min
Cara mengatasi header tabel yang hilang akibat filter array di Google Sheets. Gunakan SEQUENCE dan ROWS untuk mengecualikan baris pertama dari REGEXMATCH.
Read in EnglishEN
Saya menemukan anomali yang cukup membingungkan di spreadsheet monitoring procurement kantor: header kolom ID GR (kolom ke-27) tiba-tiba hilang. Formulanya sama sekali tidak diubah dan data di sheet sumber juga aman, sempat bikin saya garuk-garuk kepala sebelum akhirnya sadar apa masalahnya.
Penyebab utamanya ada di variabel
Karena logika
Berikut adalah formula
Menemukan bug yang tidak memicu pesan error (silent bug) seperti ini memang butuh ketelitian ekstra. Semuanya terlihat normal di permukaan, padahal ada logika di belakang layar yang sedang terganggu tanpa memicu pesan error formal.
Ke depannya, jika kamu menggunakan
Kalau kamu punya cara lain yang lebih praktis buat mengamankan header di Google Sheets, bagikan di kolom komentar ya. Semoga trik kecil ini bisa membantu menghemat waktu troubleshooting kamu di projek spreadsheet berikutnya!
_c27 di dalam formula LET yang bertugas memfilter nomor GR. Saya menggunakan REGEXMATCH agar kolom ini hanya menampilkan angka:
_c27; ARRAYFORMULA(IFERROR(
IF(REGEXMATCH(INDEX(_data;;27)&"";"^\d+$"); INDEX(_data;;27); "");
""
));
REGEXMATCH(INDEX(_data;;27)&"";"^\d+$") hanya mengembalikan TRUE jika semua karakter dalam cell berupa angka, data di baris bawah memang terfilter dengan rapi. Masalahnya, baris pertama adalah header berisi teks "ID GR". Karena teks tersebut mengandung huruf, regex mengembalikan FALSE, dan formula pun otomatis mengosongkan cell header tersebut.
Di dalam array spreadsheet, header kolom hanyalah baris pertama dari data biasa, sehingga rentan ikut terfilter jika kita menerapkan aturan pembersihan data di sepanjang kolom.
Untuk mengatasinya, kita perlu membuat pengecualian agar baris pertama tidak ikut diperiksa oleh regex. Saya mengakalinya dengan fungsi SEQUENCE(ROWS(_data))=1. Fungsi ini menghasilkan array boolean yang bernilai TRUE hanya untuk baris pertama (seperti {TRUE; FALSE; FALSE; ...}). Dengan menggabungkan kondisi ini menggunakan operator + (yang berfungsi sebagai OR dalam konteks array), kita bisa menyelamatkan header kolom dari filter regex.
1
Temukan Variabel Filter
Cari bagian variabel di dalam fungsiLET yang bertugas memfilter data kolom menggunakan regex atau kondisi lainnya. Di kasus saya, variabelnya adalah _c27.2
Sisipkan Pengecualian Baris Pertama
Ubah kondisiIF dengan menambahkan (SEQUENCE(ROWS(_data))=1) + [kondisi_filter]. Hasil perubahan variabelnya akan seperti ini:_c27; ARRAYFORMULA(IFERROR(
IF(
(SEQUENCE(ROWS(_data))=1) + REGEXMATCH(INDEX(_data;;27)&"";"^\d+$");
INDEX(_data;;27);
""
);
""
));
3
Verifikasi Hasil
Lihat kembali output spreadsheet kamu. Sekarang teks header"ID GR" seharusnya sudah muncul kembali di baris paling atas, sementara data di baris-baris bawahnya tetap terfilter dengan benar.LET lengkap setelah diperbaiki:
=LET(
_id; "1c1GqBT1FeI6wFtTfXRQZElXcjRWHWCcocYYxCBJ7gNU";
_r1; IMPORTRANGE(_id; "Konstruksi!B1:AP5000");
_r2; IMPORTRANGE(_id; "Close!B3:AP5000");
_r3; IMPORTRANGE(_id; "Rutin!B3:AP5000");
_r4; IMPORTRANGE(_id; "PO Reg_HO!B3:AP5000");
_raw; VSTACK(
QUERY(_r1; "select * where Col1 is not null offset 1"; 1);
QUERY(_r2; "select * where Col1 is not null"; 0);
QUERY(_r3; "select * where Col1 is not null"; 0);
QUERY(_r4; "select * where Col1 is not null"; 0)
);
_c1; ARRAYFORMULA(IF(INDEX(_raw;;1)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;1)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c2; ARRAYFORMULA(IF(INDEX(_raw;;2)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;2)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c3; ARRAYFORMULA(IF(INDEX(_raw;;3)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;3)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c6; ARRAYFORMULA(IF(INDEX(_raw;;6)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;6)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_c7; ARRAYFORMULA(IF(INDEX(_raw;;7)="";"";REGEXREPLACE(SUBSTITUTE(INDEX(_raw;;7)&"";CHAR(160);" ");"^\s+|\s+$";"")));
_data; HSTACK(
_c1; _c2; _c3;
CHOOSECOLS(_raw;4;5);
_c6; _c7;
CHOOSECOLS(_raw;SEQUENCE(COLUMNS(_raw)-7;1;8))
);
_col34; ARRAYFORMULA(IF(INDEX(_data;;34)="";"";REGEXREPLACE(INDEX(_data;;34);"^\d+\.\s*";"")));
_col35; ARRAYFORMULA(IF(INDEX(_data;;35)="";"";REGEXREPLACE(REGEXREPLACE(INDEX(_data;;35);"^\d+\.\s*";"");"(?i)DROP LOKASI";"DROP")));
_c27; ARRAYFORMULA(IFERROR(
IF(
(SEQUENCE(ROWS(_data))=1) + REGEXMATCH(INDEX(_data;;27)&"";"^\d+$");
INDEX(_data;;27);
""
);
""
));
IFERROR(
HSTACK(
CHOOSECOLS(_data;SEQUENCE(26));
_c27;
CHOOSECOLS(_data;SEQUENCE(6;1;28));
_col34;
_col35;
CHOOSECOLS(_data;SEQUENCE(COLUMNS(_data)-35;1;36))
);
""
)
)
Kenapa bug ini tiba-tiba muncul?
Formula ini padahal sudah berjalan normal selama berbulan-bulan, dan saya yakin sebelumnya teks header aman-aman saja. Kemungkinan besar Google Sheets memperbarui evaluation engine mereka atau mengubah cara formula array memproses tipe data campuran. Logika yang kemarin berjalan mulus, tiba-tiba menjadi silent failure yang mengosongkan cell header tanpa memicu pesan error apa pun.
REGEXMATCH atau filter berbasis kondisi lainnya di dalam LET untuk membersihkan kolom tertentu, selalu ingat bahwa baris pertama adalah header, bukan data. Gunakan pola sederhana ini agar baris pertama selalu dikecualikan dari filter:
IF(
(SEQUENCE(ROWS(_range))=1) + [kondisi_filter_kamu];
_range;
""
)
Topics
Topik dalam catatan
Jelajahi pembahasan serupa lewat topik-topik terkait berikut.
Bagikan artikel ini
Diskusi
Menyiapkan area komentar...