How to add a nested list with gspread?

Can someone help me please with implementation of a nested list via gspread? Screenshot of what i want to do.

Upvotes: 1

Answers (3)

donor

Reputation: 41

Was looking for a convenient way to the same and found https://github.com/robin900/gspread-formatting.

Here is the part where Data Validation rules are described: https://github.com/robin900/gspread-formatting#getting-and-setting-data-validation-rules-for-cells-and-cell-ranges

To install run pip install gspread-formatting. Hope that helps!

Upvotes: 0

iampre409

Reputation: 357

If you just want a drop down list that you can edit manually, you can do it with no code. Here are the steps:

#1 Select the cell you want the list to be, then go to Data > Data Validation:

#2 Select "List of Items" under criteria:

#3 Then type in your values, separated by a comma:

#4 Press save, and click the carrot to see your drop down list.

Upvotes: 0

Tanaike

Reputation: 201553

I believe your goal and your current situation as follows.

You want to create a dropdown list in a cell.
- From your following sample image and "a nested list" in your question, I understood like this.
You want to achieve this using gspread of python.
You have already been able to get and put values for Google Spreadsheet using Sheets API.

In this case, I think that it is required to use the batchUpdate method.

Sample script:

In this sample script, please use your script for retrieving credentials of client = gspread.authorize(credentials). And please set the variables. When you run this script, the dropdown list is created in the cell "A1" of "Sheet1" in spreadsheetId. The dropdownlilst is from your sample image.

client = gspread.authorize(credentials)
spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet ID.

spreadsheet = client.open_by_key(spreadsheetId)
sheetId = spreadsheet.worksheet(sheetName).id
body = {
    "requests": [
        {
            "updateCells": {
                "range": {
                    "sheetId": sheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 1,
                    "startColumnIndex": 0,
                    "endColumnIndex": 1
                },
                "rows": [
                    {
                        "values": [
                            {
                                "dataValidation": {
                                    "condition": {
                                        "values": [
                                            {
                                                "userEnteredValue": "help"
                                            },
                                            {
                                                "userEnteredValue": "me"
                                            },
                                            {
                                                "userEnteredValue": "please"
                                            }
                                        ],
                                        "type": "ONE_OF_LIST"
                                    },
                                    "showCustomUi": True
                                }
                            }
                        ]
                    }
                ],
                "fields": "dataValidation"
            }
        }
    ]
}
spreadsheet.batch_update(body)

Result:

When above script is run, the following result is obtained.

Note:

This sample script supposes that you have already been able to get and put values for Google Spreadsheet using Sheets API. Please be careful this.

References:

Upvotes: 3