2

We are making extensive use of Google Sheets' HYPERLINK formula combined with mailto links to create links in cells that will dynamically generate emails. For example:

=HYPERLINK(
    "mailto:[email protected]" &
        "[email protected]" &
        "&subject=Test email" &
        "&body=This is a test email.",
    "Send email"
)

That will create linked text that, when clicked, will create an email in your default email client. Naturally we can easily make that dynamic by replacing the hard-coded values with cell references:

=HYPERLINK(
    "mailto:" & $C2 &
        "?cc=" & $D2 &
        "&subject=" & $E2 &
        "&body=" & $F2,
    "Send email"
)

We were hoping to do the same thing with sms links, but none of the following result in a clickable link:

=HYPERLINK("sms:", "Send SMS")
=HYPERLINK("sms:19875550198", "Send SMS")
=HYPERLINK("sms:+19875550198", "Send SMS")
=HYPERLINK("sms://19875550198", "Send SMS")
=HYPERLINK("sms://+19875550198", "Send SMS")

So it seems that the HYPERLINK formula doesn't recognize sms links the way it recognizes mailto links.

Is there a way to do this in Google Sheets?

Improve this question

2 Answers 2

Reset to default
4

From the documentation for HYPERLINK: "Only certain link types are allowed. http://, https://, mailto:, aim:, ftp://, gopher://, telnet://, and news:// are permitted; others are explicitly forbidden (my emphasis)."

As you can see, sms is not listed so one might reasonably assume that it is explicitly forbidden

Why aim: is included, I don't know, since AIM was discontinued in 2017.

A quick Google will yield any number of Add-ons and/or vendor services that will enable sms, but that is outside the scope of webapps.

Improve this answer
3

Tedinoz's answer points out which link types are allowed within HYPERLINK, which made me realize that I can use a permitted https:// link to a simple HTML page with some JavaScript that forwards to an sms link.

Here's the code of the HTML page:

<!doctype html>

<html>
    <head>
        <title>HTTPS to SMS</title>
    </head>
    <body>
        <script>
            // Based on https://stackoverflow.com/a/901144/1652620
            const urlSearchParams = new URLSearchParams(window.location.search);
            const params = Object.fromEntries(urlSearchParams.entries());

            function getParameterByName(name, url = window.location.href) {
                name = name.replace(/[\[\]]/g, '\\$&');
                var regex = new RegExp('[?&]' + name + '(=([^&#]*)|&|#|$)'),
                    results = regex.exec(url);
                if (!results) return null;
                if (!results[2]) return '';
                return decodeURIComponent(results[2].replace(/\+/g, ' '));
            }

            var tel = getParameterByName('tel');
            var body = getParameterByName('body');

            // Based on https://stackoverflow.com/a/4745622/1652620
            if (tel != "" && tel != null) {
                window.location = "sms://+" + tel + ";?&body=" + body;
            }
        </script>
    </body>
</html>

So say you save that as sms.html and upload it to https://www.someurl.com/sms.html. You then just add a query string with values for tel and optionally body, which can be generated as needed in Google Sheets.

The final formula in Google Sheets would then look something like this:

=HYPERLINK("https://www.someurl.com/sms.html?tel=19875550198&body=This%20is%20a%20test%20message.", "Send SMS")

Or, with cell references:

=HYPERLINK("https://www.someurl.com/sms.html?tel=" & A2 & "&body=" & B2, "Send SMS")

where A2 contains the phone number and B2 contains the URL-encoded body text.

It's not quite the elegant in-app solution I was hoping for, but it gets the job done, if indirectly.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.