Entries by admin

Google Sheets Apps Script – Dynamic Dependent Dropdown Data Validation Lists

February 15, 2018 in , /by

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

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

February 13, 2018 in , /by

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

Google Sheets Get Distance & Time – GOOGLEMAPS Function

November 23, 2017 in , /by

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

Google Sheets Import JSON – IMPORTJSON Function

September 4, 2017 in , /by

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

Average Salaries – City of Chicago Fire Police Department Earnings

March 21, 2017 in /by

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 /by

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