How To Read The Color Of A Cell In Google Sheets

I'm using the Python Google Sheets API, and I'd like to read the color of an individual cell. I've read the docs, but I could only find info on how to retrieve the text from cell,

Solution 1:

You can use the Method: spreadsheets.get to get the color of a specific cell.

Just pass the spreadsheetId and make sure you set to true the includeGridData parameter.

For example, this picture specifying the range B1.

By using this spreadsheets.get, you will get here a result of "backgroundColor": {"green": 1}, and not only the background color, you will also get here the fontFamily, fontSize and if it is bold, italic, strikethrough or underline.

You can try it by creating a spreadsheet, then follow the picture above.

Here is the request that I use.

GET https://sheets.googleapis.com/v4/spreadsheets/YOUR_SPREADSHEET_ID?includeGridData=true&ranges=B1&key={YOUR_API_KEY}

Solution 2:

With java:

 List<String> ranges = new ArrayList<>();
            ranges.add("LinksToSearch!D2:D");
            var request = sheetService.spreadsheets().get(SheetsID);
            request.setRanges(ranges);
            request.setIncludeGridData(true);
            var response = request.execute();
            System.out.println(response); //Look at the response

I did not find any resource regarding how to parse it with the given google sheet API methods, so I converted the response to a JSONObject and parsed it myself, respectively to each row: (I only inspect 1 column)

JSONArray jObj = new JSONArray(response.get("sheets").toString());
    jObj = jObj.getJSONObject(0).getJSONArray("data").getJSONObject(0).getJSONArray("rowData");
    System.out.println(jObj.toString()); //All rows as an object

    for (int i = 0; i < jObj.length(); i++) {
        System.out.println(
                jObj.getJSONObject(i).getJSONArray("values").getJSONObject(0).getJSONObject("effectiveFormat").
                        getJSONObject("backgroundColor"));
    }