Google Sheets Apps Script – Dynamic Dependent Dropdown Data Validation Lists

Learn how to assign data validation using Apps Scripts and create dynamic dependent dropdown lists for the whole column for Google Sheets.

function onEdit(){
  var tabLists = "lists";
  var tabValidation = "Main";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  
  var activeCell = ss.getActiveCell();
  
  if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getSheetName() == tabValidation){
    
    activeCell.offset(0, 1).clearContent().clearDataValidations();
    
    var makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues();
    
    var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1;
    
    if(makeIndex != 0){
    
        var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
  
     }  
      
  }
  
}

Understand How This Script Works

 

Google Sheets Apps Script – Combine Multiple Tabs to a Master Tab When Column Positions Don’t Match

Results of Apps Script tutorial to combine multiple worksheets to a master sheet. It will also work when column positions don’t match.

function combineData() {
  
  var masterSheet = "Master";
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(masterSheet);
  var lc = ss.getLastColumn();
  var lr = ss.getLastRow() > 1 ? ss.getLastRow() : 2;
  
  ss.getRange(2, 1, lr-1, lc).clearContent();
  
  var labels = ss.getRange(1, 1, 1, lc).getValues()[0];
  
  
  labels.forEach(function(label,i){
    var colValues = getCombinedColumnValues(label,masterSheet);
    ss.getRange(2, i+1, colValues.length, 1).setValues(colValues);
    
  })
  
}


function getCombinedColumnValues(label,masterSheetName) {
  
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  var colValues = [];
  
  for([i,sheet] in sheets){
    var sheetName = sheet.getSheetName();
    if(sheetName !== masterSheetName) {
      var tempValues = getColumnValues(label,sheetName);
      colValues = colValues.concat(tempValues);
    }
  }
  
  return colValues;
}


function getColumnValues(label,sheetName) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var colIndex = getColumnIndex(label,sheetName);
  var numRows = ss.getLastRow() - 1;
  var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
  return colValues;
}


function getColumnIndex(label,sheetName) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  var lc = ss.getLastColumn();
  var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0];
  
  var index = lookupRangeValues.indexOf(label) + 1;
  
  return index;
  
}

Understand How This Script Works

Google Sheets Get Distance & Time – GOOGLEMAPS Function

Add the following code to your Apps Script Editor first and then use the function.

/**
* Get Distance between 2 different addresses.
* @param start_address Address as string Ex. "300 N LaSalles St, Chicago, IL"
* @param end_address Address as string Ex. "900 N LaSalles St, Chicago, IL"
* @param return_type Return type as string Ex. "miles" or "kilometers" or "minutes" or "hours"
* @customfunction
*/

function GOOGLEMAPS(start_address,end_address,return_type) {

  // https://www.chicagocomputerclasses.com/
  // Nov 2017
  // improvements needed
  
  var mapObj = Maps.newDirectionFinder();
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  var directions = mapObj.getDirections();
  
  var getTheLeg = directions["routes"][0]["legs"][0];
  
  var meters = getTheLeg["distance"]["value"];
  
  switch(return_type){
    case "miles":
      return meters * 0.000621371;
      break;
    case "minutes":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to minutes and return
        return duration / 60;
      break;
    case "hours":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to hours and return
        return duration / 60 / 60;
      break;      
    case "kilometers":
      return meters / 1000;
      break;
    default:
      return "Error: Wrong Unit Type";
   }
  
}

How to Use GOOGLEMAPS Function?

Understand How This Script Works

Google Sheets Import JSON – IMPORTJSON Function

Add the following code to your Apps Script Editor first and then use the function.

/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
  
  try{
    // /rates/EUR
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
    
    var patharray = xpath.split("/");
    //Logger.log(patharray);
    
    for(var i=0;i<patharray.length;i++){
      json = json[patharray[i]];
    }
    
    //Logger.log(typeof(json));
    
    if(typeof(json) === "undefined"){
      return "Node Not Available";
    } else if(typeof(json) === "object"){
      var tempArr = [];
      
      for(var obj in json){
        tempArr.push([obj,json[obj]]);
      }
      return tempArr;
    } else if(typeof(json) !== "object") {
      return json;
    }
  }
  catch(err){
      return "Error getting data";  
  }
  
}

How to Use IMPORTJSON Function?

Understand How This Script Works

Excel VBA – Set Column Width Exactly the Same in Other Workbooks – How to Code Examples

This Excel macro is useful if you have one or more Excel files where you want to set the column sizes exactly the same as the other workbook.

This script will take the current active (selected) workbook (Excel File) as a reference, get all the column widths from the active sheet (selected tab) and apply exactly the same widths to the active sheets (selected tabs) on all the other open workbooks (Excel files).

This VBA code will only affect active sheets.

Don’t forget to like and share if you found the code useful!

 

Sub MatchColumWidth()
    'Set column widths of all the other open workbooks to the same size as the current one
    'This script will only affect active sheets
    'http://www.chicagocomputerclasses.com/
    'Chi Brander, Inc.
    '11/29/2016
    
    abookm = ActiveWorkbook.Name
    
    For Each wb In Application.Workbooks
        If wb.Name <> abookm Then
            For Each c In Workbooks(abookm).ActiveSheet.Columns
                wb.ActiveSheet.Columns(c.Column).ColumnWidth = c.ColumnWidth
            Next c
        End If
    Next wb

    MsgBox "Done."

End Sub

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

Excel VBA – Combine Many Tabs to One Master Worksheet – How to Code Examples

Suppose you have an Excel file with multiple tabs in the following format and you need to combine them to a single master sheet. This can either be a very long and boring copy/paste work or you can speed up the process by using the VBA code further down on this page.

A B C
1 Date Account Amount
2 1/1/2016 484194 $18,006.00
3 1/5/2016 497074 $37,865.00
4 1/9/2016 321592 $19,426.00
5 1/13/2016 784416 $37,348.00
6 1/17/2016 904154 $3,394.00
7 1/21/2016 936320 $25,869.00
8 1/25/2016 596282 $46,453.00
9 1/29/2016 257233 $8,262.00
10 2/2/2016 640688 $20,651.00
11 2/6/2016 169251 $2,652.00
12 2/10/2016 204842 $19,168.00
13 2/14/2016 403114 $38,061.00
14 2/18/2016 932651 $30,586.00
Tab1

 

Don’t forget to like and share if you found the code useful!

 

Sub Combine_All_Tabs_to_Master_Sheet()

'http://www.chicagocomputerclasses.com/
'11/28/2016
'Chi Brander, Inc.

    st = Application.InputBox("Please, enter a valid master worksheet name")

    If Len(st) < 32 Then
    
        mname = st & ""
    
        Worksheets.Add.Name = mname
    
        For Each ws In Worksheets
        
            If ws.Name <> mname Then
                ws.Select
                
                If Range("A1").CurrentRegion.Rows.Count > 1 Then
                
                    Range("A1").CurrentRegion.Offset(1, 0).Select
                    Selection.Resize(Selection.Rows.Count - 1).Copy
                    Worksheets(mname).Select
                    Cells(Worksheets(mname).Range("a1").CurrentRegion.Rows.Count + 1, 1).Select
                    ActiveSheet.Paste
                    
                End If
                
            End If
        
        Next ws
        
    End If

End Sub

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

VBA – Run a PowerPoint Macro using Excel VBA – How to Code Examples

Below is a demonstration of using PowerPoint.Application to open a PowerPoint file and then run a macro that is stored within the PowerPoint file.

  1. Make sure you modify “fullpath\p1.pptm” and point it to the full path-filename to your PowerPoint file.
  2. Modify the macro name. Macro name must be full macro name with file, module and macro name in your PowerPoint file in order for this script to work. Use the following format “p1.pptm!Module1.name”
Sub RunPowerPointMacro()

Dim objPP As Object
Dim objPPFile As Object

Set objPP = CreateObject("PowerPoint.Application")
objPP.Visible = True

Set objPPFile = objPP.Presentations.Open("fullpath\p1.pptm")

Application.EnableEvents = False

objPP.Run "p1.pptm!Module1.name"

Application.EnableEvents = True

objPPFile.Close

Set objPPFile = Nothing
Set objPP = Nothing

End Sub

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

VBA – Display a File Open Dialog and Open the File – Excel functions

Below is a demonstration of using Application.FileDialog to give the user an option to select a file and then open it. The code is well commented and should be self explanatory.

The full file path will be stored in fullpath variable, which is used later in the code to open the file after making sure there was an Excel file selected.

Sub FileOpenDialogBox()

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
  With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
        
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With
    
    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

    'Open the file selected by the user
    Workbooks.Open fullpath

End Sub

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/