CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-sheets-apigoogle-api-python-client
jwillis0720
jwillis0720

Reputation: 4477

Google Sheet API - Get Data Validation

I'm trying to set data validation rules for my current spreadsheet. One thing that would help me would to be able to view the rules in JSON from data validation rules I have already set (In the spreadsheet UI or within an API call).

Example.

request = {
      "requests": [
        {
          "setDataValidation": {
            "range": {
              "sheetId": SHEET_ID,
              "startRowIndex": 1,
              "startColumnIndex": 0,
               "endColumnIndex":1
            },
            "rule": {
              "condition": {
                "type": "BOOLEAN"},
              "inputMessage": "Value MUST BE BOOLEAN",
              "strict": "True"
            }
          }
        }
      ]
    }

service.spreadsheets().batchUpdate(spreadsheetId=SPREADSHEET_ID body=request).execute()

But what API calls do I use to see the Data Validation on these range of cells? This is useful for if I set the Data Validation rules in the spreadsheet and I want to see how google interprets them. I'm having a lot of trouble setting complex Datavalidations through the API.

Thank you

Upvotes: 2

Views: 3823

Answers (2)

tehhowch
tehhowch

Reputation: 9872

To obtain only the "Data Validation" components of a given spreadsheet, you simply request the appropriate field in the call to spreadsheets.get:

service = get_authed_sheets_service_somehow()

params = {
  spreadsheetId: 'your ssid',
  #range: 'some range',
  fields: 'sheets(data/rowData/values/dataValidation,properties(sheetId,title))' }

request = service.spreadsheets().get(**params)
response = request.execute()

# Example print code (not tested :p )
for sheet in response['sheets']:
  for range in sheet['data']:
    for r, row in enumerate(range['rowData']):
      for c, col in enumerate(row['values']):
        if 'dataValidation' in col:
          # print "Sheet1!R1C1" & associated data validation object.
          # Assumes whole grid was requested (add appropriate indices if not).
          print(f'\'{sheet["properties"]["title"]}\'!R{r}C{c}', col['dataValidation'])

By specifying fields, includeGridData is not required to obtain data on a per-cell basis from the range you requested. By not supplying a range, we target the entire file. This particular fields specification requests the rowData.values.dataValidation object and the sheetId and title of the properties object, for every sheet in the spreadsheet.

You can use the Google APIs Explorer to interactively determine the appropriate valid "fields" specification, and additionally examine the response: https://developers.google.com/apis-explorer/#p/sheets/v4/sheets.spreadsheets.get

For more about how "fields" specifiers work, read the documentation: https://developers.google.com/sheets/api/guides/concepts#partial_responses

(For certain write requests, field specifications are not optional so it is in your best interest to determine how to use them effectively.)

Upvotes: 3

jwillis0720
jwillis0720

Reputation: 4477

I think I found the answer. IncludeGridData=True in your spreadsheet().get

from pprint import pprint    
response = service.spreadsheets().get(
        spreadsheetId=SPREADSHEETID, fields='*',
        ranges='InputWorking!A2:A',includeGridData=True).execute()

You get a monster datastructure back. So to look at the very first data in your range you could do.

pprint(response['sheets'][0]['data'][0]['rowData'][0]['values'][0]['dataValidation'])


{'condition': {'type': 'BOOLEAN'},
 'inputMessage': 'Value MUST BE BOOLEAN',
 'strict': True}

Upvotes: 2

Related Questions