search - reading google sheet rows marked with certain colors - Stack Overflow

admin管理员组

文章数量:1123694

I am creating a Java application where I need to read google sheets, create objects from the row values and store them in the database. The problem is that I need to read not the whole sheet (tens of thousands rows) but only rows that are highlighted with certain colors. I did not find in the google docs how to set up this search criteria. If someone has done something similar, I will appreciate some pointers.

I am creating a Java application where I need to read google sheets, create objects from the row values and store them in the database. The problem is that I need to read not the whole sheet (tens of thousands rows) but only rows that are highlighted with certain colors. I did not find in the google docs how to set up this search criteria. If someone has done something similar, I will appreciate some pointers.

• google-sheets
• search
• colors

Share Improve this question Follow asked yesterday Gary GreenbergGary Greenberg 1,14488 silver badges1515 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Getting Values based on Background Color

As far as I know in the current stages of the Google Sheets API V4, getByDataFilter doesn't support filters using Cell Formatting. This could be processed from the Response received from Method: spreadsheets.get. By setting Parameters of range depending on your project and includeGridData to True.

Here's a sample response leaving behind part of the response which is not needed on your question.

Sample Response for Each Cell

{
                  "userEnteredValue": {
                    "stringValue": "PERSON A"
                  },
                  "effectiveValue": {
                    "stringValue": "PERSON A"
                  },
                  "formattedValue": "PERSON A",
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    },
                    "padding": {
                      "top": 2,
                      "right": 3,
                      "bottom": 2,
                      "left": 3
                    },
                    "horizontalAlignment": "LEFT",
                    "verticalAlignment": "BOTTOM",
                    "wrapStrategy": "OVERFLOW_CELL",
                    "textFormat": {
                      "foregroundColor": {},
                      "fontFamily": "Arial",
                      "fontSize": 10,
                      "bold": false,
                      "italic": false,
                      "strikethrough": false,
                      "underline": false,
                      "foregroundColorStyle": {
                        "rgbColor": {}
                      }
                    },
                    "hyperlinkDisplayType": "PLAIN_TEXT",
                    "backgroundColorStyle": {
                      "rgbColor": {
                        "red": 1,
                        "green": 1,
                        "blue": 1
                      }
                    }
                  }
                }

References

spreadsheet.Get

本文标签： searchreading google sheet rows marked with certain colorsStack Overflow