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.

    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})
    

    Note:

    References: