I don't seem to find any tutorials or a reliable free resource to integrate WhatsApp messages from the Google App script. I've found add-ons but they are paid after a certain nu ber of messages sent. I could use some help with the App script Integration. If anyone can share any resources that can help me out with it would be great.
I think I was able to find the particular reference about using Sheets, App script and Whatsapp API, however it was in Spanish. I was able to translate it and you can do it too, feel free to review it here
I edited the code and added the comments translating the important information
function addSheet() {
const spreadsheeturl = '##URL DE LA HOJA DE CALCULO##' //URL of the Sheet
const sheetname = '##NOMBRE DE LA HOJA DONDE TENEMOS ANOTADOS LOS TELÉFONOS Y LOS MENSAJES A ENVIAR##' //Name of the Sheet workbook that would be used with the phone numbers and where the messages would be sent
const columnatelefono = '##LETRA DE LA COLUMNA EN LA QUE VAS A ESCRIBIR EL TELÉFONO AL QUE ENVIAR EL MENSAJE POR WHATSAPP##' // Letter of the column where you would write the phone number
const columnamensaje = '##LETRA DE LA COLUMNA EN LA QUE VAS A ESCRIBIR EL MENSAJE A ENVIAR POR WHATSAPP##' // Letter of the column where you would write the message to be sent
const columnaenlace = '##LETRA DE LA COLUMNA EN LA QUE DESEAS QUE APAREZCA EL ENLACE DE WHATSAPP##' // Letter of the column where you want to show the whatsapp link.
const columnas = {
A: 0, B: 1, C: 2, D: 3,
E: 4, F: 5, G: 6, H: 7,
I: 8, J: 9, K: 10, L: 11,
M: 12, N: 13, O: 14, P: 15,
Q: 16, R: 17, S: 18, T: 19,
U: 20, V: 21, W: 22, X: 23,
Y: 24, Z: 25, a: 0, b: 1,
c: 2, d: 3, e: 4, f: 5,
g: 6, h: 7, i: 8, j: 9,
k: 10, l: 11, m: 12, n: 13,
o: 14, p: 15, q: 16, r: 17,
s: 18, t: 19, u: 20, v: 21,
w: 22, x: 23, y: 24, z: 25
}
const ss = SpreadsheetApp.openByUrl(spreadsheeturl)
const sh = ss.getSheetByName(sheetname)
const shlastrow = sh.getLastRow()
const shlastcolumn = sh.getLastColumn()
const shdatarange = sh.getRange(1, 1, shlastrow, shlastcolumn)
const shdata = shdatarange.getValues()
const columnatelefononumber = columnas[columnatelefono]
const columnamensajenumber = columnas[columnamensaje]
const columnaenlacenumber = columnas[columnaenlace]
function generaEnlacesWhatsapp() {
for (var i in shdata) {
shdata[i][columnaenlacenumber] = 'https://api.whatsapp.com/send?phone=' + shdata[i][columnatelefononumber] + '&text=' + encodeURI(shdata[i][columnamensajenumber])
}
sh.getRange(1, 1, shlastrow, shdata[i].length).setValues(shdata)
SpreadsheetApp.flush()
Utilities.sleep(4000)
Logger.log('a otra cosa') //this means something else
const newshlastrow = sh.getLastRow()
const newshlastcolumn = sh.getLastColumn()
const newshdatarange = sh.getRange(1, 1, newshlastrow, newshlastcolumn)
const newshdata = newshdatarange.getValues()
Logger.log(newshdata)
for (const j in newshdata) {
for (const k in newshdata[j]) {
if (newshdata[j][k] == null || newshdata[j][k] == 'null' || newshdata[j][k] == 'NOT_FOUND') {
newshdata[j][k] = ''
}
}
}
Logger.log(newshdata)
sh.getRange(1, 1, shlastrow, newshdata[i].length).setValues(newshdata)
}
}