How to send Messages from google sheets to WhatsApp using Google App script?

Question

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.

Answer 1

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)
}

}