How To Use Google Sheet As Database for Android App -3 Read Operation

This is continuation to first part How To Use Google Sheet As Database for Android App -1 Insert Operation .

 

Here I am going to demonstrate on displaying Google sheet content in Android list view.

 

Please watch below video along with tutorial to avoid errors, don’t skip any part.

 

 

 

 

1.Android Part :

Step 1 : Add 2 xml files

  1. Add list_item.xml , here I am adding simple list view
<?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">

    <ListView
        android:id="@+id/lv_items"
        android:layout_width="0dp"
        android:layout_height="0dp"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />
</android.support.constraint.ConstraintLayout>

2. Add list_item_row, here we define what each list element should display.

<?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"
    android:background="#e8eaea"
    tools:layout_editor_absoluteX="0dp"
    tools:layout_editor_absoluteY="81dp">

    <LinearLayout
        android:id="@+id/linearLayout"
        android:layout_width="match_parent"
        android:layout_height="100dp"
        android:layout_marginEnd="8dp"
        android:layout_marginStart="8dp"
        android:layout_marginTop="8dp"
        android:background="#fff"
        android:orientation="horizontal"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent">

        <android.support.constraint.ConstraintLayout
            android:id="@+id/constraintLayout"
            android:layout_width="match_parent"
            android:layout_height="match_parent"

            app:layout_constraintBottom_toTopOf="@+id/tv_brand"
            app:layout_constraintTop_toTopOf="@+id/tv_brand"
            tools:layout_editor_absoluteX="5dp">

            <TextView
                android:id="@+id/tv_item_name"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginStart="33dp"
                android:text="TextView"
                android:textColor="#000"
                android:textSize="18dp"
                app:layout_constraintBottom_toTopOf="@+id/tv_brand"
                app:layout_constraintStart_toStartOf="parent"
                app:layout_constraintTop_toTopOf="parent" />

            <TextView
                android:id="@+id/tv_brand"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginBottom="14dp"
                android:text="TextView"
                android:textSize="15dp"
                app:layout_constraintBottom_toBottomOf="parent"
                app:layout_constraintStart_toStartOf="@+id/tv_item_name"
                app:layout_constraintTop_toBottomOf="@+id/tv_item_name" />

            <TextView
                android:id="@+id/textView3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginEnd="18dp"
                android:text="₹"
                android:textColor="#000"
                android:textSize="18dp"
                app:layout_constraintBottom_toBottomOf="parent"
                app:layout_constraintEnd_toStartOf="@+id/tv_price"
                app:layout_constraintTop_toTopOf="parent" />

            <TextView
                android:id="@+id/tv_price"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_marginEnd="51dp"
                android:text="TextView"
                android:textColor="#000"
                android:textSize="18dp"
                app:layout_constraintBaseline_toBaselineOf="@+id/textView3"
                app:layout_constraintEnd_toEndOf="parent" />
        </android.support.constraint.ConstraintLayout>
    </LinearLayout>

</android.support.constraint.ConstraintLayout>

 

 

Step 2 : Add ListItem.java file which

  • send request to server to get Items
  • Parse JSON responce on recieving it from server, webApp (AppScript)
  • Add parsed JSON object to list. Here Items in our case
  • then add list to Adapter
  • Finally bind adapter with list view to display content in list view

 

package com.ccc.gsheetitemdemo2;

import android.app.ProgressDialog;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.widget.Adapter;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;

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 org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

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

public class ListItem extends AppCompatActivity {


    ListView listView;
    ListAdapter adapter;
    ProgressDialog loading;

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

        listView = (ListView) findViewById(R.id.lv_items);

        getItems();

    }


    private void getItems() {

        loading =  ProgressDialog.show(this,"Loading","please wait",false,true);

        StringRequest stringRequest = new StringRequest(Request.Method.GET, "Your App Script web app URL along with action parameter",
                new Response.Listener<String>() {
                    @Override
                    public void onResponse(String response) {
                        parseItems(response);
                    }
                },

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

                    }
                }
        );

        int socketTimeOut = 50000;
        RetryPolicy policy = new DefaultRetryPolicy(socketTimeOut, 0, DefaultRetryPolicy.DEFAULT_BACKOFF_MULT);

        stringRequest.setRetryPolicy(policy);

        RequestQueue queue = Volley.newRequestQueue(this);
        queue.add(stringRequest);

    }


    private void parseItems(String jsonResposnce) {

        ArrayList<HashMap<String, String>> list = new ArrayList<>();

        try {
            JSONObject jobj = new JSONObject(jsonResposnce);
            JSONArray jarray = jobj.getJSONArray("items");


            for (int i = 0; i < jarray.length(); i++) {

                JSONObject jo = jarray.getJSONObject(i);

                String itemName = jo.getString("itemName");
                String brand = jo.getString("brand");
                String price = jo.getString("price");


                HashMap<String, String> item = new HashMap<>();
                item.put("itemName", itemName);
                item.put("brand", brand);
                item.put("price",price);

                list.add(item);


            }
        } catch (JSONException e) {
            e.printStackTrace();
        }


        adapter = new SimpleAdapter(this,list,R.layout.list_item_row,
                new String[]{"itemName","brand","price"},new int[]{R.id.tv_item_name,R.id.tv_brand,R.id.tv_price});


        listView.setAdapter(adapter);
        loading.dismiss();
    }


}

Step 3 : Add Button in activity_main.xml for demonstarion

<?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" />

    <Button
        android:id="@+id/btn_list_items"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginEnd="8dp"
        android:layout_marginTop="50dp"
        android:text="List Items"
        app:layout_constraintEnd_toEndOf="@+id/btn_add_item"
        app:layout_constraintTop_toBottomOf="@+id/btn_add_item" />
</android.support.constraint.ConstraintLayout>

Step 4 : Update MainActivity.java as we added new button List Items

package com.ccc.gsheetitemdemo2;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;



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,buttonListItem;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

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

    }

    @Override
    public void onClick(View v) {

        if(v==buttonAddItem){

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

        if(v==buttonListItem){

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


    }
}

Step 5: Update Manifest file, add ListItem activity to manifest

<?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"/>
        <activity android:name=".ListItem"/>
    </application>


</manifest>

Back End Google App Script Part

 

Step 6 : Add getItems() function to appscript that we used in the previous tutorial

 

function getItems(e){
  
  var records={};
 
  var rows = sheet.getRange(2, 1, sheet.getLastRow() - 1,sheet.getLastColumn()).getValues();
      data = [];

  for (var r = 0, l = rows.length; r < l; r++) {
    var row     = rows[r],
        record  = {};
    record['itemName'] = row[2];
    record['brand']=row[3];
    record['price']=row[4];
    
    data.push(record);
    
   }
  records.items = data;
  var result=JSON.stringify(records);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

 

Complete App Script

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

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 doGet(e){

var action = e.parameter.action;

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

  }
  
  }


function addItem(e){

var date =  new Date();

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

var itemName = e.parameter.itemName;

var brand = e.parameter.brand;

var price = e.parameter.price;

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

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

}


function getItems(e){
  
  var records={};
 
  var rows = sheet.getRange(2, 1, sheet.getLastRow() - 1,sheet.getLastColumn()).getValues();
      data = [];

  for (var r = 0, l = rows.length; r < l; r++) {
    var row     = rows[r],
        record  = {};
    record['itemName'] = row[2];
    record['brand']=row[3];
    record['price']=row[4];
    
    data.push(record);
    
   }
  records.items = data;
  var result=JSON.stringify(records);
  return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}

 

Deploy with new Version.. Dont update Web App with previous version. Always deploy with new verison on every changes you make.

 

Update web app URL along with Action parameter in ListItem.java file in step 2

 

Your webapp url should look like this

 

https://script.google.com/macros/s/AKfycbzzZxxxxxxxxxxxxxxxxxxxxxxrg4KQjS9d0/exec?action=getItems

 

Step 7 : Run And check the Application

 

 

If you face any difficulty cooment below or follow up in YouTube