// kredensial
const spreadsheetId = ''
const dataOrderSheetName = 'Data Order'
const logSheetName = 'Log'
const botHandle = ''
const botToken = ''
const appsScriptUrl = ''
const telegramApiUrl = `https://api.telegram.org/bot${botToken}`
function log(logMessage = '') {
// akses sheet
const spreadsheet = SpreadsheetApp.openById(spreadsheetId)
const sheet = spreadsheet.getSheetByName(logSheetName)
const lastRow = sheet.getLastRow()
const row = lastRow + 1
// inisiasi nilai
const today = new Date
// insert row kosong
sheet.insertRowAfter(lastRow)
// insert data
sheet.getRange(`A${row}`).setValue(today)
sheet.getRange(`B${row}`).setValue(logMessage)
}
function formatDate(date) {
const monthIndoList = ['Jan', 'Feb', 'Mar', 'Apr', 'Mei', 'Jun', 'Jul', 'Ags',
'Sep', 'Okt', 'Nov', 'Des']
const dateIndo = date.getDate()
const monthIndo = monthIndoList[date.getMonth()]
const yearIndo = date.getFullYear()
const result = `${dateIndo} ${monthIndo} ${yearIndo}`
return result
}
function sendTelegramMessage(chatId, replyToMessageId, textMessage) {
// url kirim pesan
const url = `${telegramApiUrl}/sendMessage`;
// payload
const data = {
parse_mode : 'HTML',
chat_id : chatId,
reply_to_message_id : replyToMessageId,
text : textMessage,
disable_web_page_preview: true,
}
const options = {
method : 'post',
contentType: 'application/json',
payload : JSON.stringify(data)
}
const response = UrlFetchApp.fetch(url, options).getContentText()
return response;
}
function parseMessage(message = '') {
// pisahkan berdasarkan karakter enter
const splitted = message.split('\n')
// inisiasi variabel
let nama = ''
let kodeBarang = ''
let alamat = ''
let resi = ''
// parsing pesan untuk mencari nilai variabel
splitted.forEach(el => {
nama = el.includes('Nama:') ? el.split(':')[1].trim().replaceAll('\n', ' ') :
nama;
kodeBarang = el.includes('Kode Barang:') ? el.split(':')
[1].trim().replaceAll('\n', ' ') : kodeBarang;
alamat = el.includes('Alamat:') ? el.split(':')[1].trim().replaceAll('\n', ' ')
: alamat;
resi = el.includes('Resi:') ? el.split(':')[1].trim().replaceAll('\n', ' ') :
resi;
})
// kumpulkan hasil
const result = {
nama : nama,
kodeBarang: kodeBarang,
alamat : alamat,
resi : resi,
}
// jika data kosong
const isEmpty = (nama === '' && kodeBarang === '' && alamat === '' && resi ===
'')
return isEmpty ? false : result
}
function inputDataOrder(data) {
try {
// akses sheet
const spreadsheet = SpreadsheetApp.openById(spreadsheetId)
const sheet = spreadsheet.getSheetByName(dataOrderSheetName)
const lastRow = sheet.getLastRow()
const row = lastRow + 1
// inisiasi nilai
const number = lastRow
const idOrder = `ORD-${number}`
const today = new Date
// insert row kosong
sheet.insertRowAfter(lastRow)
// insert data
sheet.getRange(`A${row}`).setValue(number)
sheet.getRange(`B${row}`).setValue(idOrder)
sheet.getRange(`C${row}`).setValue(today)
sheet.getRange(`D${row}`).setValue(data['nama'])
sheet.getRange(`E${row}`).setValue(data['kodeBarang'])
sheet.getRange(`F${row}`).setValue(data['alamat'])
sheet.getRange(`G${row}`).setValue(data['resi'])
sheet.getRange(`H${row}`).setValue('Sedang dikemas')
sheet.getRange(`I${row}`).setValue(data['chatId'])
// jika berhasil, return idOrder
return idOrder
} catch(err) {
return false
}
}
function cekResi(resi = null) {
// cegah resi kosong
if (!resi) {
return 'Format pencarian resi tidak valid.'
}
// akses sheet
const spreadsheet = SpreadsheetApp.openById(spreadsheetId)
const sheet = spreadsheet.getSheetByName(dataOrderSheetName)
const lastRow = sheet.getLastRow()
// ambil data
const range = `A2:I${lastRow}`
const dataList = sheet.getRange(range).getValues()
// filter data
const dataListFiltered = dataList.filter(el => el[6].toString().toLowerCase() ===
resi.toString().toLowerCase())
// cek jika resi ditemukan
const isResiFound = dataListFiltered.length > 0
// variabel balasan
let messageReply = ''
// jika ditemukan
if (isResiFound) {
// jika ada no resi yang sama, yang diambil yang paling atas
const data = dataListFiltered[0]
messageReply = `Info Resi <b>${resi}</b>
ID Order: ${data[1]}
Tanggal Order: ${formatDate(data[2])}
Nama: ${data[3]}
Kode Barang: ${data[4]}
Alamat: ${data[5]}
Status Pengiriman: <b>${data[7]}</b>`
// jika tidak
} else {
messageReply = `Resi ${resi} tidak ditemukan.`
}
return messageReply
}
function handleUpdateDeliveryStatus(e) {
// ambil info sheet dan row yang baru diedit
const row = e.range.getRow()
const column = e.range.getA1Notation().replace(/[^a-zA-Z]/g, '')
const sheetName = e.range.getSheet().getSheetName()
// jika perubahan bukan pada sheet data order kolom H
if (sheetName !== dataOrderSheetName || column !== 'H') {
return false
}
// akses sheet
const spreadsheet = SpreadsheetApp.openById(spreadsheetId)
const sheet = spreadsheet.getSheetByName(dataOrderSheetName)
const today = new Date
// ambil data
const range = `A${row}:I${row}`
const data = sheet.getRange(range).getValues()
// isi konstanta
const idOrder = data[0][1]
const tanggalOrder = data[0][2]
const nama = data[0][3]
const kodeBarang = data[0][4]
const alamat = data[0][5]
const resi = data[0][6]
const statusPengiriman = data[0][7]
const chatId = data[0][8].toString()
const textMessage = `Update Info Resi <b>${resi}</b>
ID Order: ${idOrder}
Tanggal Order: ${formatDate(tanggalOrder)}
Nama: ${nama}
Kode Barang: ${kodeBarang}
Alamat: ${alamat}
Status Pengiriman: <b>${statusPengiriman}</b>
<i>Data per-${formatDate(today)}</i>`
// kirim pesan
sendTelegramMessage(chatId, null, textMessage)
}
function doPost(e) {
try {
// urai pesan masuk
const contents = JSON.parse(e.postData.contents)
const chatId = contents.message.chat.id
const receivedTextMessage = contents.message.text.replace(botHandle, '').trim()
// hapus botHandle jika pesan berasal dari grup
const messageId = contents.message.message_id
let messageReply = ''
// 1. jika pesan /start
if (receivedTextMessage.toLowerCase() === '/start') {
// tulis pesan balasan
messageReply = `Halo! Status bot dalam keadaan aktif.`
// 2. jika pesan diawali dengan /input
} else if (receivedTextMessage.split('\n')[0].toLowerCase() === '/input') {
const parsedMessage = parseMessage(receivedTextMessage)
// 2a.jika ada data
if (parsedMessage) {
const data = {
nama : parsedMessage['nama'],
kodeBarang: parsedMessage['kodeBarang'],
alamat : parsedMessage['alamat'],
resi : parsedMessage['resi'],
chatId : chatId
}
// insert data ke sheet
const idOrder = inputDataOrder(data)
// tulis pesan balasan
messageReply = idOrder ? `Data berhasil disimpan dengan ID Order <b>$
{idOrder}</b>` : 'Data gagal disimpan'
// 2b. jika tidak ada data
} else {
messageReply = 'Data kosong dan tidak dapat disimpan'
}
// 3. cek resi
} else if (receivedTextMessage.split(' ')[0].toLowerCase() === '/resi') {
// ambil resi
const resi = receivedTextMessage.split(' ')[1]
// ambil info
messageReply = cekResi(resi)
// 4. format
} else if (receivedTextMessage.toLowerCase() === '/format') {
messageReply = `Untuk <b>input data order</b> gunakan format:
<pre>/input
Nama:
Kode Barang:
Alamat:
Resi: </pre>
Untuk <b>cek resi</b> gunakan format:
<pre>/resi [nomor resi]</pre>
(Tanpa tanda kurung siku)`
// 5. format salah
} else {
messageReply = `Pesan yang Anda kirim tidak sesuai format.
Kirim perintah /format untuk melihat daftar format pesan yang tersedia.`
}
// kirim pesan balasan
sendTelegramMessage(chatId, messageId, messageReply)
} catch(err) {
log(err)
}
}
function setWebhook() {
// akses api
const url = `${telegramApiUrl}/setwebhook?url=${appsScriptUrl}`
const response = UrlFetchApp.fetch(url).getContentText()
Logger.log(response)
}
