Mesage Template

AppSheet AppSheet Q&A
1

Hello,

im trying to make action that will send pre-defined message to customer over WhatsApp.

Problem im facing is when i try to substitute template message with customer specific information. I know that i need multiple substitute expressions. Problem for me is that SUBSTITUTE breaks formatting.

does anyone have some ideas how to circle around this problem?

2

I don’t see why you’d need to use SUBSTITUTE().

How does SUBSTITUTE() break formatting?

Please post a screenshot of the entire expression you’re using.

1 Like
3

I’m not so sure about that

1 Like
4

I have predefined message saved in LongText type field in Google sheet, this message is something like:

Hello <>,

in few days you will arrive at our <> for your vacation. Here is some final information before your arrival on <>.

so i need to substitute information in <> with actual values i want to use. Because i might change some data later and dont want to have it hardcoded

5

What formatting do you have in the text in your app that you’re referring to? WhatsApp doesn’t support formatted text anyway, although it does provide basic markup-based formatting.

2 Likes
6

Text formatting within a spreadsheet cell is entirely ignored by AppSheet. So, SUBSTITUTE() or not, the formatting will be lost.

1 Like
7

Text formatting is not ignored as when i read text without SUBSTITUTE() i have predefined text and format in WhatApp

Is there option to Unicode text before passing it to SUBSTITUTE?

8

Im talking about only basic next/new row formating

9

SOLUTION:

  1. use: ENCODEURL()

  2. then SUBSTITUTE() with url encoded values that will be substituded.

my example:

CONCATENATE("https://api.whatsapp.com/send?phone=", [Contact uuid].[Phone No.],"&text=", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ENCODEURL(SELECT(Messages[English], ([Name] = "CheckIn"))), "%3C%3CArrival%3E%3E", TEXT([Arrival],"DD.MM.YYYY") ), "%3C%3CCheckInTime%3E%3E", TEXT([App Name].[Check In Time],"HH:MM") ), "%3C%3CSecurity%20Deposit%3E%3E", [App Name].[Security Deposit] ), "%3C%3CGoogle%20Map%20Link%3E%3E", [App Name].[Google Map Link] ), "%3C%3CGuest%20Name%3E%3E", [Contact uuid].[Guest Name] ), "%3C%3CApp%20Name%3E%3E", [App Name].[App Name] ))
1 Like
10

So, apparently, you have a “messages” table, that’s why you are using your own workaround (with SUBSTITUTE()) to make it work.

CONCATENATE(
  "https://api.whatsapp.com/send?phone=",
  [Contactuuid].[PhoneNo.],
  "&text=",
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              ENCODEURL(
                SELECT(
                  Messages[English],
                  ([Name]="CheckIn")
                )
              ),
              "<<Arrival>>",TEXT([Arrival],"DD.MM.YYYY")
            ),
            "<<CheckInTime>>",TEXT([AppName].[CheckInTime],"HH:MM")
          ),
          "<<Security Deposit>>",[AppName].[SecurityDeposit]
        ),
        "<<Google Map Link>>",[AppName].[GoogleMapLink]
      ),
      "<<Guest Name>>",[Contactuuid].[GuestName]
    ),
    "<<App Name>>",[AppName].[AppName]
  )
)

Next time would be great to know this kind of details.

PS: It’s not a bad idea though, just somewhat cumbersome

1 Like
11

“<>” should be “%3C%3CArrival%3E%3E” due to ENCODEURL

What now i would love it that

Messages[English] could be dinamic depending on contact language. Example Messages[English] or Messages[German]