POST data to Google Docs sheet via app

By | February 22, 2015

Many a times you want to send data from your app to a backend. To send and store all that data, you need a database and a web service. Creating the web service and a database can cost you money and time. It’ll also most probably give you maintenance issues if you haven’t done it before.

A classic example can be a “contact us” page in your app. It can be easily done if your app already has a backend database with a proper communication channel, but what if this is the only data that your app will be sending(like our internetusage app)? There is no need to put so much time and effort into creating a web service for that. Cheapest (cheap as in free!) and easiest way is to use a Google docs spreadsheet to store all that data.

Here’s how you can create a Google docs spreadsheet to store the data and programmatically update it for a contact us form.

CREATE A GOOGLE FORM

First thing you need to do is login to drive.google.com and create a new “Google Form”

new_google_forms

A new window will open. Give the form a name, let’s call it Feedback form.

Now we need to enter details about the data we want to store. We need the email address, subject and message fields for our contact us page.

form_entries

Enter details for each field and click on Add item to add new item.

Once you are done with all the fields, click on the “View live form” button present just above the form. You should be navigated to a new page with the form looking something like this:-

view_live_form

This is the most important page. You’ll need to note down a few things from this page. First thing to note is the URL for the form. For us, it is https://docs.google.com/forms/d/1dcq0Fgt5dSUoIiH119K5-eF-XMGiq3eVPt97Aocspx4/viewform

This URL needs to be converted to be used for sending data from code. The conversion very easy, just replace “viewform” in the URL to “formResponse”. Thus, our POST URL becomes https://docs.google.com/forms/d/1dcq0Fgt5dSUoIiH119K5-eF-XMGiq3eVPt97Aocspx4/formResponse

 

Now comes the tricky part (only if you have never done web development). To send data for these fields via code, we need to make a POST request with the data attached as key-value pair. Values being the data entered by your user in the app and the keys being the ids of input fields on the form.

To get the keys, right click on each TextBox and select “Inspect Element”

 

inspect_element

 

Clicking on inspect element will show the HTML behind this page and focus on the HTML line used to create this particular text box. You need to copy the value of the id field in that line, in our case it is entry_313227136

 

element_id

 

Similarly, find ids for all other elements. For us, the ids are –

Email – entry_313227136

Subject – entry_1834657755

Message – entry_1110317093

 

SEND DATA THROUGH CODE

Once we have the URL and keys for all fields, we just need to send a POST request from our code to enter values in the spreadsheet which will hold all the data for the form we created above.

Here’s the code to upload data for an android application, it can be easily converted for WindowsPhone and iOS apps.

 

 

Above, an activity ContactActivity is the “Contact Us” page in the android app. This Activity has 3 EditText controls, once for each Email, Subject and Message.

The http POST request is made inside an AsyncTask which creates a separate thread each time execute() method is called. We pass the URL and data to the AsyncTask which in turn uses Square’s OkHttp library to create and send the POST request. There are various benefits of using the OkHttp library, but it is not necessary to use it. Another easy and efficient way is to use the HttpRequest class specified in this StackOverflow answer(usage present in above code under multiline comments).

GET NOTIFIED FOR CHANGES

We have successfully created a Google form, associated it with a spreadsheet (done automatically as soon as you create the form) and wrote an app to send data to the spreadsheet from code. Now, the only thing remaining for us to do is to know when a user sends us feedback using the “contact us” form.

To achieve this, go to the page where you were editing your form, and click on “Responses” button, select “View responses” . This will open the spreadsheet which will store all the data you send from your app. Notice that Google automatically adds an extra “Timestamp” field to your sheet.

view_responses

To get an email notification when new responses are entered in this sheet, we need to change the notification rules for this spreadsheet. Click on Tools->Notification rules

notification_rules

Select Notify me at “your email” when “A user submits a form” and Notify me with “Email – straight away” and then click on save.

edit_rules

And you are done!

Now anytime a user opens your app and sends you some feedback via your contact us page, the response will be saved in “Feedback form (responses)” and you’ll get an email notification for the same. You can always see these results anytime by logging in to drive.google.com or docs.google.com

Loading Disqus Comments ...
Loading Facebook Comments ...