Dang Nguyen Dang Nguyen - 4 months ago 39
Android Question

How to get data from private google sheet after user sign in with their Google account?

I'm developing an Android app, it include a feature that after user sign in with their Google account we will access their google sheets to get database. I have tried to search for Google Spreadsheet Api but not clear how to use it.

Of course I can get data from public sheets from the code below, but the problem is getting private sheets.

private List<String> getDataFromApi() throws IOException {
String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
String range = "Class Data!A2:E";
List<String> results = new ArrayList<String>();
ValueRange response = this.mService.spreadsheets().values()
.get(spreadsheetId, range)
.execute();
List<List<Object>> values = response.getValues();
if (values != null) {
results.add("Name, Major");
for (List row : values) {
results.add(row.get(0) + ", " + row.get(4));
}
}


return results;
}


I also searched somewhere and found the code below but it throws NoSuchMethodError exception, and I confused what is "username" and "password" in the line spreadsheet.setUserCredentials.

SpreadsheetService spreadsheet= new SpreadsheetService("v1");
spreadsheet.setProtocolVersion(SpreadsheetService.Versions.V3);

try {
spreadsheet.setUserCredentials("username", "password");
URL metafeedUrl = new URL("https://spreadsheets.google.com/feeds/spreadsheets/private/full");
SpreadsheetFeed feed = spreadsheet.getFeed(metafeedUrl, SpreadsheetFeed.class);

List<SpreadsheetEntry> spreadsheets = feed.getEntries();
for (SpreadsheetEntry service : spreadsheets) {
System.out.println(service.getTitle().getPlainText());
}
} catch (AuthenticationException e) {
e.printStackTrace();
}


So anyone know how to do this or have sample code please share me. Thanks in advance!

Answer

Finally I found the solution to my question, and now I post here to help everyone else faces the same problem can overcome it.

  • First of all, go to https://console.developers.google.com and create new OAuth client ID credential associated with your project.

  • Now in your android project, add these libraries in dependencies:

    dependencies {
    compile 'com.google.android.gms:play-services-auth:9.2.1'
    compile files('libs/gdata-client-1.0.jar')
    compile files('libs/gdata-client-meta-1.0.jar')
    compile files('libs/gdata-core-1.0.jar')
    compile files('libs/gdata-core-1.0-sources.jar')
    compile files('libs/gdata-spreadsheet-3.0.jar')
    compile files('libs/gdata-spreadsheet-meta-3.0.jar')
    compile files('libs/guava-10.0.1.jar')
    compile files('libs/jsr305-1.3.9.jar')
    }

  • In AndroidManifest.xml add these permissions:
    uses-permission android:name="android.permission.INTERNET"
    uses-permission android:name="android.permission.GET_ACCOUNTS"

Also add this in <application></application> tag :

<meta-data
            android:name="com.google.android.gms.version"
            android:value="@integer/google_play_services_version" />
  • And the CODE is as follows:

public class DownloadDatabaseActivity extends AppCompatActivity {

private static final String TAG = "my_log";
public static ProgressDialog mProgressDialog;
String spreadsheetName;
String worksheetName;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_download_database);

    Button btnDownload = (Button) findViewById(R.id.btnDownload);
    btnDownload.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            EditText etSpreadsheetName = (EditText) findViewById(R.id.etSpreadsheetName);
            EditText etWorksheetName = (EditText) findViewById(R.id.etWorksheetName);
            spreadsheetName = etSpreadsheetName.getText().toString();
            worksheetName = etWorksheetName.getText().toString();

            Intent intent = AccountPicker.newChooseAccountIntent(null, null,
                    new String[]{"com.google"}, false, null, null, null, null);
            startActivityForResult(intent, 1);
        }
    });
}

@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
    if (requestCode == 1 && resultCode == RESULT_OK) {
        Log.d(TAG, "--> enter first if");

        final String accountName = data.getStringExtra(AccountManager.KEY_ACCOUNT_NAME);
        new DownloadTask(this).execute(accountName, spreadsheetName, worksheetName);

    } else if (requestCode == 2 && resultCode == RESULT_OK) {
        Log.d(TAG, "--> enter second if");
    }
}

////////////////////////////////////////////////////////////////////////////

private class DownloadTask extends AsyncTask<String, Void, String> {

    private Context context;

    // constructor
    public DownloadTask(Context context) {
        this.context = context;
    }

    @Override
    protected void onPreExecute() {
        super.onPreExecute();
        showProgressDialog(context);
    }

    @Override
    protected String doInBackground(String... params) {
        String mEmail = params[0]; // your google account name
        String mType = "com.google";
        Account account = new Account(mEmail, mType);
        String scopes = "oauth2:https://spreadsheets.google.com/feeds "
                + "https://www.googleapis.com/auth/plus.login "
                + "https://www.googleapis.com/auth/drive";
        String token = null;
        try {
            token = GoogleAuthUtil.getToken(getApplicationContext(), account, scopes);
        } catch (IOException e) {
            Log.e(TAG, e.getMessage());
        } catch (UserRecoverableAuthException e) {
            Intent recoveryIntent = e.getIntent();
            startActivityForResult(recoveryIntent, 2);
        } catch (GoogleAuthException e) {
            Log.e(TAG, e.getMessage());
        }

        try {
            SpreadsheetService service = new SpreadsheetService("MySpreadsheetIntegration-v1");
            service.setAuthSubToken(token);
            URL SPREADSHEET_FEED_URL = new URL(
                    "https://spreadsheets.google.com/feeds/spreadsheets/private/full");
            SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL, SpreadsheetFeed.class);
            List<SpreadsheetEntry> spreadsheets = feed.getEntries();

            Log.d(TAG, "spreadsheets.size() = " + spreadsheets.size());

            for (SpreadsheetEntry spreadsheet : spreadsheets) { // outer for loop
                if (spreadsheet.getTitle().getPlainText().equalsIgnoreCase(params[1])) { // your spreadsheet name
                    List<WorksheetEntry> worksheets = spreadsheet.getWorksheets();

                    for (WorksheetEntry worksheet : worksheets) { // inner for loop
                        String title = worksheet.getTitle().getPlainText();
                        if (title.equalsIgnoreCase(params[2])) { // your worksheet name
                            Log.d(TAG, "Worksheet name: " + title);
                            URL listFeedUrl = worksheet.getListFeedUrl();
                            ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);
                            Log.d(TAG, "Number of row = " + listFeed.getTotalResults());

                            // scan through each row in worksheet
                            for (ListEntry row : listFeed.getEntries()) {
                                String rowTitle = row.getTitle().getPlainText();
                                ArrayList<String> currentRow = new ArrayList<>();

                                // get elements in current row stored in ArrayList
                                for (String element : row.getCustomElements().getTags()) {
                                    if (!row.getCustomElements().getValue(element).equals(rowTitle))
                                        currentRow.add(row.getCustomElements().getValue(element));
                                }
                                // Log.d(TAG, "currentRow.size() = "+currentRow.size());

                                // do your work here with the data received

                            }
                            break; // break inner
                        }
                    }
                    break; // break outer
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return token;
    }

    @Override
    protected void onPostExecute(String token) {
        Log.d(TAG, "Token Value: " + token);
        hideProgressDialog();
    }
}

}