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.
or
Open this link in your phone browser – http://bit.ly/2wyBjS7
Demo Sheet Url – http://bit.ly/2ZbVly5
1.Android Part :
Step 1 : Add 2 xml files
- 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