Find a Google Drive Sheet By Name Using API 4 In Android

11
votes

I followed the "Android Quickstart" below.

https://developers.google.com/sheets/api/quickstart/android

Works great.

But the sample hard-codes a spreadsheetId to an existing spreadsheet. 

String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";

I need to be able to find an existing spreadsheet, by name, and store the id (for later use).

I'd like to do something like this:

private com.google.api.services.sheets.v4.Sheets sheetsService = null;


HttpTransport transport = AndroidHttp.newCompatibleTransport();
JsonFactory jsonFactory = JacksonFactory.getDefaultInstance();

sheetsService = new com.google.api.services.sheets.v4.Sheets.Builder(
        transport, jsonFactory, credential)
        .setApplicationName("My Application Name")
        .build();

String spreadsheetId = null;
List<Spreadsheet> allSpreadsheets = sheetsService.spreadsheets().getAListOfAllSpreadsheets;
for (Spreadsheet spreadsheet : allSpreadsheets) {
    if (spreadsheet.getName().equals("My Sheet")){
        // found!
        spreadsheetId = spreadsheet.getId();
    }
}

Much thanks in advance!

2
androidgoogle-sheetsgoogle-spreadsheet-api
Have you tried the solution provided in this SO thread? They used service.spreadsheets().get(spreadsheetId).setIncludeGridData (false) .execute (); and then parsed the response using the getSheets() function. - noogui
@noogui I don't have a spreadsheetId to pass. I'd hate to create a sheet just to find a sheet. Thanks tho. - LimaNightHawk
what you're doing is not even mentioned in the docs lol - noogui
Really API v4? 99.9% use 14+ - Sebastian Schneider
@SebastianSchneider Just for clarity, when I say "v4" I'm referring to the google sheets api version (developers.google.com/sheets/api) not the android version. - LimaNightHawk

2 Answers

5
votes

It looks like this cannot be done with Sheets API v4.

However...it does look like it can be done with the compatible Google Drive API v3.

Note: the best part about this solution was that I could use the same method of authentication and credential gathering for both APIs. E.g., once I had the code for getting the credentials, I could use it for both API's interchangeably and consecutively.

Here's what I did:

Added this to my build.gradle (shown below my Sheets API declaration)

compile('com.google.apis:google-api-services-sheets:v4-rev468-1.22.0') {
    exclude group: 'org.apache.httpcomponents'
}
compile('com.google.apis:google-api-services-drive:v3-rev69-1.22.0') {
    exclude group: 'org.apache.httpcomponents'
}

I was already using the EasyPermissions method for getting account and credentials. Great example here.

Then...

import com.google.api.services.drive.Drive;
import com.google.api.services.sheets.v4.Sheets;

...

private static final String[] SCOPES = { SheetsScopes.SPREADSHEETS, DriveScopes.DRIVE_METADATA_READONLY };

...

credentials = GoogleAccountCredential.usingOAuth2(getApplicationContext(), Arrays.asList(SCOPES));

...

protected Drive driveService = new Drive.Builder(transport, jsonFactory, credential)
            .setApplicationName("My Application Name")
            .build();

protected Sheets sheetsService = new Sheets.Builder(transport, jsonFactory, credential)
            .setApplicationName("My Application Name")
            .build();

... async:

    Drive.Files.List request = driveService.files().list()
            .setPageSize(10)
            // Available Query parameters here:
            //https://developers.google.com/drive/v3/web/search-parameters
            .setQ("mimeType = 'application/vnd.google-apps.spreadsheet' and name contains 'smith' and trashed = false")
            .setFields("nextPageToken, files(id, name)");

    FileList result = request.execute();

    List<File> files = result.getFiles();
    String spreadsheetId = null;
    if (files != null) {
        for (File file : files) {

            // More code here to discriminate best result, if you want
            spreadsheetId = file.getId();
        }
    }

Then you can directly use the id for the Sheets API:

    ValueRange response = sheetsService.spreadsheets().values().get(spreadsheetId, "A1:B2").execute();
    List<List<Object>> values = response.getValues();
2
votes

It looks to me like you're confusing Spreadsheet and Sheet objects:

  • a Spreadsheet is a file with an Id, accessible by a URL such as https://docs.google.com/spreadsheets/d/yourSpreadsheetId; it is a document stored in a Google Drive folder (like an Excel workbook).

  • a Sheet is a tab in a Spreadsheet, with cells, like a page in your workbook. It has no Id and no direct URL. Well actually the API v4 reference indicate that Sheets also have Ids, but confusingly enough these are just reference numbers that you cannot even access in Google Apps Script, and wouldn't help you in your problem.

So you cannot access a range directly in a Spreadsheet, nor can you find a Sheet by Id.

Similarly enough to your answer, here is what I would suggest:

_ Browse through your Drive files (filter by mimeType == "application/vnd.google-apps.spreadsheet")

__For each Spreadsheet file, browse its Sheets (spreadsheet.sheets[])

___For each Sheet in the current Spreadsheet, check whether it's the one by looking at its name (sheet.title)

Once you found the right sheet, you can get its containing Spreadsheet's Id (spreadsheet.spreadsheetId).

One more thing: at the bottom of your answer, you wrote ValueRange response = sheetsService.spreadsheets().values().get(spreadsheetId, "A1:B2").execute(); You wouldn't be able to access a cell range from a spreadsheet without first specifying the Sheet containing the range: so instead of "A1:B2", you would need "mySheetTitle!A1:B2".

I hope this is clearing up the confusion :)