Created
December 26, 2022 14:38
-
-
Save caruizdiaz/49a67657e9d2b4edebde965e26ef1c28 to your computer and use it in GitHub Desktop.
App Script to send WhatsApp messages using 2Chat and Google Sheets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// | |
// App Script to send WhatsApp messages using 2Chat and Google Sheets | |
// | |
const _2CHAT_API_KEY = '<PASTE YOUR API KEY HERE>'; | |
const _2CHAT_SOURCE_PHONE_NUMBER = '<PASTE YOUR WHATSAPP PHONE NUMBER HERE>'; | |
const STATUS_PENDING = 'Pending'; | |
const STATUS_QUEUED = 'Queued'; | |
const STATUS_SENDING = 'Sending'; | |
const STATUS_FAILED = 'Failed'; | |
const STATUS_SENT = 'Sent'; | |
function generatePendingMessages() { | |
var sheet = SpreadsheetApp.getActive().getSheetByName("Queue"); | |
var data = sheet.getDataRange().getValues(); | |
var statusCol = data[0].indexOf("Status"); | |
var nameCol = data[0].indexOf("Name"); | |
var messageCol = data[0].indexOf("Message"); | |
for ( i = 1; i < data.length; i++) { | |
const status = sheet.getRange(i + 1, statusCol + 1).getValue(); | |
if ( status.toString().toLocaleLowerCase() != STATUS_PENDING.toLocaleLowerCase() ) { | |
Logger.log(`row=[${i}] has no pending status: [${status}]`); | |
continue; | |
} | |
const firstName = data[i][nameCol]; | |
Logger.log(`Generating message for row=[${i}] with state=[${status}]`); | |
sheet.getRange(i + 1, messageCol + 1).setValue(createMessage(firstName)); | |
} | |
} | |
function confirmedSendingMessagesModal(count) { | |
const ui = SpreadsheetApp.getUi(); | |
var result = ui.alert( | |
'Confirm sending messages', | |
`${count} message(s) are queued to be sent.\n⚠️ Make sure all messages were properly generated.\n\nDo you want to continue?`, | |
ui.ButtonSet.YES_NO); | |
return result == ui.Button.YES; | |
} | |
function sendQueuedMessages() { | |
var sheet = SpreadsheetApp.getActive().getSheetByName("Queue"); | |
var data = sheet.getDataRange().getValues(); | |
var statusCol = data[0].indexOf("Status"); | |
var queuedMessages = data.filter(row => row[statusCol].toString().toLocaleLowerCase() === STATUS_QUEUED.toLocaleLowerCase()); | |
if (!queuedMessages || queuedMessages.length === 0) { | |
SpreadsheetApp.getUi().alert("No messages to send", | |
`There are no messages in ${STATUS_QUEUED} state to be processed.`, | |
SpreadsheetApp.getUi().ButtonSet.OK); | |
return; | |
} | |
if (!confirmedSendingMessagesModal(queuedMessages.length)) { | |
Logger.log("Sending was cancelled"); | |
return; | |
} | |
Logger.log("Sending messages"); | |
var sendingTimeCol = data[0].indexOf("Sending time"); | |
var messageCol = data[0].indexOf("Message"); | |
var phoneNumberCol = data[0].indexOf("Phone number"); | |
for ( i = 1; i < data.length; i++) { | |
const status = sheet.getRange(i + 1, statusCol + 1).getValue(); | |
if ( status.toString().toLocaleLowerCase() != STATUS_QUEUED.toLocaleLowerCase() ) { | |
Logger.log(`row=[${i}] is not queued: [${status}]`); | |
continue; | |
} | |
Logger.log(`Sending message on row=[${i}] status=[${status}]`); | |
sheet.getRange(i + 1, statusCol + 1).setValue(STATUS_SENDING); | |
const phoneNumber = data[i][phoneNumberCol]; | |
const message = data[i][messageCol]; | |
sendMessage(phoneNumber.toString(), message.toString(), i); | |
Utilities.sleep(Math.random() * 5000); // wait between 0 and 15 seconds | |
} | |
} | |
function createMessage(nameCell) { | |
const sheet = SpreadsheetApp.getActive().getSheetByName("Params"); | |
const message = sheet.getRange('B2').getValue(); | |
if ( !message ) { | |
SpreadsheetApp.getUi().alert("In Params sheet, cell B2 must have a valid message"); | |
return; | |
} | |
return message.toString().replace("{{primerNombre}}", "{{firstName}}").replace("{{firstName}}", nameCell); | |
} | |
function getAttachmentURL() { | |
const sheet = SpreadsheetApp.getActive().getSheetByName("Params"); | |
const url = sheet.getRange('B3').getValue().toString(); | |
if ( !url || !url.startsWith("http") ) { | |
Logger.log("No attachment to be sent witht this message"); | |
return; | |
} | |
return url.trim(); | |
} | |
function sendMessage(toNumber, message, rowIndex) { | |
var sheet = SpreadsheetApp.getActive().getSheetByName("Queue"); | |
var data = sheet.getDataRange().getValues(); | |
var sendingTimeCol = data[0].indexOf("Sending time"); | |
var statusCol = data[0].indexOf("Status"); | |
try { | |
Logger.log("Sending message to " + toNumber); | |
if (!message) { | |
throw new Error("Message can't be empty"); | |
} | |
if (!toNumber) { | |
throw new Error("Phone number can't be empty"); | |
} | |
if ( !toNumber.startsWith("+") ) { | |
toNumber = "+" + toNumber; | |
} | |
sheet.getRange(i + 1, statusCol + 1).setValue(STATUS_SENDING); | |
sheet.getRange(i + 1, sendingTimeCol + 1).setValue(null); | |
var data = { | |
"to_number": toNumber, | |
"from_number": _2CHAT_SOURCE_PHONE_NUMBER, | |
"text": message | |
} | |
var attachmentURL = getAttachmentURL(); | |
if ( attachmentURL ) { | |
data["url"] = attachmentURL; | |
} | |
var requestOptions = { | |
method: 'POST', | |
headers: { | |
"X-User-API-Key": _2CHAT_API_KEY, | |
"Content-Type": "application/json", | |
'Accept': 'application/json', | |
}, | |
payload: JSON.stringify(data), | |
// redirect: 'follow' | |
}; | |
var response = UrlFetchApp.fetch("https://api.p.2chat.io/open/whatsapp/send-message", requestOptions) | |
var res = JSON.parse(response.getContentText()); | |
sheet.getRange(rowIndex + 1, statusCol + 1).setValue(STATUS_SENT); | |
sheet.getRange(rowIndex + 1, sendingTimeCol + 1).setValue(new Date().toLocaleString()); | |
if ( res?.error ) { | |
sheet.getRange(rowIndex + 1, statusCol + 1).setValue(STATUS_FAILED); | |
sheet.getRange(rowIndex + 1, sendingTimeCol + 1).setValue("❌ " + error?.error_message); | |
} | |
} | |
catch (err) { | |
console.error(err); | |
sheet.getRange(rowIndex + 1, statusCol + 1).setValue(STATUS_FAILED); | |
sheet.getRange(rowIndex + 1, sendingTimeCol + 1).setValue("❌ " + err.message); | |
} | |
} | |
// | |
// triggered to create the dropdown menu | |
// | |
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu('🔷 2Chat') | |
.addItem('🔁 Generate Pending Messages', 'generatePendingMessages') | |
.addItem('✅ Send Queued Messages', 'sendQueuedMessages') | |
.addToUi(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment