Google Docs Google Script

Get Notified By Google When Somebody Contacts You

Use of contact form is very common these days. Every blog and websites interacts with their customers or visitors via contact forms. They are very popular because of their ease of use. Visitors don’t have to login to their emails, just by staying on your web page they can write and send their views to you.

When it is the matter of contact form, we should not forget the contribution of Google spreadsheet and Google forms. Even in this blog we use Google form as our contact form. When people writes you via Google form, all their responses gets automatically stored in a spreadsheet. Regularly you need to open the spreadsheet to check for new submissions. But now we added some flavor to this traditional process.

“Now you will get an SMS in your mobile when some body contacts you via Google form.”

How to do this

  • Make a new copy of this spreadsheet (Go to File–>Make a copy..)
  • Go to Tools–>Create a form
  • Now create the contact form as per your requirement and save it.
  • After you save the form, the columns will appear in the spreadsheet.
  • Now register your mobile in Google Calendar. If you do not know how to register then visit this page. If you have already registered, then skip this step.
  • Now go to Tools–>Script Editor…
  • In the script editor go to Resources–>All your triggers…
  • Create a new trigger as shown in below figure, authorize it & save it.
  • Now you can go to Form–>Go to live form to verify whether everything working fine or not

Can I customize the SMS

Definitely you can customize the content of the SMS as per your requirement. You can customize two parameters:
  1. An optional message
  2. Any column submitted by user
(1) An optional message
In the script editor you can see a parameter as follow 

var OPTIONAL_MESSAGE = “Hey, You got a comment in your blog.”;

You can modify this message as you wish. If you do not want any message then keep it as follow


(2) Any column from the spreadsheet
In the script editor you can see another paramter as follow

var SMS_COLUMN = [“B”];

By default its value is “B”. It means the value of column “B” from the spreadsheet will be sent in the SMS. You can even add multiple columns. For example if you want column “B”, “D” and “E” to be in the SMS then mention the parameter as follow

var SMS_COLUMN = [“B”,”D”,”E”];

Lets see an Example: Suppose I want to get an SMS “Hi, Hari” and then the content of column “B” and “C”, then I should write it as follow

var OPTIONAL_MESSAGE = “Hi, Hari”;
var SMS_COLUMN = [“B”,”C”];

How to know an SMS is sent from the spreadsheet

When the form is submitted you will get an SMS within 1 minute. In case you did not receive any SMS, then open the spreadsheet and verify the color of the first column. If it is green that means SMS has been sent but you did not receive it in your mobile, it might be your mobile carrier problem. If the color is not green that means Google Script could not track the spreadsheet. Submit another response and verify again.

Note:  As green color is used to track the status of the SMS, hence do not use this color anywhere in the spreadsheet.