I am able to retrieve sheet data(specific column) in JSON format using script. But i want to retrieve sheet data(specific column) in JSON format just entering sheet ID in my android app Edittext.
Suppose, i enter any sheet id in my app edittext and want to get those sheet data(specific column) in json format.
Sheet Link: https://docs.google.com/spreadsheets/d/1AtYF5g2_A3AiAhejVj595bDLxO1zoGq7PNGjbdV9U8Q/edit?usp=sharing
This is how i retrieve data using script:
public void getAllData(){
String urls = "https://script.google.com/macros/s/AKfycbw2hRTBRJk_UngIxZVNz1p4CMfZe2aJfyK3on3WyTZo1hVszSWu63DP/exec?action=getItems";
String json_string;
OkHttpClient client = new OkHttpClient();
Request request=new Request.Builder().url(urls).build();
client.newCall(request).enqueue(new Callback() {
@Override
public void onFailure(Call call, IOException e) {
e.printStackTrace();
}
@Override
public void onResponse(Call call, Response response) throws IOException {
if(response.isSuccessful()){
final String str = response.body().string();
MainActivity.this.runOnUiThread(new Runnable() {
@Override
public void run() {
json_string=str;
}
});
}
}
});
}
App script that i used for this:
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1AtYF5g2_A3AiAhejVj595bDLxO1zoGq7PNGjbdV9U8Q/edit#gid=0");
var sheet = ss.getSheetByName("CSE522");
function doGet(e){
var action=e.parameter.action;
if(action=='getItems'){
return getItems(e);
}
}
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['ClassDate']=row[0];
record['Umail']=row[1];
record['Subject-Code']=row[2];
data.push(record);
}
records.items=data;
var result=JSON.stringify(records);
return ContentService.createTextOutput(result).setMimeType(ContentService.MimeType.JSON);
}
But i want to get this way:
public void getSheetData(){
String strSheetID = editText.getText().toString();
//now i want to here - using strSheetID get json data for specific column
}
Please solve my problem and take millions of thanks.