admin - Chicago Computer Classes

Google Sheets Apps Script – Dynamic Dependent Dropdown Data Validation Lists

February 15, 2018 in Learn Google Sheets, Tutorial /by admin

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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

February 13, 2018 in Learn Google Sheets, Tutorial /by admin

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

<br />
function combineData() {</p>
<p>  var masterSheet = "Master";</p>
<p>  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(masterSheet);<br />
  var lc = ss.getLastColumn();<br />
  var lr = ss.getLastRow() > 1 ? ss.getLastRow() : 2;</p>
<p>  ss.getRange(2, 1, lr-1, lc).clearContent();</p>
<p>  var labels = ss.getRange(1, 1, 1, lc).getValues()[0];</p>
<p>  labels.forEach(function(label,i){<br />
    var colValues = getCombinedColumnValues(label,masterSheet);<br />
    ss.getRange(2, i+1, colValues.length, 1).setValues(colValues);</p>
<p>  })</p>
<p>}</p>
<p>function getCombinedColumnValues(label,masterSheetName) {</p>
<p>  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();</p>
<p>  var colValues = [];</p>
<p>  for([i,sheet] in sheets){<br />
    var sheetName = sheet.getSheetName();<br />
    if(sheetName !== masterSheetName) {<br />
      var tempValues = getColumnValues(label,sheetName);<br />
      colValues = colValues.concat(tempValues);<br />
    }<br />
  }</p>
<p>  return colValues;<br />
}</p>
<p>function getColumnValues(label,sheetName) {</p>
<p>  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);<br />
  var colIndex = getColumnIndex(label,sheetName);<br />
  var numRows = ss.getLastRow() - 1;<br />
  var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();<br />
  return colValues;<br />
}</p>
<p>function getColumnIndex(label,sheetName) {</p>
<p>  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);</p>
<p>  var lc = ss.getLastColumn();<br />
  var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0];</p>
<p>  var index = lookupRangeValues.indexOf(label) + 1;</p>
<p>  return index;</p>
<p>}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

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

November 23, 2017 in Learn Google Sheets, Tutorial /by admin

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

<br />
/**<br />
* Get Distance between 2 different addresses.<br />
* @param start_address Address as string Ex. "300 N LaSalles St, Chicago, IL"<br />
* @param end_address Address as string Ex. "900 N LaSalles St, Chicago, IL"<br />
* @param return_type Return type as string Ex. "miles" or "kilometers" or "minutes" or "hours"<br />
* @customfunction<br />
*/</p>
<p>function GOOGLEMAPS(start_address,end_address,return_type) {</p>
<p>  // https://www.chicagocomputerclasses.com/<br />
  // Nov 2017<br />
  // improvements needed</p>
<p>  var mapObj = Maps.newDirectionFinder();<br />
  mapObj.setOrigin(start_address);<br />
  mapObj.setDestination(end_address);<br />
  var directions = mapObj.getDirections();</p>
<p>  var getTheLeg = directions["routes"][0]["legs"][0];</p>
<p>  var meters = getTheLeg["distance"]["value"];</p>
<p>  switch(return_type){<br />
    case "miles":<br />
      return meters * 0.000621371;<br />
      break;<br />
    case "minutes":<br />
        // get duration in seconds<br />
        var duration = getTheLeg["duration"]["value"];<br />
        //convert to minutes and return<br />
        return duration / 60;<br />
      break;<br />
    case "hours":<br />
        // get duration in seconds<br />
        var duration = getTheLeg["duration"]["value"];<br />
        //convert to hours and return<br />
        return duration / 60 / 60;<br />
      break;<br />
    case "kilometers":<br />
      return meters / 1000;<br />
      break;<br />
    default:<br />
      return "Error: Wrong Unit Type";<br />
   }</p>
<p>}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

/**

* 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

September 4, 2017 in Learn Google Sheets, Tutorial /by admin

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

/**

* 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

Average Salaries – City of Chicago Fire Police Department Earnings

March 21, 2017 in News /by admin

This data is compiled using public dataset released by the City of Chicago Date Created 2/28/2017 Position Average Salary # Employed FIRST DEPUTY SUPERINTENDENT $197,724 1 CHIEF $185,364 5 GENERAL COUNSEL $170,112 1 DEPUTY CHIEF $168,835 17 SUPERINTENDENT’S CHIEF OF STAFF $162,012 1 COMMANDER $161,324 40 DIR OF HUMAN RESOURCES $150,396 1 DIR OF CAPS $145,476 […]

Average Salaries – City of Chicago Fire Department Earnings

March 14, 2017 in News /by admin

This data is compiled using public dataset released by the City of Chicago Date Created 2/24/2017 Position Average Salary # Employed FIRE COMMISSIONER $202,728 1 FIRST DEPUTY FIRE COMMISSIONER $197,736 1 DEPUTY FIRE COMMISSIONER $187,680 3 ASST DEPUTY FIRE COMMISSIONER $185,352 3 DISTRICT CHIEF $170,112 11 DEPUTY COMMISSIONER $161,886 2 COORD OF AIR MASK SERVICES $157,776 […]

Ultimate Google Sheets VLOOKUP Tutorial Series

March 14, 2017 in Learn, Learn Excel, Learn Google Sheets, Reference /by admin

The ultimate series covering everything you need to know about Google Sheets VLOOKUP function. Most of it applies to Microsoft Excel as well. Make sure you watch the whole series if you want in-depth understanding of VLOOKUP function and everything related. Other functions included: INDEX, MATCH, IFERROR, UNIQUE, QUERY & more.

Google Sheets SPLIT Function Tutorial – Similar to Text to Columns in Excel

March 5, 2017 in Learn Excel, Learn Google Sheets, Reference, Tutorial /by admin

How to Pull Data From Other SpreadSheets using Google Sheets IMPORTRANGE Function

March 5, 2017 in Learn Excel, Learn Google Sheets, Reference, Tutorial /by admin

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

November 29, 2016 in Excel, Reference, Tutorial /by admin

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 […]

About admin

Entries by admin

Google Sheets Apps Script – Dynamic Dependent Dropdown Data Validation Lists

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

Google Sheets Get Distance & Time – GOOGLEMAPS Function

Google Sheets Import JSON – IMPORTJSON Function

Average Salaries – City of Chicago Fire Police Department Earnings

Average Salaries – City of Chicago Fire Department Earnings

Ultimate Google Sheets VLOOKUP Tutorial Series

Google Sheets SPLIT Function Tutorial – Similar to Text to Columns in Excel

How to Pull Data From Other SpreadSheets using Google Sheets IMPORTRANGE Function

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

Locations:

Author Archive for: admin

About admin

Entries by admin

Locations: