0

I have created several drop-down menus, what I would like to know is, is it possible and if so how do I create a script so that once a user has finished with the sheet they press a button or it self resets itself to the top value or better blank as per the black drop-downs in the screenshot?

I have attached a simple screenshot of the tabs in question, if you need more info please ask.

screenshot of drop-down cells

1 Answer 1

2

You want a user to be able to clear a series of dropdown cells by clicking a button.

An excellent answer is found in Google Support from Dec 2016. Since this is an external source, I have adapted and expanded it here. Props to "Munkey (David)" who supplied the original answer.

The script to clear/reset the dropdown value is very simple and uses the method.clearContent(). When the dropdown cells are cleared, the values for "Position score" and "Position Explanation" (which, in my test data, are based on VLOOKUP formula) become blank by virtue of using IFERROR in conjunction with the VLOOKUP.

However... you have several ranges to reset and there are options for describing those ranges.

1 - refer to specified range (such as "B5:F5"). This will work well, but in the event that you insert a row, expand the spreadsheet, etc, then the script would need to be edited.

2 - refer to a named range (such as "DropdownSet1"). A named range will be updated as rows are added or deleted, and this avoids editing of the script.

This solution doesn't offer a button option; instead the trigger to clear the dropdowns is included in a custom menu. You should delete whichever option you don't wish to use.


// Reference: https://support.google.com/docs/forum/AAAABuH1jm0wOgLAug3DbY
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Clear Dropdowns')
      .addItem('Clear by Range name', 'clearDropdownRangesByName')
      .addItem('Clear by Cell reference', 'clearDropdownRangesByCell')
      .addToUi();
}

function clearDropdownRangesByName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);

  var dropdownNames = ["DropdownSet1","DropdownSet2","DropdownSet3"];

  for (var i=0;i<dropdownNames.length;i++){
    sheet.getRange(dropdownNames[i]).clearContent();
  }  
}

function clearDropdownRangesByCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);

  var dropdownRanges = ["B5:F5","B11:F11","B17:F17"];

  for (var i=0;i<dropdownRanges.length;i++){
    sheet.getRange(dropdownRanges[i]).clearContent();
  }  
}

Before

Before


After

After screenshot


8
  • Hi Tedinoz Thanks for the response, I have had a go but I don't see the results so I expect I have messed up. You will have to excuse me as I self teach myself and this is my first look into scripts! If I send a link to my spreadsheet here would you be able to take a look for me and adjust the script as needed? Thanks –
    – gamer
    Commented Mar 29, 2020 at 9:38
  • Sure thing. You should be aware that sharing your spreadsheet will unavoidably expose your email address.
    – Tedinoz
    Commented Mar 29, 2020 at 9:59
  • Thanks - I have set everything but the drop downs to protected so if you need access just let me know, I have no issue with email being exposed. docs.google.com/spreadsheets/d/…
    – gamer
    Commented Mar 29, 2020 at 10:54
  • Morning Tedinoz. Have you had a chance to take a look at this yet? if not it's fine, I only ask because I am leaving it alone until I know the script is working so not to interfere at the wrong time :).
    – gamer
    Commented Mar 30, 2020 at 8:10
  • You have protected ranges in that sheet - that's why the script isn't working.
    – Tedinoz
    Commented Mar 30, 2020 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.