Google Sheets API .NET v4 Creating Drop-down list for a grid cell

Question

I'm trying to create a drop-down list on a grid. I was following the advice on this page. However, I'm not able to enter any string values for UserEnteredValue. The underlined error I'm getting:

Cannot implicitly convert type 'Google.Apis.Sheets.v4.Data.ConditionValue' to 'System.Collections.Generic.IList'.An explicit conversion exists(are you missing a cast?)

    public Request createDataValidationRequest(int sheetID, int startRow, int endRow, int startColumn, int endColumn)
    {
        var updateCellsRequest = new Request() {
            SetDataValidation = new SetDataValidationRequest()
            {
                Range = new GridRange()
                {
                    SheetId = sheetID,
                    StartRowIndex = startRow,
                    StartColumnIndex = startColumn,
                    EndRowIndex = endRow,
                    EndColumnIndex = endColumn
                },
                Rule = new DataValidationRule()
                {
                    Condition = new BooleanCondition()
                    {
                        Type = "ONE_OF_LIST",
                        Values = new ConditionValue()
                        {
                            UserEnteredValue = "awer"
                        }
                    },
                    InputMessage = "Select an Option",
                    ShowCustomUi = true,
                    Strict = true
                }
            }
        };
        return updateCellsRequest;
    }

You cannot assign a single value (ConditionValue) to a list (Values). — Jeroen Heier, Aug 02 '18 at 17:09
Consider adding your solution as an answer if you think it will be useful, or deleting your question if not. — tehhowch, Aug 03 '18 at 02:34

score 0 · Answer 1 · answered May 02 '20 at 23:32

I was able to get the following, for my situation to work.

                        SpreadsheetsResource.GetRequest request = _sheetsService.Spreadsheets.Get(newTimeSheet.Id);
                        Spreadsheet spreadsheet = request.Execute();
                        SheetProperties timeSheetProperties = new SheetProperties();
                        for (int z = 0; z < spreadsheet.Sheets.Count; z++)
                        {
                            SheetProperties sheetProperties = spreadsheet.Sheets[z].Properties;
                            if (sheetProperties.Title == "3c TIME")
                            {
                                timeSheetProperties = sheetProperties;
                                break;
                            }
                        }
                        var updateCellsRequest = new Request()
                        {
                            SetDataValidation = new SetDataValidationRequest()
                            {
                                Range = new GridRange()
                                {
                                    SheetId = timeSheetProperties.SheetId,
                                    StartRowIndex = 2,
                                    StartColumnIndex = 0,
                                    EndColumnIndex = 1
                                },
                                Rule = new DataValidationRule()
                                {
                                    Condition = new BooleanCondition()
                                    {
                                        //Type = "ONE_OF_RANGE",
                                        Type = "ONE_OF_LIST",
                                        Values = new List<ConditionValue>()
                                    {
                                        new ConditionValue()
                                        {
                                            UserEnteredValue = "YES",
                                        },
                                        new ConditionValue()
                                        {
                                            UserEnteredValue = "NO",
                                        },
                                        new ConditionValue()
                                        {
                                            UserEnteredValue = "MAYBE",
                                        }
                                    }
                                    },
                                    InputMessage = "Select an Option",
                                    ShowCustomUi = true,
                                    Strict = true
                                }
                            }
                        };
                        var requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
                        var requests = new List<Request>();
                        requests.Add(updateCellsRequest);
                        requestBody.Requests = requests;
                        var batchRequest = _sheetsService.Spreadsheets.BatchUpdate(requestBody, newTimeSheet.Id);
                        batchRequest.Execute();

Google Sheets API .NET v4 Creating Drop-down list for a grid cell

1 Answers1