Posted on by

How to Create Dependent Dropdown on Google Sheets

In this post, I demonstrate how to Create a Dependent Dropdown list on Google Sheets using Google Apps Script.

How to Video:

Video Notes:

  • Legacy Apps Script Editor Used in Video
  • Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets

Code in Video:

function onEdit(e) {
  
  var range = e.range;
  var spreadSheet = e.source;
  var sheetName = spreadSheet.getActiveSheet().getName();
  var column = range.getColumn();
  var row = range.getRow();
  var colorValue = e.value;
  var returnValues = [];
  
  if(sheetName == 'MAIN' && column == 2 && row == 1)
  {
    var ss= SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = ss.getSheetByName("DATA");
    var mainSheet = ss.getSheetByName("MAIN");
    
    //GET SQL STATEMENT
    var lastRowData = dataSheet.getLastRow();    
    for(var i = 2; i <= lastRowData; i++)
    {
     if(colorValue == dataSheet.getRange(i, 1).getValue())
     {
       returnValues.push(dataSheet.getRange(i, 2).getValue());      
     }
    }
    
    mainSheet.getRange('B2').clear();
    var dropdown = mainSheet.getRange('B2');
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(returnValues).build();
    dropdown.setDataValidation(rule);

  }

}

Related Posts

Back Up Google Drive Files How to Back Up Google Drive Files using Apps Script - In this post, I demonstrate how to back up Google Drive Files using Google Apps Script with Google Sheets.
Embed Chart Google Sheets How to Embed Charts on Google Sheets using Apps Script - In this post, I demonstrate How to Create Charts on Google Sheets using Google Apps Script.
Search Google Drive Google Sheets How to Search Google Drive on Google Sheets using Apps Script - In this post, I demonstrate how to search Google Drive by File Name on Google Sheets using Apps Script
Create Custom Menu Google Sheets How to Create a Menu on Google Sheets using Apps Script - In this post, I demonstrate how to Create a Customer Menu on Google Sheets using Google Apps Script.
Get and Set Value Google Sheets How to Get and Set Values on Google Sheets using Apps Script - In this post, I demonstrate how to Get and Set Values using Google Apps Script on Google Sheets.
Create Data Validation Google Sheets How to Create Data Validation on Drop Down using Google Apps Script - In this post, I demonstrate How to Create Data Validation Drop-down List Using Google Apps Script on Google Sheets.
Stock Trend Analysis Google Sheets Create Stock Trend Analyzer on Google Sheets - In this video, I demonstrate how to analyze stock using the GOOGLEFINANACE Function on Google Sheets with Google Apps Script.
Scrape Amazon Website Googe Sheets Scrape Amazon Website to Google Sheets - In this video, I demonstrate how to Scrape Amazon Website to Google Sheets using software called ScraperAPI.
Alert Popups on Google Sheets Create Alert Popups on Google Sheets - In this post, I demonstrate how to create 3 different types of alert popups using Google Apps Script on Google Sheets.
Combine Multiple Spreadsheets Google Sheets Combine Multiple Spreadsheets into One Google Sheet - In this post, I demonstrate how to Combine Multiple Spreadsheets into one Sheet using Google Apps Script.
Last Row and Column Google Sheets How to Get Next Open Row and Column using Google Apps Script - In this post, I demonstrate how to get the next open row and column using Google Apps Script on Google Sheets.
Time and Distance Map Google Sheets How to Get Map Time and Distance using Google Apps Script - In this post, I demonstrate how to get the Map Time and Distance between locations using Google Apps Script using the Map Class.
Call Rest API Google Sheets How to Call Rest API on Google Sheets - In this post, I demonstrate how to call a Rest API using Google Apps Script and Displaying the data on a Google Sheet.
Google Doc From Apps Script How to Create Google Doc from Apps Script - In this post, I demonstrate how to create a Google Doc using Google Apps Script and save it in Google Drive. In this example, I use the data from a Google Sheet.
How to Clear, Delete, Insert, and Replace Row Google Sheets How to Clear, Delete, Insert, and Replace Row using Google Apps Script - In this post, I demonstrate how to clear, delete, insert, and replace rows using Google Apps Script on Google Sheets.
Email Last Update Google Sheet Email Last Update on Google Sheet - In this post, I demonstrate how to email the last update made on a google sheet. This is a way to monitor any changes made on Google Sheets
JSON Web Service Google Sheets How to Call JSON Web Service to Populate Google Sheet - In this post, I demonstrate how to Call a JSON Web Service and Populate a Google Sheet with a table of raw data.
Scrape Website Google Sheet Scrape Website Data to Google Sheets - In this post, I demonstrate how to Scrape data from a Website and import it to a Google Sheet. I used JavaScript and Reg Expressions to accomplish the task.
Query Function Google Sheets Create Report Builder on Google Sheets using QUERY Function - In this post, I demonstrate how to use the QUERY function and Google Apps Script and build multiple report views from a list.
Copy Row Google Sheet to Google Sheet Copy Row from Sheet to Sheet using Apps Script on Google Sheets - In this post, I show how to copy a row of data from one sheet to another using Google Apps Script. This video also shows how to search a column on Google Sheets.

Published by codewithcurt