Cara Buat Membership Otomatis: Google Form ke Sheets (Tanpa Coding)

Capek memindahkan data pendaftar member secara manual satu per satu?

Di panduan ini, saya bagikan template Google Sheets / Spreadsheets yang otomatis memilah level member (Bronze/Silver/Gold) langsung dari Google Form.

Selamat datang di tutorial Sistem Membership Bertingkat Gratis Pakai Google Sheets & Form

💡 Bayangkan: Setiap kali pelanggan mengisi Form Google Online dengan nominal pembelian, database keanggotaan Kamu di Google Sheets akan secara otomatis terisi, dan bahkan langsung menetapkan predikat keanggotaan mereka, apakah mereka masuk kategori Common, naik tingkat ke Bronze atau Silver, atau bahkan mencapai level tertinggi Gold.

Dalam panduan langkah demi langkah ini, saya akan membongkar Formula Rahasia Google Sheets yang mengintegrasikan data formulir, menghitung total pembelian, dan menerapkan logika predikat bertingkat menggunakan fungsi powerful (IFS). Siap untuk mengotomatisasi sistem loyalty dan membership Kamu?

Mari kita mulai!

***

BAGIAN 1. Persiapan Formulir Sebagai Pengisian Database Ke Sheet

Pastikan Kamu sudah login menggunakan akun google yang ingin digunakan, karena untuk diawal ini, spreadsheet dan form harus menggunakan akun google yang sama.

Jika sudah, sekarang lanjtukan untuk membuat form baru, melalui link ini https://docs.google.com/forms/u/0/

Lalu pilih Blank Form

Setelah masuk ke dalam "Blank Form", maka kamu akan disajikan dengan tampilan awal seperti ini

Pada bagian "Untitled form" pojok kiri atas merupakan nama dari file form tersebut, kamu bisa mengubah nama tersebut sesuai dengan yang Kamu mau.

Sedangkan "Untitled Form" yang dibagian tengah tersebut merupakan judul nama formulir tersebut, dimana akan dilihat oleh publik atau yang nantinya mengisi formulir tersebut.

Dalam hal ini, saya memberikan nama seperti dibawah ini, bisa kamu ikuti atau bebas memilih nama yang lain.

Jika sudah selesai menentukan nama file dan judul form, tambahkan juga deskripsi yang terletak dibawah judul formulir.

Lanjut membuat section pertama, yaitu "Nama Depan", dengan tipe "Short Answer", seperti gambar dibawah ini.

Setelah mengaktifkan tipe "short answer", centang aktif pada bagian "required" dan aktifkan show "description", lalu pada bagian description tuliskan deskripsi terkait section "Nama Depan".

Tambahkan section pertanyaan selanjutnya terkait dengan "Nama Belakang", seperti cara dibawah ini

Lakukan hal yang sama seperti section question "Nama Depan", jangan lupa aktifkan "required" dan show "description" nya

Setelah itu, lanjutkan kembali untuk membuat question lainnya seperti, "Nomor HP/WA" dan "Nilai Belanja", dengan setingan yang sama dengan question sebelumnya

Berbeda dari section sebelum-sebelumnya, untuk section selanjutnya ini, kita akan menggunakan tipe "date", untuk menampung jawaban tanggal berbelanja

Karena waktu mengisi formulir yg terisi otomatis, bisa jadi akan berbeda dengan waktu real/nyata berbelanja, oleh sebab itu dibuat section "date" khusus.

Seperti gambar dibawah ini

Untuk section terakhir, buat "Domisili" dengan tipe "short answer"

Setelah semua section berhasil dibuat, sekarang beranjak ke tab "Response", untuk melakukan pengaitan "link to sheets"

Pada pop up pilihan "link to sheets" pilih "create a new spreadsheet" untuk membuat sheet baru, karena kita akan memulai semuanya dari awal, atau semisalnya Kamu mempunyai sheet kosong yang mau digunakan, kamu bisa pilih "select existing spreadsheet".

Tapi untuk tutorial kali ini, saya menyarankan Kamu membuat semuanya dari awal, jadi pilih "create a new spreadsheet".

Setelah terhubung, jangan lupa untuk mencentang "get email notifications for new responses", hal ini berguna untuk mendapatkan notifikasi realtime, semisal ada yg mengirimkan respon jawaban dari formulir.

Sedangkan untuk "select destination for responses" digunakan untuk melakukan perubahan link spreadsheet, semisalnya mau diganti dengan sheet yang lain, untuk kali ini kita belum menggunakan fitur perubahan link spreadsheet.

Beginilah tampilan dari spreadsheet yang sudah terkoneksi dengan google form yang kita buat

Terdapat 7 header default yang sesuai dengan section pertanyaan pada form yang kita buat, kedepannya akan ditambah beberapa header pendukung untuk melakukan tracking tingkatan membership.

Sebelum lanjut ke sheet tabel membership, kita publish terlebih dahuliu formulirnya, seperti dibawah ini

Pada pengaturan "Responders" biarkan saja secara default, lalu klik "Publish".

Setelah terpublish, formulir yang sudah jadi tersebut akan mengenerate sebuah link form, yang bisa kamu bagikan kepada calon "responden" nantinya.

Jika formulir sudah di publish, sekarang balik ke sheet tabel formulir, lalu tambahkan beberapa header seperti dibawah ini

Header yang akan ditambah meliputi; "Generate Username ID", "Nama Lengkap", "Jam Belanja", jadinya seperti dibawah ini.

Pada header kolom J, ubah tipe kolomnya menjadi "time", agar dapat menampilkan data waktu/jam nya dengan tepat, dengan cara seperti ini.

***

BAGIAN 2: Optimasi Sheet Form Membership

Sebelum memulai optimasi, kita mulai terlebih dahulu untuk mengisi formulir yang barusan kita buat, unutk mendapatkan data sebagai contoh database.

Copy link formulir membership yang sudah kita buat tadi, lalu pastekan ke tab baru atau menggunakan browser yang lain. Saya memilih menggunakan browser yang sama, tapi menggunakan "incognito" atau private browser kalau di firefox.

Lalu isi 3 - 5 contoh respon sebagai data yang akan kita olah, isi saja sekenanya, jangan terlalu dipikirkan.

Perlu kamu ketahui, untuk "Timestamp" dan "Tanggal Belanja" adalah dua hal yang berbeda, untuk "Timestamp" itu terisi otomatis dari sistem form sesuai tangal dan waktu responden mengisi formulir, sedangkan "Tanggal Belanja" adalah tanggal dan waktu yang diisi manual oleh responden dari form.

Saya asumsikan yang mengisi formulir adalah pemilik usaha/karyawan tersebut, bukan dari pembeli/konsumen, sedangkan data-data bisa diketahui dari kwitansi/bon yang diberikan kepada konsumen/pembeli.

Karena akan lebih aman jika kita sendiri yang mengisi formulir itu, ketimbang user/pembeli/konsumen, karena biasanya konsumen merasa risih/tidak tertarik untuk mengisi secara mandiri hal-hal yang seperti itu.

Tapi semuanya pilihan, kembali kepada kamu lagi, mau mengisi sendiri, atau tetap membiarkan konsumen yang mengisi form online tersebut.

Oke, kita lanjut ke pembahasan.

***

Sekarang klik pada bagian H2 tepat dibawah header "Generate Username ID", ketik seperti rumus dibawah ini.

=ARRAYFORMULA(
IF(
(B2:B = "") + (C2:C = "") + (D2:D = "");
"";
LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")) &
IF(
COUNTIF(LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")); LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")))=1;
"";
"_" & COUNTIF(LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")); "<" & LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")))+1
)
)
)

PENJELASAN:

ARRAYFORMULA: Sebagai pondasi dari seluruh rumus. Fungsi ini memungkinkan Kamu menerapkan satu rumus ke seluruh kolom (dalam hal ini, dari baris H3 hingga akhir/H1000) tanpa perlu menyalin rumus ke setiap baris secara manual. Ini menghemat waktu dan mencegah kesalahan.

IF(
(B2:B = "") + (C2:C = "") + (D2:D = "");
"";
... (Bagian utama rumus) ...
)

IF(kondisi; nilai_jika_benar; nilai_jika_salah): Bagian ini adalah "penjaga gerbang" yang memastikan ID hanya dibuat jika data yang dibutuhkan sudah terisi.

(B2:B = "") + (C2:C = "") + (D2:D = ""): Dalam Google Sheets, ketika Kamu menjumlahkan ekspresi Boolean (TRUE atau FALSE), TRUE dianggap sebagai 1 dan FALSE sebagai 0. Kondisi ini akan menghasilkan TRUE` (yaitu nilai > 0) jika salah satu dari kolom B, C, atau D pada baris tersebut kosong.

"": Jika kondisi di atas benar (ada data yang kosong), maka sel ID akan dibiarkan kosong. Ini menjaga sheet Kamu tetap rapi.

***

Jika data sudah terisi alias ada responden yang mengisi, maka IF "Gerbang Utama" menjadi FALSE, dimana akan menjalankan fungsi lainnya sebagai rumus lanjutan.

LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_"))

B2:B & C2:C & D2:D: Ini adalah penggabungan (Concatenation). Data dari kolom B, C, dan D pada setiap baris digabungkan menjadi satu string (string: boleh dibaca teks) (NamaDepanNamaBelakangNomorHP/WA).

SUBSTITUTE(..., " "; "_"): Semua spasi (" ") dalam string yang sudah digabung diubah menjadi garis bawah ("_"). Ini penting untuk membuat ID yang ramah basis data.

LOWER(...): Seluruh string ID diubah menjadi huruf kecil (lowercase). Ini menciptakan konsistensi; ID "edisutikno" dianggap sama dengan "EdiSutikno".

Contoh hasil generate ID yang sudah bersih dan digabung (misalnya: edisutikno085612456987).

Setelah itu, baru masuk sebuah mekanisme dalam penanganan duplikat, agar setiap ID dipastikan unik/tidak boleh sama.

& IF(
COUNTIF(LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")); LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")))=1;
"";
"_" & COUNTIF(LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")); "<" & LOWER(SUBSTITUTE(B2:B & C2:C & D2:D; " "; "_")))+1
)

Pengecekan Duplikat

COUNTIF(rentang; kriteria): Fungsi ini menghitung berapa kali ID dasar (hasil dari langkah 3) muncul di seluruh kolom ID dasar (yang dihitung oleh ARRAYFORMULA secara virtual).
...)=1: Jika hasil hitungannya adalah 1, berarti ID tersebut unik (hanya muncul satu kali).

Jika Unik (=1)

"": Jika unik, tidak ada penambahan apa-apa. ID akan tetap seperti hasil sementara (misalnya: edisutikno).

Jika Duplikat (>1):

Ini akan menambahkan nomor urut di akhir ID untuk membedakannya.

COUNTIF(rentang; "<" & ID_Dasar) + 1: Ini adalah trik yang baik untuk penomoran urut.
COUNTIF(..., "<" & ID_Dasar): Ini menghitung berapa banyak entri ID dasar yang secara alfabetis muncul sebelum ID dasar yang sedang diproses. Angka ini secara efektif memberikan indeks dari duplikat yang sudah ada di atas baris saat ini.
+ 1: Hasil hitungan indeks tersebut kemudian ditambah 1, sehingga menghasilkan nomor urut yang tepat (misalnya: duplikat pertama akan mendapat _1, duplikat kedua mendapat _2, dan seterusnya).
"_" & ...: Nomor urut ditambahkan setelah garis bawah (_) untuk pemisah.

Hasil Akhir Rumus:

Setelah menyelesaikan "GENERATE USERNAME ID" lanjut untuk merumuskan "Nama Lengkap" menggunakan rumus fungsi dibawah ini.

=ARRAYFORMULA(IF(B2:B=""; ""; B2:B & " " & C2:C))

Mari kita bedah.

Rumus ini merupakan contoh klasik dari cara menggunakan ARRAYFORMULA untuk mengotomatisasi tugas yang paling sering dilakukan di Google Sheets: menggabungkan teks.

Rumus ini akan mengambil data dari Kolom B dan Kolom C, menggabungkannya, dan menampilkan hasilnya di kolom tempat rumus ini diletakkan, mulai dari baris 2 hingga baris terakhir.

ARRAYFORMULA: Seperti pada rumus sebelumnya, ini adalah kunci untuk efisiensi. Alih-alih menyalin rumus baris demi baris, Kamu cukup memasukkan rumus ini ke satu sel (misalnya, D2). Rumus ini akan secara otomatis mengisi hasil ke bawah, hingga akhir data pada lembar kerja Kamu.

Bagian pemeriksaan data sel kosong:

IF(B2:B=""; ""; ... (Bagian rumus utama) ...)

IF(kondisi; nilai_jika_benar; nilai_jika_salah): Ini adalah mekanisme pembersihan atau "pemutus" rumus.

B2:B = "": Rumus ini memeriksa setiap baris di Kolom B (mulai dari baris 2) apakah kolom tersebut kosong. Kolom B biasanya dipilih sebagai kolom utama yang harus terisi (misalnya, nama depan).

"": Jika sel di Kolom B kosong, maka sel tempat rumus ini bekerja juga akan dibiarkan kosong (""). Ini mencegah munculnya hasil gabungan yang kosong atau tidak diinginkan di baris-baris kosong di bawah data Anda.

Bagian rumus utama (untuk membuat nama lengkap berdasarkan data yang tersedia)

B2:B & " " & C2:C

& (Ampersand): Simbol ini adalah operator standar dalam Google Sheets (dan Excel) untuk menggabungkan atau menyambungkan teks (dikenal sebagai concatenation).

B2:B: Merujuk pada semua data di Kolom B.

C2:C: Merujuk pada semua data di Kolom C.

" ": Ini adalah spasi tunggal diapit oleh tanda kutip. Tujuannya adalah memastikan data dari Kolom B dan Kolom C dipisahkan oleh satu spasi saat digabungkan, menghasilkan format yang mudah dibaca (misalnya: "Edi Sutikno").

Pada dasarnya, rumus ini melakukan tiga langkah krusial dalam satu baris kode:

Cek Kondisi: Pastikan data di Kolom B terisi.
Gabungkan: Ambil teks dari Kolom B, tambahkan spasi, lalu tambahkan teks dari Kolom C.
Otomatisasi: Terapkan proses ini ke seluruh kolom secara otomatis menggunakan ARRAYFORMULA.

Lanjut untuk melakukan setup pada "Jam Belanja", rumusnya masih menggunakan ARRAYFORMULA, seperti dibawah ini

=ARRAYFORMULA(IF(F2:F="";"";F2:F))

Tujuan dan maksud rumus adalah mengambil data pada kolom F, lalu dikonversikan ke format Jam, hasilnya seperti dibawah ini

Jangan lupa pada setingan header "Jam Belanja" diubah menjadi format jam, seperti cara dibawah ini

***

ANALISIS REKAP DATA

Setelah bagian "Form Response 1" selesai, buat 1 sheet baru dan beri nama "RekapData"

Pemberian nama sheet baru itu opsional, kamu bebas memberi nama apapun, kali ini saya menggunakan nama RekapData

Buat header seperti yang saya buat pada sheet "RekapData" ini

Pada kolom B2, masukkan rumus QUERY dibawah ini, untuk mengambil data ID yang sudah kita olah pada sheet "Form Responses 1"

=QUERY(
'Form Responses 1'!$D$2:$I;
"SELECT H, I, G, D, MIN(F) WHERE H IS NOT NULL GROUP BY H, I, G, D ORDER BY MIN(F) ASC LABEL H '', I '', G '', D '', MIN(F) ''";
0
)

Maka hasilnya, akan otomatis seperti dibawah ini

Pada kolom "ID Pembeli", "Nama Lengkap", "Domisili", "No HP", "Tanggal Pertama Beli" terisi otomatis karena menggunakan pendekatan rumus QUERY.

Bagaimana untuk "ID MEMBER", "Total Belanja", dan "Level Member" ?

Barangkali kamu bertanya-tanya, kenapa ada istilah menggunakan ID MEMBER, bukannya sudah ada ID Pembeli?

Terkait dengan ID MEMBER, ini digunakan untuk penyesuaian setiap pembeli pertama kali langsung mendapatkan ID MEMBER sebagai acuan untuk Level Member dan riwayat Total Belanja.

Jadi ID Pembeli atas nama Edi Sutikno, bisa berkali-kali didapatkan seiring seterusnya belanja, sedangkan untuk ID MEMBER Edi Sutikno hanya satu untuk selamanya, tidak dapat bertambah pada sheet "RekapData", sedangkan ID Pembelinya pada sheet "FormResponses 1" bisa muncul berkali-kali.

Untuk menentukan otomatis "ID MEMBER", masukkan rumus dibawah ini, ke sel G2, maka G3 dan seterusnya akan otomatis terisi.

=ARRAYFORMULA(IF(B2:B="";"";TEXT(F2:F;"yymmdd") & ROW(F2:F)))

Untuk menentukan "Total Belanja", masukkan rumus dibawah ini ke H2, sehingga H3 dan seterusnya ikut terisi karena menggunakan ARRAYFORMULA

=ARRAYFORMULA( IF(B2:B="";""; SUMIF('Form Responses 1'!$H$2:$H; B2:B; 'Form Responses 1'!$E$2:$E)))

Lalu pada kolom baris sel I1 "Level Member", gunakan rumus IFS dibawah ini, masukkan ke kolom I2

=IFS(
H2=""; "";
H2 < 100000; "Common";
H2 < 500000; "Bronze";
H2 < 5000000; "Silver";
H2 >= 10000000; "Gold"
)

Jangan lupa Copy keseluruhan rumus IFS tersebut, lalu pastekan ke I3:I (baca: I 3 sampai I 1000), cara copy paste massal:

Klik pada sel I2
Tekan CTRL + C
Tekan CTRL + SHIFT + Tombol Panah Bawah (Keyboard)
Lanjutkan Paste dengan menekan CTRL + D atau CTRL + V.
Setelah tercopy semua, balik lagi ke Kolom I2, dengan menekan tombol CTRL + SHIT + Panah Atas (Keyboard)

Untuk keterangan "Common", "Bronze" dst, bisa kamu sesuaikan dengan keinginan "kata" kamu sendiri.

Maka hasil akhirnya menjadi seperti ini

Untuk mepercantik dan mempertegas data, kamu bisa menerapkan Conditional Formatting.

Note:

Pada kolom "Level Member" ubah batas nominal dalam rumus, menyesuaikan dengan keinginan kamu.
Spreadsheet ini bisa kamu kembangkan lebih jauh, bisa ditambah Pivot, analisa bulanan dan lain-lain

Untuk mengakses Form dan sheet contoh, kamu bisa akses atau download gratis disini:

Google Form
Google Sheets

Untuk detail tutorial dan tips spreadsheet lainnya, kamu bisa kunjungi youtube saya di @guidofamula29

Semoga tutorial ini bermanfaat

Guido Famula Berbagi info tutorial praktis seputar digital dan pengolahan data yang akan mengubah cara Kamu bekerja menjadi lebih cepat, efisien, dan otomatis.