Using Google Spread sheet as DataBase Part -2 | Posting ( Insert ) data into sheets from Android App.

By admin
|
February 22, 2017
| 64 Comments

We have seen about fetching data from Google spread sheet in Part 1. Here I am going to explain about Posting data into Google Sheets from your android device.

 

Here It Consists of 2 Parts

  1. Creating app script (Google Script) that inserts data into Spread Sheet and publishing it as web app.
  2. Using the url send Http Post request and pass the parameter from Android App.                                                    [maxbutton id=”1″]

1. Creating App Script

Note : create your own script or you can use the script I have published.

 

Step 1 :  Go to App script console and create new project in app script .

Step 2 : Insert the below Code which inserts data to Spread sheet.

function doGet(e){
  return handleResponse(e);
  
}

//Recieve parameter and pass it to function to handle

function doPost(e){
  return handleResponse(e);
}  

// here handle with parameter

function handleResponse(request) {
  var output  = ContentService.createTextOutput();
  
  //create varibles to recieve respective parameters
  
  var name = request.parameter.name;
  var country = request.parameter.country;
  var id = request.parameter.id;
  
  
  //open your Spread sheet by passing id
  
  var ss= SpreadsheetApp.openById(id);
  var sheet=ss.getSheetByName("Sheet1");
  
  //add new row with recieved parameter from client
  
  var rowData = sheet.appendRow([name,country]);  
  
 
  var callback = request.parameters.callback;
  if (callback === undefined) {
    output.setContent(JSON.stringify("Success"));
  } else {
    output.setContent(callback + "(" + JSON.stringify("Success") + ")");
  }

  output.setMimeType(ContentService.MimeType.JSON);
  
  return output;


}

 

Step 3 : Now Publish (Deploy) it as web App with Access to anyone.

Step 4 : Copy the link and save it for further use in Step

 

you can use post man tool to verify doGet and doPost activities.

 

2.  Android Part

Note : Since I am enhancing previous project. You can refer previous part before reading further.

and I have renamed MainActivity.java and main.xml files. Everything else remains same.

 

Step 1 : Create form.xml to take inputs from user.

<android.support.design.widget.CoordinatorLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:layout_width="match_parent"
    android:layout_height="match_parent">

    <android.support.design.widget.AppBarLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:theme="@style/ThemeOverlay.AppCompat.Dark.ActionBar">

        <android.support.v7.widget.Toolbar
            android:id="@+id/toolbar"
            android:layout_width="match_parent"
            android:layout_height="?attr/actionBarSize"
            android:background="?attr/colorPrimary"
            app:layout_scrollFlags="scroll|enterAlways"
            app:popupTheme="@style/ThemeOverlay.AppCompat.Light" />
    </android.support.design.widget.AppBarLayout>

    <LinearLayout
        android:layout_width="fill_parent"
        android:layout_height="match_parent"
        android:layout_marginTop="?attr/actionBarSize"
        android:orientation="vertical"
        android:paddingLeft="20dp"
        android:paddingRight="20dp"
        android:paddingTop="60dp">

        <android.support.design.widget.TextInputLayout
            android:id="@+id/input_layout_name"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <EditText
                android:id="@+id/input_name"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:singleLine="true"
                android:hint="name" />
        </android.support.design.widget.TextInputLayout>

        <android.support.design.widget.TextInputLayout
            android:id="@+id/input_layout_email"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <EditText
                android:id="@+id/input_country"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:hint="country" />
        </android.support.design.widget.TextInputLayout>



        <Button android:id="@+id/btn_submit"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:text="Submit"
            android:background="@color/colorPrimary"
            android:layout_marginTop="40dp"
            android:textColor="@android:color/white"/>

    </LinearLayout>

</android.support.design.widget.CoordinatorLayout>

 

Step 2 : Create PostData.java class to handle input and post it into server using deployed web app. Thats it with the requirement.

package androidlabs.gsheets2.Post;

import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
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.JSONObject;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.Iterator;

import javax.net.ssl.HttpsURLConnection;

import androidlabs.gsheets2.R;

public class PostData extends AppCompatActivity {
    private ProgressDialog progress;


    TextView tvName;
    TextView tvCountry;
    Button button;
    String name;
    String country;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.form);

        button=(Button)findViewById(R.id.btn_submit);
        tvName=(EditText)findViewById(R.id.input_name);
        tvCountry=(EditText)findViewById(R.id.input_country);

        button.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

               name = tvName.getText().toString();
                country=tvCountry.getText().toString();

                new SendRequest().execute();
            }

        }   );

        }







    public class SendRequest extends AsyncTask<String, Void, String> {


        protected void onPreExecute(){}

        protected String doInBackground(String... arg0) {

            try{

                URL url = new URL("https://script.google.com/macros/s/AKfycbx-Nu9l0zuZ0olaCeuS5iBHoaLoDEXtQhvw6TmUokuQj_uR7Uw/exec");
                // https://script.google.com/macros/s/AKfycbyuAu6jWNYMiWt9X5yp63-hypxQPlg5JS8NimN6GEGmdKZcIFh0/exec
                JSONObject postDataParams = new JSONObject();

                //int i;
                //for(i=1;i<=70;i++)


                //    String usn = Integer.toString(i);

                String id= "1hYZGyo5-iFpuwofenZ6s-tsaFPBQRSx9HQYydigA4Dg";

                postDataParams.put("name",name);
                postDataParams.put("country",country);
                postDataParams.put("id",id);


                Log.e("params",postDataParams.toString());

                HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                conn.setReadTimeout(15000 /* milliseconds */);
                conn.setConnectTimeout(15000 /* milliseconds */);
                conn.setRequestMethod("POST");
                conn.setDoInput(true);
                conn.setDoOutput(true);

                OutputStream os = conn.getOutputStream();
                BufferedWriter writer = new BufferedWriter(
                        new OutputStreamWriter(os, "UTF-8"));
                writer.write(getPostDataString(postDataParams));

                writer.flush();
                writer.close();
                os.close();

                int responseCode=conn.getResponseCode();

                if (responseCode == HttpsURLConnection.HTTP_OK) {

                    BufferedReader in=new BufferedReader(new InputStreamReader(conn.getInputStream()));
                    StringBuffer sb = new StringBuffer("");
                    String line="";

                    while((line = in.readLine()) != null) {

                        sb.append(line);
                        break;
                    }

                    in.close();
                    return sb.toString();

                }
                else {
                    return new String("false : "+responseCode);
                }
            }
            catch(Exception e){
                return new String("Exception: " + e.getMessage());
            }
        }

        @Override
        protected void onPostExecute(String result) {
            Toast.makeText(getApplicationContext(), result,
                    Toast.LENGTH_LONG).show();

        }
    }

    public String getPostDataString(JSONObject params) throws Exception {

        StringBuilder result = new StringBuilder();
        boolean first = true;

        Iterator<String> itr = params.keys();

        while(itr.hasNext()){

            String key= itr.next();
            Object value = params.get(key);

            if (first)
                first = false;
            else
                result.append("&");

            result.append(URLEncoder.encode(key, "UTF-8"));
            result.append("=");
            result.append(URLEncoder.encode(value.toString(), "UTF-8"));

        }
        return result.toString();
    }
}

 

But to give UI to select Inserion and View I am adding one more xml and class

 

Step 3 : Create main_page.xml to add 2 buttons to choose.

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

    <Button
        android:id="@+id/insertUser"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:layout_marginLeft="25dp"
        android:layout_marginRight="25dp"
        android:background="#37a8f7"
        android:text="View User"
        android:layout_marginTop="15dp"
        android:textColor="#fff" />

    <Button
        android:id="@+id/viewUser"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_gravity="center"
        android:layout_marginLeft="25dp"
        android:layout_marginRight="25dp"
        android:background="#37a8f7"
        android:text="Add User"
        android:layout_marginTop="15dp"
        android:textColor="#fff" />

</LinearLayout>

 

Step 4 : Create MainPage.java to handle buttons. Use Intent objects to Navigate to respective activities.

package androidlabs.gsheets2;

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

import androidlabs.gsheets2.Post.PostData;

/**
 * Created by ADJ on 2/21/2017.
 */
public class MainPage extends AppCompatActivity{

    Button getData;
    Button sendData;

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

        getData=(Button)findViewById(R.id.insertUser);
        sendData=(Button)findViewById(R.id.viewUser);

        getData.setOnClickListener(new View.OnClickListener(){

            @Override
            public void onClick(View v) {


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

            }

        });
        sendData.setOnClickListener(new View.OnClickListener(){

            @Override
            public void onClick(View v) {


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

        });


    };



    }

 

Step 5 : Then Modify in manifest file as below by including newly added Classes and change Launching Activity.

<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="androidlabs.gsheets2">


    <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:icon="@drawable/logo_icon"
        android:label="@string/app_name"
        android:supportsRtl="true"
        android:theme="@style/AppTheme" >
        <activity
            android:name=".MainPage"
            android:label="@string/app_name"
            android:theme="@style/AppTheme.NoActionBar" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

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

        <activity
            android:name=".UserList">
            </activity>

        <activity android:name=".Post.PostData">

        </activity>
    </application>

</manifest>

 

Final output :

 

[maxbutton id=”1″]

 

 

 

android, AppScript, google, google spread sheet, spread sheet
| Tags: , , , , , , , , , , , ,