7

Using Google Sheets API python How to add a dropdown list to google sheet with list items [YES. NO, MAYBE] for an invite asking friends if they will attend an event.

I looked at google developer sheets api documentation HERE and no example was provided.

Looking for the JSON structure.

The result would be something like this :

enter image description here

Thank you!

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
mongotop
  • 7,114
  • 14
  • 51
  • 76

1 Answers1

18

I found the trick inside the setDataValidation property and choosing ONE_OF_LIST as the condition type and all I had to do is providing the list of items inside the value list

{
  "setDataValidation": {
    "range": {
      "sheetId": sheet_id,
      "startRowIndex": 1,
      "endRowIndex": 1,
      "startColumnIndex": 22,
      "endColumnIndex": 23
    },
    "rule": {
      "condition": {
        "type": 'ONE_OF_LIST',
        "values": [
          {
          "userEnteredValue": 'YES',
          },
          {
          "userEnteredValue": 'NO',
          },
          {
          "userEnteredValue": 'MAYBE',
          },
        ],
      },
      "showCustomUi": True,
      "strict": True
    }
  }
},
mongotop
  • 7,114
  • 14
  • 51
  • 76
  • 2
    worth mentioning that this type of data validation rule do not accept more than 500 elements into the list. – darul75 Sep 13 '19 at 09:41
  • 1
    Here is the doc https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#setdatavalidationrequest – Vlad Dec 15 '19 at 14:19
  • can I pass the entire dictionary above to `spreadsheet_obj.update_ranges`? – Ciasto piekarz Feb 05 '21 at 16:47