https://www.crazycodersclub.com/android/using-google-spread-sheet-as-database-part-2-posting-insert-data-into-sheets-from-android-app/
Here It Consists of 2 Parts
- Creating app script (Google Script) that inserts data into Spread Sheet and publishing it as web app.
- Using the url send Http Post request and pass the parameter from Android App. Download Source Code
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 :
