Google-sheets – Reset drop-downs

google sheets

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.

Best Answer

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

After

Code

// create menu item
function onOpen() {
  createMenu('Validation', 'Add validation to active cell', 'myValidation');
}

// add validation to active cell
function myValidation() {
  var ss = SpreadsheetApp.getActive(), output = [];

  // create input box or prompt
  var int = getPrompt('How many ranges do you want to combine?', 'Add integer');

  // collect data
  output = getData(1);
  for(var i=1; i<int; i++) {
    output.push.apply(output, getData(i+1));
  }

  // build validation
  var rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(output.sort(sortNumber)).build();
  ss.getActiveCell().setDataValidation(rule);
}

// show prompt and retrieve the corresponding data
function getData(int) {
  var ss = SpreadsheetApp.getActive(), text, values;

  // create input box or prompt
  var text = getPrompt('Range like A1:A4 or Sheet1!A1:A4', 'Range ' + int).split('!');  

  // check if reference to other sheet is made  
  if(text.length > 1) {    
    values = ss.getSheetByName(text[0]).getRange(text[1]).getValues();
  } else {
    values = ss.getRange(text).getValues();
  } 

  // check for entries
  if(values[0].length > 1) {
    throw 'Only single columns are to be used';
    return;
  } 

  // return the data
  return values;    
}

function createMenu(menuName, itemName, funcName) {
  try {
    SpreadsheetApp.getUi()
      .createMenu(menuName).addItem(itemName, funcName).addToUi();
  } catch(e) {
    SpreadsheetApp.getActive()
      .addMenu(menuName, [{name: itemName, functionName: funcName}]);    
  }
}

function getPrompt(title, header) {
  // create input box or prompt
  try {
    return SpreadsheetApp.getUi().prompt(title, header, app.ButtonSet.OK).getResponseText();
  } catch(e) {
    return Browser.inputBox(title, header, Browser.Buttons.OK);
  }
}

// http://www.devcurry.com/2010/08/sort-numeric-array-in-javascript.html
function sortNumber(num1, num2) {
  return num1 - num2;
}

Example

I've created an example file for you: validator from two ranges
Add the script under Tools>Script editor and press the bug button to activate the authentication.

Google Sheets – Disable Drag & Drop in Google Sheets

It's not possible to disable drag & drop while keeping the range editable. The alternative is to use other mean to add data to the spreadsheet that could be:

Use an secondary spreadsheet and import the data by using IMPORTRANGE
Use a Google Form
Use a Custom form. This requires the use of Google Apps Script or the Google Sheets API.
Publish the data to a web page or other web resource an use IMPORTHTML, IMPORTXML, IMPORTDATA, IMPORTFEED

Best Answer

Related Solutions

Google Sheets Dropdown – How to Create a Single Dropdown with Data from Separate Columns

Code

Example

Google Sheets – Disable Drag & Drop in Google Sheets

Related Topic