Get data from google sheet in JSON format using sheet ID - android

Viewed 619

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.

0 Answers
Related