Create an Apps Script web app to collect phone numbers to text message customers updates đź“ź
PART 2 of 2
In my prior article I share how to use a Google Sheet to send text messages via Twilio (which is the foundation). In this article I elaborate on how to create a basic interface using a standalone Apps Script (based on JavaScript and HTML) in order to collect phone numbers on your website, email signature, or wherever you can embed a URL of this web app and have the phone numbers input, delivered to your Google Sheet.
Web app vs. Google Forms vs. Google Cloud hosting
I’d like to first call out, that Google Forms is my go-to interface when collecting data as it has zero dependencies on teams having a technical background in HTML, it autoscales the volume of users that decide to all fill it out all at once (only limit is the 5 million rows per Sheet), and it has built-in analytics.
However if you wish to further customize the look and feel, and the interface will not need to serve a high volume of traffic, and of course you do not mind maintaining HTML code, then building a web app using an Apps Script is the way to go as it can serve up to 30 simultaneous submissions presently.
If you do build it for a high volume with a custom look and feel, I would instead recommend using App Engine and Cloud Functions to build a frontend, and BigQuery or DataStore as databases rather than a Sheet from the Google Cloud Platform, which have a free tier for prototyping apps.
Prerequisite
- Access to creating a Google Sheet.
Try it
1. Create the web app (an unbound script)
- Visit Script.google.com > New script
- Replace the existing code (if any) with the code in the Code.gs file
- Then save (File > Save).
- Create another file by clicking File > New > HTML file> call it “Page.html” and paste the code from the Page.html tab here.
- Then save (File > Save).
2. Create new Google Sheet and copy it’s URL.
Note: Entering “sheet.new” in your browser is a shortcut to create sheet.
3. Paste your sheet’s URL at the top of the web app’s code in the Code.gs file.
Note: if you made a copy of the spreadsheet I shared in my first article to send text messages from there, paste that spreadsheet’s URL at the top of the code of the web app.
4. Publish web application
- Click Publish > Deploy as a web app > Deploy (leave all the default options selected).
- You will be given a URL, copy it and paste in a new tab in your browser.
- This will bring up the web app that looks like this (I did not make it public so I am sharing the screenshot):
5. Enter data via the web app
- Visit the tab with the web app and enter your personal phone number and click “Submit”, the one you used to verify your account with Twilio (this is not the phone number you bought from Twilio to send an SMS).
- Return to the spreadsheet and you will see your phone number and the time you entered it logged in the sheet.
6. Tie it to Google Sheet that can send text messages
- Copy this Google Sheet and paste the URL of your copy in the web app’s code (step 3)
- Please review my prior article for instructions.
