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.
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!
I believe your goal is as follows.
In this case, how about the following 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})