當APP與電腦使用GOOGLE SpreadSheet當線上資料庫，就可以做簡單的IOT監控與控制了
- 首先，先到Google SpreadSheet建立新文件，並且將文件設為半公開(連結可編輯或檢視)
- 取得網址SpreadSheet ID
https://docs.google.com/spreadsheets/d/ID/XXX
- 取得Json檔，這邊主要做檢視用，可以先研究，等等程式就是由這邊作分析取得資料
https://spreadsheets.google.com/tq?key=ID
- 可以由Json看到，一開始分為 "cols" 和 "rows"，Farmer使用rows抓取資料，
所以Farmer會大約介紹一下rows的格子規則(Farmer自想，有錯誤請指教)，
在rows中每rows為"c"開頭，每一小格中則為"v"開頭，後面則為小格中的內容
則內容皆為utf-8，所以開啟Json時，中文為亂碼是因為編碼不對的問題。 - Json可以正常下載，則表示就可以開始下一步了，Android是使用抓取Json內容做分析，取得使用者要的內容
Android Studio Code
AndroidManifest.xml
需增加兩種權限
- <uses-permission android:name="android.permission.INTERNET" />
- <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
LayOut
使用兩種元件，Button *1 & TextView *2
<Button
android:id="@+id/btnDownload"
android:enabled="false"
android:onClick="buttonClickHandler"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="download table"
android:layout_alignParentBottom="true"
android:layout_alignParentStart="true" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="New Text"
android:id="@+id/textView"
android:layout_alignParentTop="true"
android:layout_alignParentStart="true" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="New Text"
android:id="@+id/textView2"
android:layout_alignParentTop="true"
android:layout_alignEnd="@+id/btnDownload" />
Java
這邊比較麻煩些，需新建立兩個 Java Class 及原本就有的MainActivty
兩個Class分為
- AsyncResult
- DownloadWebpageTask
AsyncResult
import org.json.JSONObject;
interface AsyncResult{
void onResult(JSONObject object);
}
DownloadWebpageTask
import android.os.AsyncTask;
import org.json.JSONException;
import org.json.JSONObject;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
public class DownloadWebpageTask extends AsyncTask<String, Void, String> {
AsyncResult callback;
public DownloadWebpageTask(AsyncResult callback) {
this.callback = callback;
}
@Override
protected String doInBackground(String... urls) {
// params comes from the execute() call: params[0] is the url.
try {
return downloadUrl(urls[0]);
} catch (IOException e) {
return "Unable to download the requested page.";
}
}
// onPostExecute displays the results of the AsyncTask.
@Override protected void onPostExecute(String result) {
// remove the unnecessary parts from the response and construct a JSON
int start = result.indexOf("{", result.indexOf("{") + 1);
int end = result.lastIndexOf("}");
String jsonResponse = result.substring(start, end);
try {
JSONObject table = new JSONObject(jsonResponse);
callback.onResult(table);
} catch (JSONException e) {
e.printStackTrace();
}
}
private String downloadUrl(String urlString) throws IOException {
InputStream is = null;
try {
URL url = new URL(urlString);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setReadTimeout(10000 /* milliseconds */);
conn.setConnectTimeout(15000 /* milliseconds */);
conn.setRequestMethod("GET");
conn.setDoInput(true);
// Starts the query conn.connect();
int responseCode = conn.getResponseCode();
is = conn.getInputStream();
String contentAsString = convertStreamToString(is);
return contentAsString;
} finally {
if (is != null) {
is.close();
}
}
}
private String convertStreamToString(InputStream is) {
BufferedReader reader = new BufferedReader(new InputStreamReader(is));
StringBuilder sb = new StringBuilder();
String line = null;
try {
while ((line = reader.readLine()) != null) {
sb.append(line + "\n");
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return sb.toString();
}
}
MainActivity
- ID為網址後的ID
import android.content.Context;
import android.net.ConnectivityManager;
import android.net.NetworkInfo;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
public class MainActivity extends AppCompatActivity {
Button btnDownload;
TextView NameView,CountryView;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
btnDownload = (Button) findViewById(R.id.btnDownload);
NameView = (TextView) findViewById(R.id.textView);
CountryView = (TextView) findViewById(R.id.textView2);
ConnectivityManager connMgr = (ConnectivityManager) getSystemService(Context.CONNECTIVITY_SERVICE);
NetworkInfo networkInfo = connMgr.getActiveNetworkInfo();
if (networkInfo != null && networkInfo.isConnected()) {
btnDownload.setEnabled(true);
} else {
btnDownload.setEnabled(false);
}
}
public void buttonClickHandler(View view) {
new DownloadWebpageTask(new AsyncResult() {
@Override
public void onResult(JSONObject object) {
processJson(object);
}
}).execute("https://spreadsheets.google.com/tq?key=ID");
//https://spreadsheets.google.com/tq?key=ID
}
private void processJson(JSONObject object) {
String Name = "";
String Country = "";
try {
JSONArray rows = object.getJSONArray("rows");//rows / cols
for (int r = 0; r < rows.length(); ++r) {
JSONObject row = rows.getJSONObject(r);
JSONArray columns = row.getJSONArray("c");
/*
columns.getJSONObject("小表格順序，由0開始，可以跳號單選").getString("v");
columns.getJSONObject("小表格順序，由0開始，可以跳號單選").getBoolean("v");
columns.getJSONObject("小表格順序，由0開始，可以跳號單選").getDouble("v");
columns.getJSONObject("小表格順序，由0開始，可以跳號單選").getInt("v");
columns.getJSONObject("小表格順序，由0開始，可以跳號單選").getLong("v");
*/
Name += columns.getJSONObject(0).getString("v") + "\n";
Country += columns.getJSONObject(1).getString("v") + "\n";
}
NameView.setText(Name);
CountryView.setText(Country);
} catch (JSONException e) {
e.printStackTrace();
}
}
}
參考資料:
Using Google Spreadsheets as Data Source in Your Android App
Android開發(046) 抓取Google Spreadsheet 的資料(1)
沒有留言:
張貼留言