[TUT] Send app data to a web spreadsheet (Google Sheets)

Have you ever had a spreadsheet and thought it’d be really good if I could get data into this from a mobile app. What a great idea, next comes the thought that this is actually much more effort than it seems. Well not any more, this tutorial explains how you can take input in an Android application and easily upload it to a spreadsheet – in this example a google sheets spreadsheet.

If I’m no good at explaining myself here is gif:
spreadsheet_tut_720

We’ll do this in 4 steps:
– Create the spreadsheet on Google Drive
– Create the app to get user input
– Investigate the spreadsheet to find the input hooks
– Post the user data from app to the spreadsheet using Retrofit

First, create your spreadsheet that the data is going to go into. The trick here is to actually create a Google Form.

create_form_1

Within this Google Form you can imagine each question as the title of your column in the spreadsheet. Add as many questions as you want columns (1). Make them of type short answer (2), and make the question the title of your column (3).

create_form_2

Excellent! That is the spreadsheet created we will be coming back to this later so keep the tab open.
Now we create the Android application to accept the user input. This can take the form of any type of questionnaire you want. In this example I have just used one EditText and one CheckBox to get the answer to two questions from the user. Also adding a button to ‘save’ the questionnaire once completed.

Here is the layout:
questions_activity.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  xmlns:support="http://schemas.android.com/apk/res-auto"
  xmlns:tools="http://schemas.android.com/tools"
  android:layout_width="match_parent"
  android:layout_height="match_parent"
  android:orientation="vertical"
  android:paddingBottom="@dimen/activity_vertical_margin"
  android:paddingLeft="@dimen/activity_horizontal_margin"
  android:paddingRight="@dimen/activity_horizontal_margin"
  android:paddingTop="@dimen/activity_vertical_margin"
  tools:context=".QuestionsActivity">

  <TextView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:text="Hello World!" />

  <android.support.design.widget.TextInputLayout
    android:layout_width="match_parent"
    android:layout_height="wrap_content">

    <EditText
      android:id="@+id/question_name_input"
      android:layout_width="match_parent"
      android:layout_height="wrap_content"
      android:hint="@string/question_name_input_hint"
      android:inputType="textAutoComplete" />
  </android.support.design.widget.TextInputLayout>

  <CheckBox
    android:id="@+id/question_cats_input"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="@string/question_cats" />

  <android.support.design.widget.FloatingActionButton
    android:id="@+id/questions_submit_button"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_gravity="bottom|end"
    android:layout_marginRight="15dip"
    android:elevation="6dip"
    android:src="@android:drawable/ic_dialog_email"
    support:pressedTranslationZ="12dip" />

</LinearLayout>

(This uses the design support library to get a nice floating label for the name input and a floating action button for the save action.

Hook up this XML with your Activities onCreate:
QuestionsActivity.java

public class QuestionsActivity extends AppCompatActivity {

    private TextView nameInputField;
    private CheckBox catQuestionInputField;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.questions_activity);

        nameInputField = (TextView) findViewById(R.id.question_name_input);
        catQuestionInputField = (CheckBox) findViewById(R.id.question_cats_input);
        findViewById(R.id.questions_submit_button).setOnClickListener(
            new View.OnClickListener() {
                @Override
                public void onClick(View v) {
                    String nameInput = nameInputField.getText().toString();
                    String catQuestionInput = String.valueOf(catQuestionInputField.isChecked());
                    Log.d("XXX", "Got: " + nameInput + " " + catQuestionInput);
                }
            }
        );
    }
}

Now that we have an android app that can receive input from the user we need to send this to our spreadsheet. We’ll be using Retrofit to do our http calls. Retrofit needs a url to post our data to, so lets go get that. Go back to your Google Form you just created and select the ‘preview’ button from the top bar. Shown on this screenshot:

find_http_ids_1

When the form preview opens, right click and select ‘view page source’. You can now see the HTML for the page. Use your browser search function (CMD+F) and search for “<form” (1), this will get you to the html for your form. The action of the form is the url we will be posting to (2). You then need to look for the <input> tags that have an id starting with entry. each one of these is the ID for one of your spreadsheet columns (3/4).
This screenshot shows the finding of the first ID, note I would need to scroll the page to find the second id:
find_http_ids_2

From this we have:

  • The spreadsheet url:
    https://docs.google.com/forms/d/1HEvFwx10XIMa4ZH6CuQzHFInuCgosaNzttMzU8sMnyg/formResponse
  • The id of the name column: entry.188672219
  • The id of the cat question column: entry.1859048068

Finally we can hook the app and spreadsheet together using retrofit. Sorry that this is not a retrofit tutorial and you can use any http web service client or library that you choose. Here retrofit creates an API call that will POST the data from our users app to our spreadsheet url.

Creating our webservice involves using the url endpoint for the spreadsheet and the ID’s for our columns, each of these ID’s becomes a method parameter.

public interface QuestionsSpreadsheetWebService {
    @POST("1HEvFwx10XIMa4ZH6CuQzHFInuCgosaNzttMzU8sMnyg/formResponse")
    @FormUrlEncoded
    Call<Void> completeQuestionnaire(
        @Field("entry.188672219") String name,
        @Field("entry.1859048068") String answerQuestionCat
    );
}

Retrofit itself is created using the base of the Google Forms url and a call is made with a callback for when it completes.

Retrofit retrofit = new Retrofit.Builder()
            .baseUrl("https://docs.google.com/forms/d/")
            .build();
final QuestionsSpreadsheetWebService spreadsheetWebService = retrofit.create(QuestionsSpreadsheetWebService.class);

        findViewById(R.id.questions_submit_button).setOnClickListener(
            new View.OnClickListener() {
                    String nameInput = nameInputField.getText().toString();
                    String catQuestionInput = String.valueOf(catQuestionInputField.isChecked());
                    Call<Void> completeQuestionnaireCall = spreadsheetWebService.completeQuestionnaire(nameInput, catQuestionInput);
                    completeQuestionnaireCall.enqueue(callCallback);
            }
        );
    }

    private final Callback<Void> callCallback = new Callback<Void>() {
        @Override
        public void onResponse(Response<Void> response) {
            Log.d("XXX", "Submitted. " + response);
        }

        @Override
        public void onFailure(Throwable t) {
            Log.e("XXX", "Failed", t);
        }
    };

One caveat with this approach is, we are paying no attention to what the server sends us after we post the data off. (It actually returns us some HTML that we ignore). Therefore it is not guaranteed that sending the data actually worked (although very likely).

That’s it! Now you have a simple spreadsheet backend to your android app questionnaire. Full source of the example is available here on GitHub.

spreadsheet_tut_720

3 thoughts on “[TUT] Send app data to a web spreadsheet (Google Sheets)

  1. What are the chances that this will stop working ?
    if google changes form api and other stuff and entry id variables are not relevant ?
    is it worth making app for a client based on this

  2. Great post! This made me remember ACRA. They were using Google Forms’ API to write to Google Spreadsheets. But in their website, they say that they will remove the support for Google Forms because the new forms implementation does not have required APIs. I guess they can just use your way of sending data to Google Sheets.

Leave a Reply

Your email address will not be published. Required fields are marked *