Display data from Google Sheets into your android app [without authorization]

Sometimes, all we need is to display (read-only) some dynamic data in our application, and setting up a server and API, or even Firebase for such a purpose could be an unnecessary hassle. It would be simpler to just put the data into a Google Spreadsheet, and read it into your app. This way it can be updated anytime you want.

For complete read/write functionalities, Google Sheets API requires authorization from the user. But for our purpose we have to display public read-only data, which is simpler, using an API key.

So before we begin, we need the API key for making requests to the Google Sheets API.

Get API Key from Google

  1. Create a project in Google Developer Console. Go to the website, log in with your Google account, and create a new project.
  2. Go to APIs and Services, and click on ENABLE APIS AND SERVICES
  3. Select Google Sheets API and ENABLE it
  4. Go back to APIs and Services, and select Credentials.
  5. Click on Create credentials button
  6. From the dropdown, select API Key.
  7. Copy the key.

Get spreadsheet ID & change its access

Go to Google Spreadsheets.

  1. Create a new sheet.
  2. Check the URL and copy the spreadsheet ID. https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId
  3. On the right, click on the green Share button.
  4. Click on advanced settings.
  5. Under Who can access, change the setting to Public on the web – Anyone on the Internet can find and view

Set up your project

I am assuming you have created a new Android project. Save the IDs in a Config file.

public class Config {
    public static String google_api_key = "your_api_key";
    public static String spreadsheet_id = "spread_sheet_id";
}

In your app level build.gradle, add these 2 dependencies

implementation 'com.google.apis:google-api-services-sheets:v4-rev516-1.23.0'
implementation 'com.google.api-client:google-api-client-android:1.23.0'

Don’t forget to add INTERNET permission to your AndroidManifest.xml.

<uses-permission android:name="android.permission.INTERNET"/>

Initialize sheets service

In your activity, initialize the Google sheets service

HttpTransport transport = AndroidHttp.newCompatibleTransport();
JsonFactory factory = JacksonFactory.getDefaultInstance();
final Sheets sheetsService = new Sheets.Builder(transport, factory, null)
        .setApplicationName("My Awesome App")
        .build();
final String spreadsheetId = Config.spreadsheet_id;

You must always call the read functions on a new thread since Android doesn’t allow network calls on the main thread.

new Thread() {
    @Override
    public void run() {
        try {
            //you call here
        }
        catch (IOException e) {
            Log.e("Sheets failed", e.getLocalizedMessage());
        }
    }
}.start();

For retrieving values, let us first understand how the sheet and range are specified in our queries. According to Google’s docs,

A1 notation

Some API methods require a range in A1 notation. This is a string like 

Sheet1!A1:B2

, that refers to a group of cells in the spreadsheet, and is typically used in formulas. For example, valid ranges are:

  • Sheet1!A1:B2

     refers to the first two cells in the top two rows of Sheet1.

  • Sheet1!A:A

     refers to all the cells in the first column of Sheet1.

  • Sheet1!1:2

     refers to the all the cells in the first two rows of Sheet1.

  • Sheet1!A5:A

     refers to all the cells of the first column of Sheet 1, from row 5 onward.

  • A1:B2

     refers to the first two cells in the top two rows of the first visible sheet.

  • Sheet1

     refers to all the cells in Sheet1.

Named ranges are also supported. When a named range conflicts with a sheet’s name, the named range is preferred.

If the sheet name has spaces or starts with a bracket, surround the sheet name with single quotes (

'

), e.g 

'Sheet One'!A1:B2

. For simplicity, it is safe to always surround the sheet name with single quotes.

Retrieve a single range of values

String range = "Sheet1!A1:B2"
ValueRange result = sheetsService.spreadsheets().values()
        .get(spreadsheetId, range)
        .setKey(Config.google_api_key)
        .execute();
int numRows = result.getValues() != null ? result.getValues().size() : 0;
Log.d("SUCCESS.", "rows retrived " + numRows);

Retrieving multiple ranges

List<String> ranges = Arrays.asList(
        "MyFirstSheet!A1:B2",  //replace with your sheet name
        "Sheet2!A3:B5"
);
BatchGetValuesResponse result = sheetsService.spreadsheets().values()
        .batchGet(spreadsheetId)
        .setKey(Config.google_api_key)
        .setRanges(ranges).execute();
Log.d("success.", "ranges retrieved: " + result.getValueRanges().size());

 

 

 

 

 


Also published on Medium.

By |2019-04-03T03:57:09+00:00March 14th, 2019|Categories: Android Tutorials|0 Comments

Leave A Comment