Google Sheets: 3d References

This is one of my favorite scripts I've ever built to "hack" Google Sheets. It allows you to reference the same cell on multiple sheets in a single call, rather than calling each one individually. IMPORTANT NOTES: • As written, this script only accepts a single-cell range. It works on Sheet1:Sheet4!A1 but NOT on Sheet1:Sheet4!A1:A5. The latter is possible, but would require a modified script to perform an operation on the entire .getRange().getValues() array, rather than simply summing the values from .getRange().getValue() • The code will not save with a number starting the function name, so I used _3dSum rather than 3dSum as the function name. Connect with me: • [email protected] • spencerfarris.me • www.linkedin.com/in/spencer-farris/ • Twitter @FarrisSpencer • Google Product Expert support.google.com/docs/profile/12305 Sheet (to copy) https://docs.google.com/spreadsheets/d/1tBDE81TgkUmH_osJ8y0lx9H25FiB3gtoBR-Znax7uO0/edit?usp=sharing Script: function _3dSum(input) { var sh = SpreadsheetApp.getActiveSpreadsheet(); var inRange = input; var sheets = inRange.split(":"); var range = sheets[1].split("!"); sheets[1]=range[0]; range = range[1]; var out = 0; var sSheets = sh.getSheets(); for (var i in sSheets) sSheets[i] = sSheets[i].getName(); for (var i = sSheets.indexOf(sheets[0]);iless_than_signsSheets.indexOf(sheets[1])+1;i++) out = out + parseInt(sh.getSheets()[i].getRange(range).getValue()); return out; } function _3dAvg(input) { var sh = SpreadsheetApp.getActiveSpreadsheet(); var inRange = input; var sheets = inRange.split(":"); var range = sheets[1].split("!"); sheets[1]=range[0]; range = range[1]; var out = 0; var sSheets = sh.getSheets(); for (var i in sSheets) sSheets[i] = sSheets[i].getName(); var n = sSheets.indexOf(sheets[1]) - sSheets.indexOf(sheets[0])+1; for (var i = sSheets.indexOf(sheets[0]);iless_than_signsSheets.indexOf(sheets[1])+1;i++) out = out + sh.getSheets()[i].getRange(range).getValue(); out = out/n; return out; } function _3dMax(input) { var sh = SpreadsheetApp.getActiveSpreadsheet(); var inRange = input; var sheets = inRange.split(":"); var range = sheets[1].split("!"); sheets[1]=range[0]; range = range[1]; var out = 0; var sSheets = sh.getSheets(); for (var i in sSheets) sSheets[i] = sSheets[i].getName(); for (var i = sSheets.indexOf(sheets[0]);iless_than_signsSheets.indexOf(sheets[1])+1;i++){ if(sh.getSheets()[i].getRange(range).getValue() greater_than_sign out) out = sh.getSheets()[i].getRange(range).getValue(); } return out; } function _3dMin(input) { var sh = SpreadsheetApp.getActiveSpreadsheet(); var inRange = input; var sheets = inRange.split(":"); var range = sheets[1].split("!"); sheets[1]=range[0]; range = range[1]; var out = sh.getSheetByName(sheets[0]).getRange(range).getValue(); var sSheets = sh.getSheets(); for (var i in sSheets) sSheets[i] = sSheets[i].getName(); for (var i = sSheets.indexOf(sheets[0]);iless_than_signsSheets.indexOf(sheets[1])+1;i++){ if(sh.getSheets()[i].getRange(range).getValue() less_than_sign out) out = sh.getSheets()[i].getRange(range).getValue(); } return out; } function _3dCount(input) { var sh = SpreadsheetApp.getActiveSpreadsheet(); var inRange = input; var sheets = inRange.split(":"); var range = sheets[1].split("!"); sheets[1]=range[0]; range = range[1]; var out = 0; var sSheets = sh.getSheets(); for (var i in sSheets) sSheets[i] = sSheets[i].getName(); for (var i = sSheets.indexOf(sheets[0]);iless_than_signsSheets.indexOf(sheets[1])+1;i++){ if(sh.getSheets()[i].getRange(range).getValue() != "") out++; } return out; }