CODEWITHSUNDEEP
javagoogle-sheets-api
Matthew
Matthew

Reputation: 841

How to get full A1 notation of NamedRange?

How does a Java program get the full A1 notation of a Google Sheets NamedRange, including its <sheetName>! prefix?

To read a range of cells in a sheet, call Spreadsheets.Values.get(String spreadsheetId, String range) where range is in A1 notation. But when getting a range as a NamedRange there seems to be no way to get its sheet name (AKA sheet title) for prefixing in conversion to A1 notation.

It seems the only way supported by the API would be to get the NamedRange's embedded GridRange's sheetID with NamedRange.getRange.getSheetId(), then iterate through the spreadsheet's sheets with Spreadsheet.getSheets() until one sheet's getProperties().getSheetId() matches the GridRange's sheetID. But NamedRange.getRange.getSheetId() almost always returns null rather than the gid=<sheetID> from a sheet's URL. Indeed I don't understand why the call only intermittently returns the correct sheetID rather than returning null.

Upvotes: 1

Views: 1722

Answers (2)

Andrew Rybka
Andrew Rybka

Reputation: 23

sheetId field of GridRange object is set to NULL when a corresponding named range was defined without a sheet part (i.e. just A1:B2 instead of Sheet1!A1:B2).

A1 notation chapter of Google API docs states that:

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

Upvotes: 0

Matthew
Matthew

Reputation: 841

I ran the program again that contained the NamedRange.getRange.getSheetId() that was failing to reliably return a sheetID value (instead returning null). It had been returning null all day, after returning the correct sheetID once yesterday among otherwise null. And now it's reliably returning the sheetID.

The problem here looks like the Google Sheets API Spreadsheets webservice is intermittently failing, without any other sign, without any confession of such from Google. The Java API, especially for managing a NamedRange, seems to be so infrequently used if you google for people reporting even success using it that there's no mentions of it failing.

This is of course the worst kind of error. I can't even reproduce it now to report it to Google, which is often an exercise in futility anyway. I'll have to keep testing it until it fails reliably and then report it to Google, then wait forever to find out they're never fixing it.

The appended code fails. I commented the getSheetId() line that was failing but then started working OK.

private static String getRangeNameA1Str(String spreadsheetId, String rangeNameStr)
    throws IOException
{
    // Get spreadsheet by its ID.
    Spreadsheet spreadsheet = sheetsService.spreadsheets().get(spreadsheetId).execute();

    // Get named range.
    NamedRange namedRange =
        spreadsheet.getNamedRanges().parallelStream()
            .filter(aRange -> aRange.getName().equals(rangeNameStr))
            .findFirst()
            .get();

    // Get named range's GridRange for its A1 notation components.
    GridRange gridRange = namedRange.getRange();

    // Get named range's sheet's name.
    Integer  sheetID = gridRange.getSheetId(); // *** This call was getting null but then started getting the correct sheet ID.
    String sheetName =
        spreadsheet.getSheets().parallelStream()
            .filter(aSheet -> aSheet.getProperties().getSheetId().equals(sheetID))
            .findFirst()
            .get()
            .getProperties()
            .getTitle();

    // Get named range's A1 notation from its GridRange's components.
    String rangeA1Str =
        sheetName + "!" +
        Character.toString((char)((int)gridRange.getStartColumnIndex()+65)) +
        (gridRange.getStartRowIndex()+1) +
        ":" +
        Character.toString((char)((int)gridRange.getEndColumnIndex()+65)) +
        gridRange.getEndRowIndex();


    return(rangeA1Str);
} // getNamedRangeA1String
public static Sheets getSheetsService()
    throws IOException
{
    Credential credential = authorize();
    return new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential)
    .setApplicationName(APPLICATION_NAME)
    .build();
} // getSheetsService

Upvotes: 1

Related Questions