Problem this solves:
Building a Glide app with a contact list that includes a WhatsApp link on each contact's profile.
This is not specific to Glide, but can be used for any app that relies on a Google sheet for it's backend data source. Below is an example of the output.
Step 1: How to create a WhatsApp link
Every WhatsApp number has a URL. And the URL is actually relatively simple to create. the URL begins with "https://wa.me/" followed by the full international phone number with area code. e.g. "+15551234123" for USA or Canada, or "+495551234123" for Germany
So to create the WhatsApp link all we need from the user is their complete phone number. We will capture the user's phone number in our sheet and then use the CONCAT function to combine it with the static part of the URL that we already know.
Note: For our case we need to use CONCAT instead of CONCATENATE. I'll explain later.
=CONCAT("https://wa.me/",B2)
Step 2: Dynamically creating new WhatsApp links as new data comes in
So once we know the components of the WhatsApp link, we will need this new cell to dynamically fill whenever a new row is added. Sure, we could just copy and paste the formula down throughout our table, but that would not only cause a bit of a mess for the app, but it also means we need to manually update it if our user list gets longer than the number of cells we've pasted to.
For this we will use the ARRAYFORMULA
Tangent: How does ARRAYFORMULA work
ARRAYFORMULA is an interesting tool that allows you to enter a formula once and have it auto populate throughout an entire array.
A great use case of this is if you have a table of user data with, for example, private data in cells A through C and the data that you actually want publicly available in the app in cells D through G. You can create another tab for the public data, and simply use the ARRAYFORMULA in the first cell and have it
Super basic example in this screenshot is me duplicating everything that is in Columns A through B into column L through M
=ARRAYFORMULA(A:B)
Back to our WhatsApp project, we're still on step 2 here 🙂
We're going to wrap our CONCAT into an ARRAYFORMULA so that it can auto populate the rest of the columns without us manually pasting the formula.
=ARRAYFORMULA(CONCAT("https://wa.me/",B2))
Note that in the above, all I did was wrap the existing CONCAT into the ARRAYFORMULA so our result is kinda useless. We need to update our CONCAT so that it references an array instead of just a single cell. Let's do that first:
=ARRAYFORMULA(CONCAT("https://wa.me/",B2:B))
Now we're getting somewhere. The entire column is now being populated with our CONCAT formula. It's not perfect though, because this means we have a ton of bad URLs that could muddy our app data and create tons of unused rows in the app DB.
Let's fix this.
Step 3: Stopping the ARRAYFORMULA from creating useless content
We want to tell the array to return nothing when we don't have a contact number. For this we'll use an IF statement to use the CONCAT if we have a contact number while ignoring it and returning nothing if we don't have a contact number.
Here's an example of an IF formula in action
=IF(B2=0,"ZERO","NOT ZERO")
Let's wrap our CONCAT in the IF statement so that if there is no contact number it ignores the CONCAT formula and just returns nothing, while if there is a contact number then it runs the CONCAT to return our URL.
Here's what that looks like without the ARRAYFORMULA First:
=IF(B2=0,,CONCAT("https://wa.me/",B2))
Let's now finish this up by wrapping the whole thing back into our ARRAYFORMULA. Don't forget to update the references in both the IF and the CONCAT to be ranges so we get a complete formula like this:
=ARRAYFORMULA(IF(B2:B=0,,CONCAT("https://wa.me/",B2:B)))
And that's it. Now whenever a new contact number gets added to our database, a new WhatsApp link will be automagically generated that we can simultaneously surface back to our app.
Note on open ended ranges:
You may have noticed that when I converted my single-cell reference B2 to a range I used B2:B instead of B:B . This is because our array is going to pull in everything starting at the beginning of the range we define. So if we don't tell it to start at row 2, it will start at row 1 and return an error.
Outtake 1: ISBLANK
A more elegant way of checking wether we had a contact number would have been to use the ISBLANK Formula.
In our IF statement we actually asked "if the cell is equal to zero then return nothing"
What ISBLANK does is actually check if the cell is blank and return "True" if it's blank or "False" if it's not blank. Using this formula inside of our IF statement would have instead been asking "if the cell is blank then return nothing".
It's a subtle difference but an important one depending on your use case.
=ISBLANK(B2)
Outtake 2: CONCATENATE
So I mentioned way up at the beginning that we specifically wanted to use CONCAT instead of CONCATENATE. This is because CONCAT returns specifically two values while CONCATENATE returns as many as the formula sets out.
Since we ended up using an array formula, it means we are passing in an array into the concatenate, which in turn means we are concatenating an array of data.
Here's what this ends up looking like
=ARRAYFORMULA(IF(B2:B=0,,CONCATENATE("https://wa.me/",B2:B)))
See what happened there. Because CONCATENATE can take in a lot of values, it does, and simply concatenates all of the values from the entire array every time.