function sendPersonalMessages() {
// === KONFIGURASI UTAMA ===
const CONFIG = {
sheetName: "Sheet1", // Nama sheet
phoneColumnName: "phone_no", // Kolom nomor HP
messageTemplate: "Halo {{col_1}}, pesanan {{col_2}} Anda akan sampai dalam {{col_3}}. Terima kasih 🙏",
api_key: "YOUR-API-KEY",
number_id: "YOUR-NUMBER-ID",
enable_typing: "1",
method_send: "async", // async / sync
country_code: "62",
minPhoneLength: 10 // validasi dasar nomor HP
};
// ==========================
try {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.sheetName);
if (!sheet) throw new Error(`Sheet '${CONFIG.sheetName}' tidak ditemukan`);
const data = sheet.getDataRange().getValues();
if (data.length < 2) throw new Error(`Sheet '${CONFIG.sheetName}' tidak memiliki data`);
const headers = data[0];
const rows = data.slice(1);
const phoneIndex = headers.indexOf(CONFIG.phoneColumnName);
if (phoneIndex === -1) {
throw new Error(`Kolom '${CONFIG.phoneColumnName}' tidak ditemukan di header`);
}
rows.forEach((row, rowIndex) => {
const actualRow = rowIndex + 2; // index baris untuk log (karena mulai dari baris 2 di spreadsheet)
// Lewati baris kosong
if (row.every(cell => cell === "")) {
Logger.log(`Baris ${actualRow} dilewati (kosong)`);
return;
}
const phoneNo = row[phoneIndex];
if (!phoneNo || phoneNo.toString().length < CONFIG.minPhoneLength) {
Logger.log(`Baris ${actualRow} dilewati (nomor HP tidak valid: '${phoneNo}')`);
return;
}
// Bangun data placeholder
const rowData = {};
headers.forEach((col, i) => {
rowData[`{{${col}}}`] = row[i] || "";
});
// Gantikan semua placeholder
let finalMessage = CONFIG.messageTemplate;
Object.entries(rowData).forEach(([placeholder, value]) => {
finalMessage = finalMessage.replaceAll(placeholder, value.toString());
});
// Siapkan payload
const payload = {
api_key: CONFIG.api_key,
number_id: CONFIG.number_id,
enable_typing: CONFIG.enable_typing,
method_send: CONFIG.method_send,
phone_no: phoneNo.toString(),
country_code: CONFIG.country_code,
message: finalMessage
};
const options = {
method: "post",
contentType: "application/json",
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
try {
const response = UrlFetchApp.fetch("https://api.balesotomatis.id/public/v1/send_personal_message", options);
const code = response.getResponseCode();
const result = response.getContentText();
if (code >= 200 && code < 300) {
Logger.log(`✅ Baris ${actualRow} sukses kirim ke ${phoneNo}:\n${result}`);
} else {
Logger.log(`❌ Baris ${actualRow} GAGAL kirim (status ${code}):\n${result}`);
}
} catch (e) {
Logger.log(`❌ Baris ${actualRow} ERROR saat mengirim: ${e.message}`);
}
});
} catch (globalError) {
Logger.log("💥 ERROR GLOBAL: " + globalError.message);
}
}