Google Sheet If dropdown

Question

I have drop down for column A where I want to select value from drop down only for certain times (ie. 3 times), after I select value from dropdown 3 times, if I try to select it 4th time, value should be removed from dropdown or not able to select. Is this possible using excel or google sheet?

https://docs.google.com/spreadsheets/d/1nbXAkK565V24KDTAzE68q8rQgQWzn-jDJz_6piNYyEw/edit?usp=sharing

In above google sheet, I had selected Red 3 times, now if I want to select Red 4th time, I should not be able to select or Red should be removed from list.

I know using excel VBA, I can do same using below code, but can we add same to google sheets?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lVal As Long
Dim check2 As Long

If Target.Count > 2 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal

  lVal = Application.WorksheetFunction _
        .CountIf(Columns(Target.Column), _
            "*" & newVal & "*")
  If lVal > 3 Then
    If newVal = "" Then
        'do nothing
    Else
        MsgBox "Not available"
        Target.Value = oldVal
    End If

  Else
    If Target.Column >= 47 And Target.Column <= 56 Then
      If oldVal = "" Then
        'do nothing
        Else
        If newVal = "" Then
        'do nothing
        Else
        Target.Value = oldVal _
          & ", " & newVal
        End If
      End If
    End If
  End If

End If



exitHandler:
  Application.EnableEvents = True
End Sub

Could you please provide a Google sheet sample with your issue? — Mateo Randwolf, Feb 25 '20 at 14:49
https://docs.google.com/spreadsheets/d/1nbXAkK565V24KDTAzE68q8rQgQWzn-jDJz_6piNYyEw/edit?usp=sharing In above google sheet, I had selected Red 3 times, now if I want to select Red 4th time, I should not be able to select or Red should be removed from list. — mkpatel299, Feb 25 '20 at 16:09
Just to make sure, so you basically don't want to have Red more than 3 times selected? OR you don't want anything more than 3 dropdowns selected? To make it more clear, are you ok if it would be Red Red Blue Blue for instance? — Mateo Randwolf, Feb 26 '20 at 08:18
@MateoRandwolf : Yes, it could be anything like Red, Blue, Red, White, Red, Black, etc. But once any of this(Drop down list (Red, black, White, etc)) is selected 3 times, it should be removed from dropdown or should not be able to select (disable). — mkpatel299, Feb 26 '20 at 14:00
According to the [documetation](https://developers.google.com/apps-script/reference/spreadsheet/data-validation) there is no way of diabling a data validation field (dropdown). Why don't you just use three dropdowns ensuring therefore that only 3 will be selected as a maximum? — Mateo Randwolf, Feb 26 '20 at 14:29
Thanks @MateoRandwolf . Using Excel I can do that by above VBA code so I thought we have same solution for Google sheets as well, but looks like we don't have. — mkpatel299, Feb 26 '20 at 14:35
Yes, I dont think there is no way in Apps Scripts at least to disable a dropdown without setting it to ```null``` as I stated in my answer. Have you considered using the Sheets API ? If so maybe we could take a further look at [this](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#setdatavalidationrequest) better explained in [this SO post](https://stackoverflow.com/a/43442775/12835757) and see if there we would have a possibility. Let me know what you think about this. — Mateo Randwolf, Mar 11 '20 at 09:12

score 0 · Answer 1 · answered Mar 04 '20 at 09:10

0

So after doing some research I found the way of achieving what you were aiming for here.

Solution

In the class data validation it mentions a method to set the validation to null within the range class. You can check more details about this method here. In that method it mentions that if the parameter is set to null, it will disable the data validation (dropdowns) therefore not allowing to select other values.

Here is a piece of sample code self explained with comments to achieve what you want in your specific case:

function onEdit(){
  // get the sheet and values to check if in that range there are more than 3 elements selected
  var ss =  SpreadsheetApp.getActiveSheet();
  var values = ss.getRange('A1:A18').getValues();
  // this variable is for counting the amount of elements selected
  var count = 0;
  
  for(i=0;i<values.flat().length;i++){
    // if an element in that range is not empty
    if(values.flat()[i]!=''){
      count++;
    }
  }
  
  // if the count is over 3 then disable the dropdowns
  if(count>3){
    ss.getRange('A1:A18').setDataValidation(null);
  }  
}

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

answered Mar 04 '20 at 09:10

Mateo Randwolf

2,823
1
6
17

Thanks @Mateo - but in above code last line where you mentioned ss.getRange('A1:A18').setDataValidation(null); is deleting other cell's list dropdown. Instead of Null, what else we can use do it only count Red as 3 times and remove from dropdown list for remaining cell list? – mkpatel299 Mar 04 '20 at 14:27
So do you basically want to keep the dropdowns where you have selected the three reds adn remove them from the rest of the cells where nothing was selected? – Mateo Randwolf Mar 04 '20 at 14:31
Keep dropdown for all cell, just remove Red from dropdown list or when you select Red, it should say Not allowed when Red is selected for 3 times. – mkpatel299 Mar 04 '20 at 17:45
For achieving that [check the information in the documentation](https://developers.google.com/apps-script/reference/spreadsheet/range#setdatavalidationrule) and change my code appropiately. It might be useful to pass the event in ```onEdit(e)``` and then get the range of the changed cell ```e.range``` to be able to modify the validation rules on that cell if the count is higher than 3. Please, instead of demanding users to build code for you ask relevant questions following the [Stack Overflow Guidelines](https://stackoverflow.com/help/how-to-ask). Thank you :) – Mateo Randwolf Mar 05 '20 at 09:52

Google Sheet If dropdown

1 Answers1

Solution