To develop a android app that require data to store in server , we need to have server and database in the server. So is there any possibility that I can use Google Sheet as DB and use it in Android app?

 

Yes…! We can use it … here I am presenting series of  tutorial on using Google Sheet As Database in web and Android Apps. Please understand the along with flow. Lets start

 

[maxbutton id=”6″ ]

or

Open this link in your phone browser – http://bit.ly/2wyBjS7

 

Demo Sheet Url – http://bit.ly/2ZbVly5

 

 

 

1.Android Part :

Step 1 : Create new Project ,

• Make sure you are using latest version of android studio.
• Make sure you are connected to internet.
• In app level gradle file check for sdk version you want to use.. sometimes latest version may give some error so, try suing previous if latest creates any issues.

 

Step 2 : Create add_item.xml file and below code. Here for demonstration I am using 2 Edit text and 1 Button. Make sure you use same ID of elements in the AddItem class.

<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="16dp"
        android:layout_marginTop="16dp"
        android:text="Item Name"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginBottom="18dp"
        android:layout_marginStart="16dp"
        android:text="Brand"
        app:layout_constraintBottom_toTopOf="@+id/et_brand"
        app:layout_constraintStart_toStartOf="parent" />

    <EditText
        android:id="@+id/et_item_name"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_marginBottom="50dp"
        android:layout_marginEnd="10dp"
        android:layout_marginStart="10dp"
        android:layout_marginTop="54dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintBottom_toBottomOf="@+id/textView2"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/et_brand"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_marginEnd="10dp"
        android:layout_marginStart="10dp"
        android:layout_marginTop="168dp"
        android:ems="10"
        android:inputType="textPersonName"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <Button
        android:id="@+id/btn_add_item"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="49dp"
        android:text="Add Item"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/et_brand" />
</android.support.constraint.ConstraintLayout>

 

Step 3 : Add Volley Library in the gradle dependency. This is used to handle HTTP REST requests between client – server. Here in our case Android App and Google App script . Find more details on Volley library here https://developer.android.com/training/volley/

 

implementation 'com.android.volley:volley:1.0.0'

It should look like this in buid.gradle file

 

Once Added, SYNC gradle files.

 

 

Step 4: create AddItem.java file. Here on click ButtonAddItem , I am calling addItemsToSheet() functions, which takes inputs from Edit text and pass it as parameters along with HTTP Post request that we are sending to web app that we create in Step 9. Don’t forget to change web app url in stringRequest object(Highlighted in code)

 

package com.ccc.gsheetitem;

import android.app.ProgressDialog;
import android.content.Intent;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.android.volley.AuthFailureError;
import com.android.volley.DefaultRetryPolicy;
import com.android.volley.Request;
import com.android.volley.RequestQueue;
import com.android.volley.Response;
import com.android.volley.RetryPolicy;
import com.android.volley.VolleyError;
import com.android.volley.toolbox.StringRequest;
import com.android.volley.toolbox.Volley;

import java.util.HashMap;
import java.util.Map;

public class AddItem extends AppCompatActivity implements View.OnClickListener {


    EditText editTextItemName,editTextBrand;
    Button buttonAddItem;
    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        setContentView(R.layout.add_item);

        editTextItemName = (EditText)findViewById(R.id.et_item_name);
        editTextBrand = (EditText)findViewById(R.id.et_brand);

        buttonAddItem = (Button)findViewById(R.id.btn_add_item);
        buttonAddItem.setOnClickListener(this);


    }

    //This is the part where data is transafeered from Your Android phone to Sheet by using HTTP Rest API calls

    private void   addItemToSheet() {

        final ProgressDialog loading = ProgressDialog.show(this,"Adding Item","Please wait");
        final String name = editTextItemName.getText().toString().trim();
        final String brand = editTextBrand.getText().toString().trim();




        StringRequest stringRequest = new StringRequest(Request.Method.POST, "Add Your Web App URL",
                new Response.Listener<String>() {
                    @Override
                    public void onResponse(String response) {

                        loading.dismiss();
                        Toast.makeText(AddItem.this,response,Toast.LENGTH_LONG).show();
                        Intent intent = new Intent(getApplicationContext(),MainActivity.class);
                        startActivity(intent);

                    }
                },
                new Response.ErrorListener() {
                    @Override
                    public void onErrorResponse(VolleyError error) {

                    }
                }
        ) {
            @Override
            protected Map<String, String> getParams() {
                Map<String, String> parmas = new HashMap<>();

                //here we pass params
                parmas.put("action","addItem");
                parmas.put("itemName",name);
                parmas.put("brand",brand);

                return parmas;
            }
        };

        int socketTimeOut = 50000;// u can change this .. here it is 50 seconds

        RetryPolicy retryPolicy = new DefaultRetryPolicy(socketTimeOut, 0, DefaultRetryPolicy.DEFAULT_BACKOFF_MULT);
        stringRequest.setRetryPolicy(retryPolicy);

        RequestQueue queue = Volley.newRequestQueue(this);

        queue.add(stringRequest);


    }




    @Override
    public void onClick(View v) {

        if(v==buttonAddItem){
            addItemToSheet();

            //Define what to do when button is clicked
        }
    }
}

 

 

Step 5: Add button in  activity_home.xml for Demonsatrion

 

<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/btn_add_item"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="130dp"
        android:layout_marginTop="194dp"
        android:text="Add Item"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintTop_toTopOf="parent" />
</android.support.constraint.ConstraintLayout>

 

 

Step 6: Add following code in ActivityMain.java . Here on click Add Item Button , screen i.e Activity  is navigated to AddItem class using Intent object.

package com.ccc.gsheetitem;

import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

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

        buttonAddItem = (Button)findViewById(R.id.btn_add_item);
        buttonAddItem.setOnClickListener(this);

    }

    @Override
    public void onClick(View v) {

        if(v==buttonAddItem){

            Intent intent = new Intent(getApplicationContext(),AddItem.class);
            startActivity(intent);
        }

    }
}

 

 

 

Step 7: Add AddItem.class and Internet permission to manifest.xml file

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.ccc.gsheetitemdemo2">

    <uses-permission android:name="android.permission.INTERNET"/>

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
        <activity android:name=".AddItem"/>
    </application>

</manifest>

 

2. Back End – AppScript Part

 

Step 8: Create new Spread Sheet and 4 columns.

Date, Id , Item Name , Brand

Change sheet name form Sheet1 to Items

 

 

Step 9: Create new AppScript and below code. Please understand the logic

• Change the Spread Sheet URL to your Spread Sheet URL that you created in step 8
• Make sure your parameters match with what ever you are sending from Android code.

Example e.parameter.brand  should match with the params.put(“brand“,brand)

 

• Deploy as web App, make sure even anonymous user can access the web App

note : if you make any changes in the Appscript don’t update with the same version while deploying always use new version.

 

 

var ss = SpreadsheetApp.openByUrl("Add Your Spread Sheet URL here");

var sheet = ss.getSheetByName('Items'); // be very careful ... it is the sheet name .. so it should match 


function doPost(e){
var action = e.parameter.action;

if(action == 'addItem'){
  return addItem(e);

}

}





function addItem(e){

var date =  new Date();

var id  =  "Item"+sheet.getLastRow(); // Item1

var itemName = e.parameter.itemName;

var brand = e.parameter.brand;

sheet.appendRow([date,id,itemName,brand]);

   return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT);



}

 

Step 10 : Test it in Post man Tool. Once it works then , copy the web App URL and paste it in the URL – section used in step 4

 

Step11 : Run and test it in real device or emulator.