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

Question

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!

Have you tried the solution provided in this [SO thread](http://stackoverflow.com/questions/38123383/how-to-get-sheet-name-of-individual-sheets-in-google-spreadsheet-google-sheet)? They used service.spreadsheets().get(spreadsheetId).setIncludeGridData (false) .execute (); and then parsed the response using the getSheets() function. — ReyAnthonyRenacia, Apr 13 '17 at 11:15
@noogui I don't have a spreadsheetId to pass. I'd hate to create a sheet just to find a sheet. Thanks tho. — LimaNightHawk, Apr 13 '17 at 12:51
@SebastianSchneider Just for clarity, when I say "v4" I'm referring to the google sheets api version (https://developers.google.com/sheets/api/) not the android version. — LimaNightHawk, Apr 13 '17 at 20:18

LimaNightHawk · Accepted Answer · 2017-04-24T12:36:44.913

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();

I followed your code example, But I get 403, Insufficient Permission error message. I have implemented GoogleSheets API, which works before this code changes. — Manikandan, Apr 21 '17 at 12:46
Ah, yes! I bet your lacking the the correct SCOPES. You probably need the `DRIVE_METADATA_READONLY` scope. I'll update the answer to include this. (I forgot that this was one of the steps I made when I was getting this to work.) — LimaNightHawk, Apr 22 '17 at 16:29

score 2 · Answer 2 · answered Apr 17 '17 at 21:49

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 :)

Updated question to be more clear about "sheet" vs. "spreadsheet." Great tip about the mimeType (updated answer to include that as a search criteria). Finally, You do NOT need the sheet qualifier in the range. The range setting is from tested code and works. — LimaNightHawk, Apr 18 '17 at 12:34
If the spreadsheet contains only one sheet then I trust you it works without specifying the sheet in the range; however if the spreadsheet contains multiple sheets (which is probably the most frequent case), then it wouldn't know which sheet to choose from. — flo5783, Apr 18 '17 at 16:29

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

2 Answers2

Linked