Service Requests: With automatic notifications

A little background: This app is created for a customer care team to track all the complaints received from across India for the products they have installed at various places. They have a team of 30 technicians who are spread across 9 zones. Each zone has a zone incharge.
First a complaint is received by the customer care team over the phone and is added to the app. The complaint is then listed in the app with various emblems to track its status.


Each complain has a choice component for the zone and the complain is forwarded to the specific zone incharge for assignment which can be done by opening the complaint and choosing a technician from a choice component.

Once this is done, Messages are automatically sent to the customer and the technician through the google sheets using google scripts in conjunction with an external API built to automate messages.

The complaint can now be seen on the technician’s app by logging in with their email and they can solve the complaint there by entering relevant details.
Watch video
Once this is done, another message is sent to the customer as a confirmation along with the expense amounts.

Note: Messages are triggered every 15 minutes (can be triggered every minute) using google scripts.This is an internal application and hence cannot share the app links here. Sorry for that.

Using Glide, this was a quick and efficient way to create a complete system to optimize an ongoing process.
Thanks for showing interest previously which motivated me to create a post @sardamit @vijay @Hemmings

14 Likes

Thanks, @Manan_Mehta for the description of the app. The app is simple and powerful. I look forward to creating such an app.

1 Like

Would be happy to help if needed!

You can use formula contrate and cotranate’ on your sheet to whatsapp… …

Hi,

Interesting the fact to send messages via Whatsapp from Google Sheets.

Any guide or tutorial to know the API and commands in Google Javascript?

Thanks for your help!

Saludos

1 Like

Hi @gvalero!
This was something that I found out online to integrate whatsapp


But I am not a coder hence I couldn’t create it on my own and had to use some external paid help. I found somebody who could create a tool with a SaaS model and that is what I am currently using. It is a simple API link which needs to have the message to be sent and the mobile number of the receiver and when the link is opened, a whatsapp is automatically sent by a predefined number.
Then all I had to do was to embed the link in my google sheets and write a script to open the pending links every 15 minutes to automatically send the messages.
I could get you in touch with the coder who has done it for me if you need.
Hope I am making sense.
Thanks!

2 Likes

Hi, Manan_Mehta!
Thank you for the example!
Could you please share the Apps-Script code sample for opening URLs.

1 Like

Thanks for the offer @Manan_Mehta!

Let me try by myself, now I have “a lot” of free time :slight_smile:

If I find another workaround I will let you know, very interesting and usefull your app.
Just to know, if you would sell your App to another customer, how much will it cost?

Best regards

Gavp

This is a part of the code to fetch the URL as per some given conditions according to my use case.

if (values[i][0] != ‘’ && values[i][17] == ‘’ ) {
Utilities.sleep(5000);
UrlFetchApp.fetch(data101);
//s15.getRange(“A41”).setValue(data101)
var row = +i + 2
ss.getRange(row, 18).setValue(3);
SpreadsheetApp.flush();
}
And this is triggered every 15 minutes using the trigger function.
You can connect with me personally to understand better!

1 Like

Hey @gvalero!

This is indeed a very productive way to use the free time. :wink:
When it comes to selling the app I would have to know the exact use case of the customer so that I can customize that accordingly and hence I cannot tell you a cost without knowing more details.
We could discuss the use case, the customizations required and the cost on a personal level if you are interested!

Best Regards,
Manan Mehta

1 Like

Hi Manan,
I have a question. You have kept the customer separate from this app. Meaning you have customer call the customer care team, and then the app is used by the customer care team. Any reason why the customer cannot use the app and fill in the details of the problem right in the app? Have you thought about integrating the customer part into the app as well? Because, I wanted to develop an app that has students and tutors using the same app, and students asking questions to tutors.

Hey Vijay,
I could easily give the customer access to the same app in order to add complaints and make them the row owners so that they can see only the complains that they have added. Infact I could allow anybody to add the complain through the same app. But in this case, the company has more than 5000 existing customers who have already been given a customer care phone number and sharing the app with all of them and expecting them to fill in the details wouldn’t be feasible for this specific case. But it can be done.

To get a Quick idea of how it works you can check wfhome.glideapp.io
and assign tasks to your team and get whatsapp notifications when you do that.
The new app (Task Assignment) has been created using what I learnt from this one and is a public app which can be used by anybody.

Hi @Manan_Mehta

Just to confirm something: does your APP send WA messages without any confirmation?

I mean, is there no need to push any “Send” button?

Thanks?.

Yes @gvalero it does send it automatically without any confirmation.

2 Likes

In detail here: WhatsApp notifications

1 Like

Thanks a lot @Manan_Mehta I’m working on it and studying your way. Do you hav an app to test or copy to learn from inside how you didit?

1 Like

Great. Nice Work.
What mechanism are you using to autogenerate service request number? Can you share the formula or script?

1 Like
=arrayformula("P-"&DAY(B2:B)&"-"&MONTH(B2:B)&"/"&LEFT(G2:G,1)&countifs(G2:G,G2:G,row(G2:G),"<="&row(G2:G)))

B2 has a date column and G2 is the name of the technicians.
This ensures unique service request number always and gives information also if I want to open a backdated request. You could tweak this to make it simpler.

1 Like

Thanks a lot.

1 Like