Search code examples
pythongoogle-sheetsgoogle-sheets-apigspread

Add dropdown menu (nested list) to Google Spreadsheat with gspread / Google Spread API to whole column

I am struggling to insert a dropdown menu into a Google Spreadsheet using the gspread module in python.

This question explains how to get a dropdown menu in a spreadsheet:

How to add a nested list with gspread?

However, even if I change the startColumnIndex and endColumnIndex the dropdown menu only shows up in one cell.

I have experimented with something like this:

data = sheet.get_all_values()
values = [request if e[3] != "" else "" for e in data]
cells = sheet.range("C1:C%d" % len(values))
for i, e in enumerate(cells):
    e.value = values[i]
sheet.update_cells(cells)

where request is the dropdown menu. So I want to insert a dropdown menu in the fourth column if the third column is not empty, else I don't want to insert anything. But as of now, this only works if request is a regular string and not the dropdown formatted cell I want it to be.

This picture shows what I want. You can see that row 1, 3, and 4 have a dropdown list in the fourth column (as the third column is not empy) while row 2 doesn't have anything.

It seems to be that it would be batch to use the batch_update module in combination with request and not the loop but I don't seem to get it working for multiple cells at the same time (preferably a whole column).

Thank you for your help!

Solution

  • I believe your goal is as follows.

    • When column "C" is not empty, you want to insert a dropdown list to column "D".
    • You want to achieve this using gspread for python.

    In this case, how about the following sample script?

    Sample script:

    # Please use your gspread client.

spreadsheetId = "###" # Please set your Spreadsheet ID.
sheetName = "Sheet1" # Please set sheet name.
dropdownOptions = [1, 2, 3, 4, 5]  # This is from your showing image.

spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
sheetId = sheet.id
v = [{"userEnteredValue": str(e)} for e in dropdownOptions]
values = sheet.get_all_values()[1:]
requests = [
    {
        "setDataValidation": {
            "range": {
                "sheetId": sheetId,
                "startRowIndex": i + 1,
                "endRowIndex": i + 2,
                "startColumnIndex": 3,
                "endColumnIndex": 4,
            },
            "rule": {
                "showCustomUi": True,
                "strict": True,
                "condition": {"values": v, "type": "ONE_OF_LIST"},
            },
        }
    }
    for i, r in enumerate(values)
    if r[2] != ""
]
spreadsheet.batch_update({"requests": requests})
    • When this script is run, all values are retrieved and searched in column "C". When column "C" is empty, a dropdown list is inserted into column "D".

    Note:

    • From your showing image, it supposes that your Spreadsheet has the 1st header row. Please be careful about this.

    References: