Google Sheet As DATABASE for your Android Application Part – 3 | [CRUD] Create, Read, Update, Delete operation on Google Sheets from Android App

By admin

May 18, 2017

Previously I have posted about Insert and Fetching data from Google Sheet to Android app. So here I am covering complete CRUD operation in a single App so that it can be helpful for those who need it completely.

I recommend using this code to everyone instead of Part-1 and Part -2,

Here It consists of 2 Parts,

Google App Script Part (Back End)
Android Part (Front End)

[maxbutton id=”5″ url=”https://www.crazycodersclub.com/download2/” ]

1.Google App Script Part

Step 1 : Create new app script project. Click here to create App Script.

Step 2 : Copy and paste the below script which handles two attributes (id,name). You can change the logic, attributes as required.

function doGet(e) {

    var op = e.parameter.action;

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



    if (op == "insert")
        return insert_value(e, sheet);

    //Make sure you are sending proper parameters 
    if (op == "read")
        return read_value(e, sheet);

    if (op == "update")
        return update_value(e, sheet);

    if (op == "delete")
        return delete_value(e, sheet);

    if (op == "readAll")
        return read_all_value(e, ss);

}

//Recieve parameter and pass it to function to handle




function insert_value(request, sheet) {


    var id = request.parameter.id;
    var country = request.parameter.name;

    var flag = 1;
    var lr = sheet.getLastRow();
    for (var i = 1; i <= lr; i++) {
        var id1 = sheet.getRange(i, 2).getValue();
        if (id1 == id) {
            flag = 0;
            var result = "Id already exist..";
        }
    }
    //add new row with recieved parameter from client
    if (flag == 1) {
        var d = new Date();
        var currentTime = d.toLocaleString();
        var rowData = sheet.appendRow([currentTime, id, country]);


        var result = "Insertion successful";
    }
    result = JSON.stringify({

        "result": result

    });

    return ContentService
        .createTextOutput(result)
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
}




function read_all_value(request, ss) {


    var output = ContentService.createTextOutput(),
        data = {};
    //Note : here sheet is sheet name , don't get confuse with other operation 
    var sheet = "sheet1";

    data.records = readData_(ss, sheet);

    var callback = request.parameters.callback;

    if (callback === undefined) {
        output.setContent(JSON.stringify(data));
    } else {
        output.setContent(callback + "(" + JSON.stringify(data) + ")");
    }
    output.setMimeType(ContentService.MimeType.JAVASCRIPT);

    return output;
}


function readData_(ss, sheetname, properties) {

    if (typeof properties == "undefined") {
        properties = getHeaderRow_(ss, sheetname);
        properties = properties.map(function(p) {
            return p.replace(/\s+/g, '_');
        });
    }

    var rows = getDataRows_(ss, sheetname),
        data = [];

    for (var r = 0, l = rows.length; r < l; r++) {
        var row = rows[r],
            record = {};

        for (var p in properties) {
            record[properties[p]] = row[p];
        }

        data.push(record);

    }
    return data;
}



function getDataRows_(ss, sheetname) {
    var sh = ss.getSheetByName(sheetname);

    return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}


function getHeaderRow_(ss, sheetname) {
    var sh = ss.getSheetByName(sheetname);

    return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
}


//update function

function update_value(request, sheet) {

    var output = ContentService.createTextOutput();
    var id = request.parameter.id;
    var flag = 0;
    var country = request.parameter.name;
    var lr = sheet.getLastRow();
    for (var i = 1; i <= lr; i++) {
        var rid = sheet.getRange(i, 2).getValue();
        if (rid == id) {
            sheet.getRange(i, 3).setValue(country);
            var result = "value updated successfully";
            flag = 1;
        }
    }
    if (flag == 0)
        var result = "id not found";

    result = JSON.stringify({
        "result": result
    });

    return ContentService
        .createTextOutput(result)
        .setMimeType(ContentService.MimeType.JAVASCRIPT);


}




function delete_value(request, sheet) {

    var output = ContentService.createTextOutput();
    var id = request.parameter.id;
    var country = request.parameter.name;
    var flag = 0;



    var lr = sheet.getLastRow();
    for (var i = 1; i <= lr; i++) {
        var rid = sheet.getRange(i, 2).getValue();
        if (rid == id) {
            sheet.deleteRow(i);
            var result = "value deleted successfully";
            flag = 1;
        }

    }

    if (flag == 0)
        var result = "id not found";



    result = JSON.stringify({
        "result": result
    });

    return ContentService
        .createTextOutput(result)
        .setMimeType(ContentService.MimeType.JAVASCRIPT);

}


function read_value(request, sheet) {

    var id = request.parameter.id;
    var name;
    var record = {};
    //var place = request.parameter.place;

    var flag = 1;
    var lr = sheet.getLastRow();
    for (var i = 1; i <= lr; i++) {
        var id1 = sheet.getRange(i, 2).getValue();
        if (id1 == id) {
            flag = 0;

            name = sheet.getRange(i, 3).getValue();



            var result = JSON.stringify({
                "user": {
                    "id": id,
                    "name": name
                }

            });


        }

    }
    return ContentService
        .createTextOutput(result)
        .setMimeType(ContentService.MimeType.JAVASCRIPT);


}

Step 3: Change the url of the spread sheet. Make sure sheet is shared .[anyone with the link can view]

Step 4: Go to Publish -> Deploy as web app. A window pop up, here ->Who has access to the app: -> Anyone, even Anonymous

Publish/Update. Copy the published URL and store it.

2. Android Part

Step 1: Create new Android App from Android Studio

Step 2: Go to build.gradle[module: app] and add following dependencies.

note: If you are downloading the source code and modifying. Please make sure build tool version and compileSdk Version in build.gradle[module: dependancyapp] are compatible with your phone. If you are developing from the scratch then no issues, just dependency,

dependencies {
.....................
..................... 
   compile 'com.squareup.okhttp:okhttp:2.4.0'
    compile 'com.squareup.okhttp:okhttp-urlconnection:2.2.0'
}

Step 3: Let’s add all layout files.

activity_main.xml This holds the button of CRUD operation.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    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="androidlabs.crud.MainActivity">


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Create / Insert"
        android:id="@+id/insert_btn"
        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete"
        android:id="@+id/delete_btn"
        android:layout_below="@+id/read_all_btn"
        android:layout_alignLeft="@+id/read_all_btn"
        android:layout_alignStart="@+id/read_all_btn"
        android:layout_alignRight="@+id/read_all_btn"
        android:layout_alignEnd="@+id/read_all_btn" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Read"
        android:id="@+id/read_btn"
        android:layout_below="@+id/insert_btn"
        android:layout_alignLeft="@+id/insert_btn"
        android:layout_alignStart="@+id/insert_btn"
        android:layout_alignRight="@+id/insert_btn"
        android:layout_alignEnd="@+id/insert_btn" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Read All"
        android:id="@+id/read_all_btn"
        android:layout_below="@+id/update_btn"
        android:layout_alignLeft="@+id/update_btn"
        android:layout_alignStart="@+id/update_btn"
        android:layout_alignRight="@+id/update_btn"
        android:layout_alignEnd="@+id/update_btn" />

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="update"
        android:id="@+id/update_btn"
        android:layout_below="@+id/read_btn"
        android:layout_alignLeft="@+id/read_btn"
        android:layout_alignStart="@+id/read_btn"
        android:layout_alignRight="@+id/read_btn"
        android:layout_alignEnd="@+id/read_btn" />


</RelativeLayout>

insert_data.xml This Contains the View of 2 Text fields and Insert Button

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent" android:layout_height="match_parent" >

    <EditText
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:id="@+id/uid"
        android:hint="User Id"
        android:layout_marginTop="54dp"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Insert"
        android:id="@+id/insert_btn"
        android:layout_marginTop="45dp"
        android:layout_below="@+id/name"
        android:layout_centerHorizontal="true" />

    <EditText
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:id="@+id/name"
        android:hint="Name"
        android:layout_below="@+id/uid"
        android:layout_centerHorizontal="true" />
</RelativeLayout>

read_data.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent" android:layout_height="match_parent" >

    <EditText
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:id="@+id/uid"
        android:hint="User ID"
        android:layout_marginTop="54dp"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Find"
        android:id="@+id/insert_btn"
        android:layout_marginTop="45dp"
        android:layout_centerHorizontal="true"
        android:layout_below="@+id/uid" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/id_v"
        android:layout_centerVertical="true"
        android:layout_toRightOf="@+id/insert_btn"
        android:layout_toEndOf="@+id/insert_btn" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/id_l"
        android:layout_centerVertical="true"
        android:layout_toLeftOf="@+id/insert_btn"
        android:layout_toStartOf="@+id/insert_btn" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/name_l"
        android:layout_marginTop="31dp"
        android:layout_below="@+id/id_l"
        android:layout_alignLeft="@+id/id_l"
        android:layout_alignStart="@+id/id_l" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/name_v"
        android:layout_alignTop="@+id/name_l"
        android:layout_alignLeft="@+id/id_v"
        android:layout_alignStart="@+id/id_v" />

</RelativeLayout>

update_data.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent" android:layout_height="match_parent" >

    <EditText
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:id="@+id/uid"
        android:hint="User ID"
        android:layout_marginTop="54dp"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Update"
        android:id="@+id/update_btn1"
        android:layout_marginTop="45dp"
        android:layout_below="@+id/name"
        android:layout_centerHorizontal="true" />

    <EditText
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:id="@+id/name"
        android:hint="Name"
        android:layout_below="@+id/uid"
        android:layout_centerHorizontal="true" />
</RelativeLayout>

delete_data,xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent" android:layout_height="match_parent" >

    <EditText
        android:layout_width="match_parent"
        android:layout_height="40dp"
        android:id="@+id/uid"
        android:hint="User ID"
        android:layout_marginTop="54dp"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />


    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Delete"
        android:id="@+id/delete_btn"
        android:layout_marginTop="45dp"
        android:layout_centerHorizontal="true"
        android:layout_below="@+id/uid" />


</RelativeLayout>

read_all.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical" android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:weightSum="1">

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Read All"
        android:id="@+id/readAll_btn1"
        android:layout_gravity="center_horizontal" />

    <ListView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:id="@+id/listView"
        android:layout_gravity="center_horizontal" />
</LinearLayout>

layout_row_view.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    xmlns:fresco="http://schemas.android.com/apk/res-auto"
    android:orientation="vertical">

    <LinearLayout
        android:layout_width="match_parent"
        android:orientation="vertical"
        android:layout_centerVertical="true"
        android:layout_height="wrap_content"
        >
        <TextView
            android:id="@+id/textViewId"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="5dp"
            android:textAppearance="?android:textAppearanceLarge"
            tools:text="TextView" />

        <TextView
            android:id="@+id/textViewName"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_margin="5dp"
            android:textAppearance="?android:textAppearanceMedium"
            tools:text="TextView" />

    </LinearLayout>
</RelativeLayout>

Step 4 : Now Let’s Add Java Class Files Which handles and Controles the operation.

1 Controller.java

package androidlabs.crud;

import android.support.annotation.NonNull;
import android.util.Log;

import com.squareup.okhttp.OkHttpClient;
import com.squareup.okhttp.Request;
import com.squareup.okhttp.Response;

import org.json.JSONException;
import org.json.JSONObject;

import java.io.IOException;


public class Controller {



    public static final String TAG = "TAG";

    public static final String WAURL="Your Script Web APP URL";
// EG : https://script.google.com/macros/s/AKfycbwXXXXXXXXXXXXXXXXX/exec?
//Make Sure '?' Mark is present at the end of URL
    private static Response response;

    public static JSONObject readAllData() {
        try {
            OkHttpClient client = new OkHttpClient();
            Request request = new Request.Builder()
                    .url(WAURL+"action=readAll")
                    .build();
            response = client.newCall(request).execute();
            return new JSONObject(response.body().string());
        } catch (@NonNull IOException | JSONException e) {
            Log.e(TAG, "" + e.getLocalizedMessage());
        }
        return null;
    }


    public static JSONObject insertData(String id, String name) {
        try {
            OkHttpClient client = new OkHttpClient();
            Request request = new Request.Builder()
                    .url(WAURL+"action=insert&id="+id+"&name="+name)
                    .build();
            response = client.newCall(request).execute();
        //    Log.e(TAG,"response from gs"+response.body().string());
            return new JSONObject(response.body().string());


        } catch (@NonNull IOException | JSONException e) {
            Log.e(TAG, "recieving null " + e.getLocalizedMessage());
        }
        return null;
    }

    public static JSONObject updateData(String id, String name) {
        try {
            OkHttpClient client = new OkHttpClient();
            Request request = new Request.Builder()
                    .url(WAURL+"action=update&id="+id+"&name="+name)
                    .build();
            response = client.newCall(request).execute();
            //    Log.e(TAG,"response from gs"+response.body().string());
            return new JSONObject(response.body().string());


        } catch (@NonNull IOException | JSONException e) {
            Log.e(TAG, "recieving null " + e.getLocalizedMessage());
        }
        return null;
    }

    public static JSONObject readData(String id) {
        try {
            OkHttpClient client = new OkHttpClient();
            Request request = new Request.Builder()
                    .url(WAURL+"action=read&id="+id)
                    .build();
            response = client.newCall(request).execute();
               // Log.e(TAG,"response from gs"+response.body().string());
            return new JSONObject(response.body().string());


        } catch (@NonNull IOException | JSONException e) {
            Log.e(TAG, "recieving null " + e.getLocalizedMessage());
        }
        return null;
    }

    public static JSONObject deleteData(String id) {
        try {
            OkHttpClient client = new OkHttpClient();
            Request request = new Request.Builder()
                    .url(WAURL+"action=delete&id="+id)
                    .build();
            response = client.newCall(request).execute();
            // Log.e(TAG,"response from gs"+response.body().string());
            return new JSONObject(response.body().string());


        } catch (@NonNull IOException | JSONException e) {
            Log.e(TAG, "recieving null " + e.getLocalizedMessage());
        }
        return null;
    }


}

2. InsertData.java

package androidlabs.crud;

import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import org.json.JSONException;
import org.json.JSONObject;

/**
 * Created by ADJ on 5/14/2017.
 */
public class InsertData extends AppCompatActivity {

 private Button insert;
 String id;
 String name;
 private EditText uid1ET, uid2, nameET;


 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.insert_data);
  insert = (Button) findViewById(R.id.insert_btn);
  uid1ET = (EditText) findViewById(R.id.uid);
  nameET = (EditText) findViewById(R.id.name);

  insert.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {

    id = uid1ET.getText().toString();
    name = nameET.getText().toString();
    new InsertDataActivity().execute();
   }
  });
 }



 class InsertDataActivity extends AsyncTask < Void, Void, Void > {

  ProgressDialog dialog;
  int jIndex;
  int x;

  String result = null;


  @Override
  protected void onPreExecute() {
   super.onPreExecute();

   dialog = new ProgressDialog(InsertData.this);
   dialog.setTitle("Hey Wait Please...");
   dialog.setMessage("Inserting your values..");
   dialog.show();

  }

  @Nullable
  @Override
  protected Void doInBackground(Void...params) {
   JSONObject jsonObject = Controller.insertData(id, name);
   Log.i(Controller.TAG, "Json obj ");

   try {
    /**
     * Check Whether Its NULL???
     */
    if (jsonObject != null) {

     result = jsonObject.getString("result");

    }
   } catch (JSONException je) {
    Log.i(Controller.TAG, "" + je.getLocalizedMessage());
   }
   return null;
  }
  @Override
  protected void onPostExecute(Void aVoid) {
   super.onPostExecute(aVoid);
   dialog.dismiss();
   Toast.makeText(getApplicationContext(), result, Toast.LENGTH_LONG).show();
  }
 }
}

3. ReadSingleData.java

package androidlabs.crud;

import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import org.json.JSONException;
import org.json.JSONObject;

/**
 * Created by ADJ on 5/14/2017.
 */
public class ReadSingleData extends AppCompatActivity {

 private Button read;
 String id;
 String name;
 private EditText uid1ET;
 private TextView id_l, name_l, id_v, name_v;


 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.read_data);
  read = (Button) findViewById(R.id.insert_btn);
  uid1ET = (EditText) findViewById(R.id.uid);

  id_l = (TextView) findViewById(R.id.id_l);
  name_l = (TextView) findViewById(R.id.name_l);
  id_v = (TextView) findViewById(R.id.id_v);
  name_v = (TextView) findViewById(R.id.name_v);


  read.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {

    id = uid1ET.getText().toString();

    new ReadDataActivity().execute();
   }
  });
 }


 class ReadDataActivity extends AsyncTask < Void, Void, Void > {

  ProgressDialog dialog;
  int jIndex;
  int x;


  @Override
  protected void onPreExecute() {
   super.onPreExecute();

   dialog = new ProgressDialog(ReadSingleData.this);
   dialog.setTitle("Hey Wait Please...");
   dialog.setMessage("Fetching your values");
   dialog.show();

  }

  @Nullable
  @Override
  protected Void doInBackground(Void...params) {
   Log.i(Controller.TAG, "IDVALUE" + id);
   JSONObject jsonObject = Controller.readData(id);
   Log.i(Controller.TAG, "Json obj " + jsonObject);

   try {
    /**
     * Check Whether Its NULL???
     */
    if (jsonObject != null) {

     JSONObject user = jsonObject.getJSONObject("user");
     name = user.getString("name");

    }
   } catch (JSONException je) {
    Log.i(Controller.TAG, "" + je.getLocalizedMessage());
   }
   return null;
  }

  @Override
  protected void onPostExecute(Void aVoid) {
   super.onPostExecute(aVoid);
   dialog.dismiss();
   if (name != null) {
    id_l.setText("ID");
    name_l.setText("NAME");
    id_v.setText(id);
    name_v.setText(name);

   } else
    Toast.makeText(getApplicationContext(), "ID not found", Toast.LENGTH_LONG).show();
  }
 }
}

4. UpdateData.java

package androidlabs.crud;

import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import org.json.JSONException;
import org.json.JSONObject;

/**
 * Created by ADJ on 5/14/2017.
 */
public class UpdateData extends AppCompatActivity{

    private Button update;
    String id;
    String name;
    private EditText uid1ET,uid2,nameET;


    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.update_data);
        update=(Button)findViewById(R.id.update_btn1);
        uid1ET=(EditText)findViewById(R.id.uid);
        nameET=(EditText)findViewById(R.id.name);

        update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                 id=uid1ET.getText().toString();
                 name=nameET.getText().toString();
                new UpdateDataActivity().execute();
            }
        });
    }



    class UpdateDataActivity extends AsyncTask<Void, Void, Void> {

        ProgressDialog dialog;
        int jIndex;
        int x;

        String result=null;


        @Override
        protected void onPreExecute() {
            super.onPreExecute();

            dialog = new ProgressDialog(UpdateData.this);
            dialog.setTitle("Hey Wait Please..."+x);
            dialog.setMessage("I am getting your JSON");
            dialog.show();

        }

        @Nullable
        @Override
        protected Void doInBackground(Void... params) {
            JSONObject jsonObject = Controller.updateData(id,name);
            Log.i(Controller.TAG, "Json obj ");

            try {
                /**
                 * Check Whether Its NULL???
                 */
                if (jsonObject != null) {

                     result=jsonObject.getString("result");

                }
            } catch (JSONException je) {
                Log.i(Controller.TAG, "" + je.getLocalizedMessage());
            }
            return null;
        }
        @Override
        protected void onPostExecute(Void aVoid) {
            super.onPostExecute(aVoid);
            dialog.dismiss();
           Toast.makeText(getApplicationContext(),result,Toast.LENGTH_LONG).show();
        }
    }
}

5. DeleteData.java

package androidlabs.crud;

import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import org.json.JSONException;
import org.json.JSONObject;

/**
 * Created by ADJ on 5/14/2017.
 */
public class DeleteData extends AppCompatActivity{

    private Button delete;
    String id;
    String name;
    private EditText uid1ET;



    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.delete_data);
        delete=(Button)findViewById(R.id.delete_btn);
        uid1ET=(EditText)findViewById(R.id.uid);



        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                id=uid1ET.getText().toString();

                new DeleteDataActivity().execute();
            }
        });
    }



    class DeleteDataActivity extends AsyncTask<Void, Void, Void> {

        ProgressDialog dialog;
        int jIndex;
        int x;
        String result=null;



        @Override
        protected void onPreExecute() {
            super.onPreExecute();

            dialog = new ProgressDialog(DeleteData.this);
            dialog.setTitle("Hey Wait Please...");
            dialog.setMessage("Deleting... ");
            dialog.show();

        }

        @Nullable
        @Override
        protected Void doInBackground(Void... params) {
           Log.i(Controller.TAG,"IDVALUE"+id);
            JSONObject jsonObject = Controller.deleteData(id);
            Log.i(Controller.TAG, "Json obj "+jsonObject);

            try {
                /**
                 * Check Whether Its NULL???
                 */
                if (jsonObject != null) {

                    result=jsonObject.getString("result");


                }
            } catch (JSONException je) {
                Log.i(Controller.TAG, "" + je.getLocalizedMessage());
            }
            return null;
        }
        @Override
        protected void onPostExecute(Void aVoid) {
            super.onPostExecute(aVoid);
            dialog.dismiss();
            Toast.makeText(getApplicationContext(),result,Toast.LENGTH_LONG).show();

        }
    }
}

Step 4: To View All the data I am using Array Adapter and list

1 MyArrayAdapter.java

package androidlabs.crud;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.RelativeLayout;
import android.widget.TextView;

import java.util.List;

public class MyArrayAdapter extends ArrayAdapter < MyDataModel > {

 List < MyDataModel > modelList;
 Context context;
 private LayoutInflater mInflater;

 // Constructors
 public MyArrayAdapter(Context context, List < MyDataModel > objects) {
  super(context, 0, objects);
  this.context = context;
  this.mInflater = LayoutInflater.from(context);
  modelList = objects;
 }

 @Override
 public MyDataModel getItem(int position) {
  return modelList.get(position);
 }

 @Override
 public View getView(int position, View convertView, ViewGroup parent) {
  final ViewHolder vh;
  if (convertView == null) {
   View view = mInflater.inflate(R.layout.layout_row_view, parent, false);
   vh = ViewHolder.create((RelativeLayout) view);
   view.setTag(vh);
  } else {
   vh = (ViewHolder) convertView.getTag();
  }

  MyDataModel item = getItem(position);

  vh.textViewId.setText(item.getId());
  vh.textViewName.setText(item.getName());


  return vh.rootView;
 }



 private static class ViewHolder {
  public final RelativeLayout rootView;

  public final TextView textViewId;
  public final TextView textViewName;


  private ViewHolder(RelativeLayout rootView, TextView textViewName, TextView textViewId) {
   this.rootView = rootView;
   this.textViewId = textViewId;
   this.textViewName = textViewName;

  }

  public static ViewHolder create(RelativeLayout rootView) {
   TextView textViewId = (TextView) rootView.findViewById(R.id.textViewId);
   TextView textViewName = (TextView) rootView.findViewById(R.id.textViewName);

   return new ViewHolder(rootView, textViewName, textViewId);
  }
 }
}

2.MyDataModel.java

package androidlabs.crud;



public class MyDataModel {

    private String name;

    private String id;



    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getId() {
        return id;
    }

    public void setCountry(String id) {
        this.id=id;
    }



}

3.ReadAllData.java

package androidlabs.crud;

import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.ListView;
import android.widget.Toast;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.util.ArrayList;

/**
 * Created by ADJ on 5/17/2017.
 */
public class ReadAllData extends AppCompatActivity {

 private ListView listView;
 private ArrayList < MyDataModel > list;
 private MyArrayAdapter adapter;
 private Button readAll;


 @Override

 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.read_all);

  readAll = (Button) findViewById(R.id.readAll_btn1);
  list = new ArrayList < > ();
  adapter = new MyArrayAdapter(this, list);
  listView = (ListView) findViewById(R.id.listView);
  listView.setAdapter(adapter);

  readAll.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {
    new ReadData1().execute();
   }
  });



 }


 class ReadData1 extends AsyncTask < Void, Void, Void > {

  ProgressDialog dialog;
  int jIndex;
  int x;

  @Override
  protected void onPreExecute() {
   super.onPreExecute();
   /**
    * Progress Dialog for User Interaction
    */

   x = list.size();

   if (x == 0)
    jIndex = 0;
   else
    jIndex = x;

   dialog = new ProgressDialog(ReadAllData.this);
   dialog.setTitle("Hey Wait Please..." + x);
   dialog.setMessage("Fetching all the Values");
   dialog.show();
  }

  @Nullable
  @Override
  protected Void doInBackground(Void...params) {
   JSONObject jsonObject = Controller.readAllData();
   try {
    /**
     * Check Whether Its NULL???
     */
    if (jsonObject != null) {
     /**
      * Check Length...
      */
     if (jsonObject.length() > 0) {
      /**
       * Getting Array named "records" From MAIN Json Object
       */
      JSONArray array = jsonObject.getJSONArray("records");

      /**
       * Check Length of Array...
       */


      int lenArray = array.length();
      if (lenArray > 0) {
       for (; jIndex < lenArray; jIndex++) {

        /**
         * Creating Every time New Object
         * and
         * Adding into List
         */
        MyDataModel model = new MyDataModel();

        /**
         * Getting Inner Object from contacts array...
         * and
         * From that We will get Name of that Contact
         *
         */
        JSONObject innerObject = array.getJSONObject(jIndex);

        String id = innerObject.getString("ID");
        String name = innerObject.getString("NAME");
        //  String image = innerObject.getString(Keys.KEY_IMAGE);
        /**
         * Getting Object from Object "phone"
         */
        //JSONObject phoneObject = innerObject.getJSONObject(Keys.KEY_PHONE);
        //String phone = phoneObject.getString(Keys.KEY_MOBILE);

        model.setName(name);
        model.setCountry(id);
        //                              model.setImage(image);

        /**
         * Adding name and phone concatenation in List...
         */
        list.add(model);
       }
      }
     }
    } else {

    }
   } catch (JSONException je) {
    Log.i(Controller.TAG, "" + je.getLocalizedMessage());
   }
   return null;
  }

  @Override
  protected void onPostExecute(Void aVoid) {
   super.onPostExecute(aVoid);
   dialog.dismiss();
   /**
    * Checking if List size if more than zero then
    * Update ListView
    */
   if (list.size() > 0) {
    adapter.notifyDataSetChanged();
   } else {
    Toast.makeText(getApplicationContext(), "No data found", Toast.LENGTH_LONG).show();
   }
  }
 }
}

Step 5 : Add MainActivity.java

package androidlabs.crud;

//MainActivity.java

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







public class MainActivity extends AppCompatActivity {


 private Button read, readAll, insert, delete, update;




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




  read = (Button) findViewById(R.id.read_btn);
  readAll = (Button) findViewById(R.id.read_all_btn);
  insert = (Button) findViewById(R.id.insert_btn);
  update = (Button) findViewById(R.id.update_btn);
  delete = (Button) findViewById(R.id.delete_btn);




  readAll.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {

    if (InternetConnection.checkConnection(getApplicationContext())) {
     Intent intent = new Intent(getApplicationContext(), ReadAllData.class);
     startActivity(intent);

    } else {
     Toast.makeText(getApplicationContext(), "Check your internet connection", Toast.LENGTH_LONG).show();
    }





   }
  });


  insert.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {

    if (InternetConnection.checkConnection(getApplicationContext())) {
     Intent intent = new Intent(getApplicationContext(), InsertData.class);
     startActivity(intent);


    } else {
     Toast.makeText(getApplicationContext(), "Check your internet connection", Toast.LENGTH_LONG).show();
    }





   }
  });


  update.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {


    if (InternetConnection.checkConnection(getApplicationContext())) {
     Intent intent = new Intent(getApplicationContext(), UpdateData.class);
     startActivity(intent);


    } else {
     Toast.makeText(getApplicationContext(), "Check your internet connection", Toast.LENGTH_LONG).show();
    }




   }
  });


  read.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {

    if (InternetConnection.checkConnection(getApplicationContext())) {
     Intent intent = new Intent(getApplicationContext(), ReadSingleData.class);
     startActivity(intent);


    } else {
     Toast.makeText(getApplicationContext(), "Check your internet connection", Toast.LENGTH_LONG).show();
    }




   }
  });

  delete.setOnClickListener(new View.OnClickListener() {
   @Override
   public void onClick(View view) {

    if (InternetConnection.checkConnection(getApplicationContext())) {
     Intent intent = new Intent(getApplicationContext(), DeleteData.class);
     startActivity(intent);


    } else {
     Toast.makeText(getApplicationContext(), "Check your internet connection", Toast.LENGTH_LONG).show();
    }



   }
  });




 }

}

Step 6 : Change Manifest.xml file

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="androidlabs.crud">
    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
    <uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        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=".InsertData"/>
        <activity android:name=".UpdateData"/>
       <activity android:name=".ReadSingleData"/>
       <activity android:name=".DeleteData"/>
        <activity android:name=".ReadAllData"/>
    </application>

</manifest>

Now Run The Code

[maxbutton id=”5″ url=”https://www.crazycodersclub.com/download2/” ]

Uncategorized

27 Reply to “Google Sheet As DATABASE for your Android Application Part – 3 | [CRUD] Create, Read, Update, Delete operation on Google Sheets from Android App”

Sushanta says:

May 28, 2017 at 9:23 pm

ReadAll function is not working.

1. ANDROID LABS says:
  
  May 29, 2017 at 10:44 am
  
  Can u copy your source code of read all to word file and send me to admin@androidlabs.info..
  
  For second part need to develop logic.. I will do it n send u
  
  1. Sushanta says:
    
    May 29, 2017 at 11:35 am
    
    I applied the logic but did not get the result.I am sending the code.
    
  2. Gimel Gimeno says:
    
    September 26, 2017 at 12:46 am
    
    Hi. Can you send it to me too? Everything works, except the readAll. The function read_all_value has a typo: var sheet = “sheet1” which I changed to “Sheet1” . Thanks!
    
chetan says:

May 30, 2017 at 1:46 pm

plz upload the curd operation video…

1. ANDROID LABS says:
  
  May 30, 2017 at 2:32 pm
  
  Ya will make and upload soon..
  
  1. chetan says:
    
    May 30, 2017 at 9:56 pm
    
    Android application part-3 source code error message
    ” Migrate Project to Gradle?This project does not use the Gradle build system. We recommend that you migrate to using the Gradle build system.”
    
Ariestoni Silalahi says:

June 14, 2017 at 3:39 pm

[Ask]

i have downloaded your source code sir, i have make my own google appsscript using your code but i have changed the spreadsheet url . Is that all the step of using your code sir ?

If yes, i still got this error sir .. Very pleased, i’m very need your help sir https://uploads.disquscdn.com/images/f494e853811570ee59242818f4a62d0312b5b3313df9de200fa88d01386732f9.png https://uploads.disquscdn.com/images/877ac92175ebb2f34e7444d4a1ae10984076bb0fddb4f8eb9a0d0882edcc07c0.png

If you want, which way i can have a private disscussion with you sir ?

Thankyou 🙂

if413009@gmail.com

1. Sateesh Kumar says:
  
  September 19, 2017 at 12:26 am
  
  Hi, is your problem solved? I am also facing same problem. Kindly help me . Please
  
Sateesh Kumar says:

September 19, 2017 at 12:24 am

Hi,
I face below problem when i try to test google app script ( test url). Message is “The script completed but did not return anything.”

And can you share the google spreadsheet URL too if possible. which you used in code

Kindly help thanks.

1. Gimel Gimeno says:
  
  September 26, 2017 at 12:48 am
  
  I use something like this for my url:
  ss = SpreadsheetApp.openByUrl(“https://docs.google.com/spreadsheets/d/1***/edit”);
  
  and I also published my appscript for “Anyone, even anonymous”
  
Sateesh Kumar says:

October 7, 2017 at 1:39 am

Hi,

Kindly help me Please. I am not able to insert itself.

Things i did:

Script Side:

1. Copied the Google App Script to my spreadsheet script. Updated line 5 with my spreadsheet Url ( https://docs.google.com/spreadsheets/d/1yHyr1PoFF9RltMLcTkZO38arHM_5b8y9Y3fJcwdR6dQ/edit )

2. Google Spread sheet: Shared and Published to Web.
link: https://docs.google.com/spreadsheets/d/1yHyr1PoFF9RltMLcTkZO38arHM_5b8y9Y3fJcwdR6dQ/edit#gid=0
Google app script: deployed as per mentioned.

Android Side:

downloaded source code and imported in android studio

1. Changed WAURL in Controlller.java to this url
url: https://script.google.com/macros/s/AKfycbzYb7PcZmg7E_ueHTGxwxTLugZWXDUGVbEIJ4TF4MgUHfLEWRGF/exec?”

2. In myDataModel.java updated setCountry to setId.

Doubts/ Problems are:

1. In Spreadsheet what are the column names should be? ( TIME_STAMP, ID, NAME) ? I Tried the same.
2. How do we check whether Google app script is working fine, before we go to android part. I have checked in your part 01. There is url to check (example: https://script.google.com/macros/u/0/s/AKfycbzYb7PcZmg7E_ueHTGxwxTLugZWXDUGVbEIJ4TF4MgUHfLEWRGF/exec?id=1yHyr1PoFF9RltMLcTkZO38arHM_5b8y9Y3fJcwdR6dQ&sheet=Sheet1) , Here it is not working.

LInks are:
Spreadhseet: https://docs.google.com/spreadsheets/d/1yHyr1PoFF9RltMLcTkZO38arHM_5b8y9Y3fJcwdR6dQ/edit#gid=0

Script: https://script.google.com/macros/d/MWI7m9p7_Z80ZS_k-TwI-5dA-zUGN14uu/edit?uiv=2&mid=ACjPJvHNMshOJ7DbWJYehigmFZrOFjP-OPHUwhzjemgxgx66qK5WJDBkJKZo5tJEqi_liGVVtxwleblY92rlQe0COY4CObYp6iLjkdoVDXSzZo2ey_fhBevk5vRCrIKHlu1LCO_ssEhE7Q#

Kindly help please.

Thanks & Best Regards,
Sateesh

Umang Patel says:

November 8, 2017 at 1:17 am

How to search the data?

John Sword says:

December 17, 2017 at 12:02 pm

even after liking the facebook page it doesnt download

Jarvis AI says:

January 3, 2018 at 7:14 pm

Hi

Thank
you for the informative tuts. It is really well done. I seem to have an
issue with the Script part of the Tut. the program runs but it does not
seem to be finding the associated data in the google spreadsheet. Also
if I past the published script link in a browser window I get: [Script
function not found: doGet]. Running the doGet in function in the script,
I get the error:[TypeError: Cannot read property “parameter” from
undefined. (line 3, file “Code”)]

Any advise? I am a novice and this is completely new territory …

I would like to contact you via email, please send my your email details…

Leonardo Meléndez says:

March 20, 2018 at 1:14 am

With this code, can I work in offline or whithout internet??

Rayuh Dhilah Hanggara says:

March 29, 2018 at 1:41 pm

You forget 1 java class for checking the internet connection

1. Rayuh Dhilah Hanggara says:
  
  March 29, 2018 at 5:36 pm
  
  and 1 more typo on sheet name
  
  1. Sateesh Kumar says:
    
    April 25, 2018 at 12:16 am
    
    Hi,
    In which file is sheet name typo error? i inserted record from android app but did not see in spreadsheet. Would like to know is this because of this.
    
    Thanks
    
    1. Rayuh Dhilah Hanggara says:
      
      April 25, 2018 at 1:26 pm
      
      typo error on script side in read_all_value function
      
      https://uploads.disquscdn.com/images/15614939e3e301ca7e4b52838efa7753f5aa55a866805d3dd219fa531f1b6f81.jpg
      
      1. Sateesh Kumar says:
        
        April 25, 2018 at 11:24 pm
        
        i made it to upper case like “Sheet1” , but only “Read All” tab is not working in android app. Did you come across such case?
        
        Toast message is ” No data found”
      2. Sateesh Kumar says:
        
        April 25, 2018 at 11:49 pm
        
        spreadsheet.openbyUrl(” sheet url”)
        
        sheet url : https://docs.google.com/spreadsheets/d/1c6BlP5slkMEB5gBYvmtk1aZOVHrY5zg0JCNYy-cDp64/edit#gid=0
        
        is this right format? can you please confirm?
        
        Thanks
      3. Rayuh Dhilah Hanggara says:
        
        April 27, 2018 at 12:29 pm
        
        yups it’s right, i just try to open it directly from link u mention above, and its actually works
      4. Sateesh Kumar says:
        
        May 7, 2018 at 11:41 pm
        
        Hi,
        Reason why readall is not working is in Controller.java — readAll method is giving ” jsonexception end of input at character 0 of ”
        
        anyone has any solution for this? i tried but did not find any solution
      5. Rayuh Dhilah Hanggara says:
        
        April 27, 2018 at 12:27 pm
        
        look up on my previous reply.
      6. summ says:
        
        April 27, 2018 at 6:17 pm
        
        same here my read all tab is not working please help me !!
Ghozy Mustofa says:

April 28, 2018 at 10:55 am

hi i cant get the json value,

when i try it in post man
it will say
Unexpected ‘<'

also when i try latest code script it will say

The script is complete, but it does not return anything.