Last active
October 17, 2020 06:29
-
-
Save franklinokech/ed315587f1ab4ade4da51820a78ddb89 to your computer and use it in GitHub Desktop.
use this simple script to audit the google sheet sizes and cells audit. sample dashboard is in the link https://docs.google.com/spreadsheets/d/1myUQEsA9oBNqigG8VdQnsoAnKoohFrl_wG5S7znHjAk/edit?usp=sharing use the sheet size auditor UI menu to Interact with the Tool
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Google Sheets Performance Auditor tool | |
* Built by Franklin Okech, 2019 | |
* http://www.franklinokech.com | |
*/ | |
/** | |
* Custom menu | |
*/ | |
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu('Sheet Size Auditor') | |
.addItem('Audit Sheet', 'sheetAuditor') | |
.addItem('Clear Data', 'clearData') | |
.addToUi(); | |
} | |
/** | |
* Clears out data and resets sheet | |
*/ | |
function clearData() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getActiveSheet(); | |
sheet.getRange(6,2).clearContent(); | |
sheet.getRange(11,1,1,5).clear(); | |
sheet.getRange(16,1,sheet.getLastRow(),10).clear(); | |
sheet.getRange(10,1,1,5).setBorder(true, true, true, true, true, true, "#D3D3D3", null); | |
sheet.getRange(15,1,1,10).setBorder(true, true, true, true, true, true, "#D3D3D3", null).setFontSize(14); | |
} | |
/** | |
* Audits the given url and prints results into the Google Sheet table | |
*/ | |
function sheetAuditor() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getActiveSheet(); | |
var url = sheet.getRange(6,2).getValue(); | |
try { | |
var audit_data = auditUrl(url); | |
// output the overall file data into my Google Sheet | |
var fileRange = sheet.getRange(11,1,1,5); | |
var fileFormats = [ | |
[ "#,###,##0", "#,###,##0", "0.0%", "#,###,##0", "0.0%" ] | |
]; | |
var fileHorizontalAlignments = [ | |
[ "center", "center", "center", "center", "center" ] | |
]; | |
fileRange.setValues([audit_data[0]]).setNumberFormats(fileFormats) | |
.setHorizontalAlignments(fileHorizontalAlignments) | |
.setBorder(true, true, true, true, true, true, "#D3D3D3", null) | |
.setFontSize(14); | |
// output the individual sheet data into my Google Sheet | |
var sheetRange = sheet.getRange(16,1,audit_data[1].length,10); | |
var sheetFormats = [ | |
[ "#,###,##0", "#,###,##0", "0.0%", "#,###,##0", "0.0%" ] | |
]; | |
sheetRange.setValues(audit_data[1]); | |
var formatRange = sheet.getRange(16,1,audit_data[1].length,10); | |
formatRange.setNumberFormat("#,###,##0") | |
.setHorizontalAlignment("center") | |
.setBorder(true, true, true, true, true, true, "#D3D3D3", null) | |
.setFontSize(14); | |
} | |
catch(e) { | |
Browser.msgBox("Please enter a valid Google Sheets URL"); | |
} | |
} | |
/** | |
* Returns performance and size data for a given sheet url | |
* @param {string} url - url of the Google Sheet | |
* @returns {array} of file data and individual sheets data | |
*/ | |
function auditUrl(url) { | |
var ss = SpreadsheetApp.openByUrl(url); | |
var sheets = ss.getSheets(); | |
// counters | |
var numSheets = 0; | |
var totalCellCounter = 0; | |
var totalDataCellCounter = 0; | |
var fileArray = []; | |
var sheetsArray = []; | |
sheets.forEach(function(sheet) { | |
// get single sheet data | |
var thisSheetInfo = getSingleSheetInfo(sheet); | |
// update the counters | |
numSheets++; | |
totalCellCounter = totalCellCounter + thisSheetInfo[3]; | |
totalDataCellCounter = totalDataCellCounter + thisSheetInfo[4]; | |
sheetsArray.push(thisSheetInfo); | |
}); | |
var totalCellPercent = totalCellCounter / 5000000; | |
var totalDataCellPercent = totalDataCellCounter / 5000000; | |
fileArray.push(numSheets, totalCellCounter, totalCellPercent, totalDataCellCounter, totalDataCellPercent); | |
return [fileArray,sheetsArray]; | |
} | |
/** | |
* Returns individual sheet performance and size data | |
* @param {object} sheet - the individual sheet within a Google Sheet | |
* @returns {array} of configuration settings | |
*/ | |
function getSingleSheetInfo(sheet) { | |
var singleSheetArray = []; | |
var name = sheet.getName(); | |
// how many cells in the sheet currently | |
var maxRows = sheet.getMaxRows(); | |
var maxCols = sheet.getMaxColumns(); | |
var totalCells = maxRows * maxCols; | |
// how many cells have data in them | |
var r = sheet.getLastRow(); | |
var c = sheet.getLastColumn(); | |
var data_counter = r * c; | |
if (data_counter !== 0) { | |
var dataRange = sheet.getRange(1,1,r,c); | |
var dataValues = dataRange.getValues(); | |
dataValues.forEach(function(row) { | |
row.forEach(function(cell) { | |
if (cell === "") { | |
data_counter --; | |
} | |
}); | |
}); | |
} | |
// count how many volatile formulas | |
var slowFuncs = slowFunctions(sheet); | |
singleSheetArray.push( | |
name, | |
maxRows, | |
maxCols, | |
totalCells, | |
data_counter, | |
slowFuncs[0], | |
slowFuncs[1], | |
slowFuncs[2], | |
slowFuncs[3], | |
slowFuncs[4] | |
); | |
return singleSheetArray; | |
} | |
function slowFunctions(sheet) { | |
var vols = identifyVolatiles(sheet); | |
var arrs = identifyArrayFormulas(sheet); | |
vols.push(arrs); | |
return vols; | |
} | |
/** | |
* Returns counts for volatile functions in individual Google Sheet | |
* @param {object} sheet - the individual sheet within a Google Sheet | |
* @returns {array} of volatile function counts | |
*/ | |
function identifyVolatiles(sheet) { | |
// how many cells have data in them | |
var r = sheet.getLastRow(); | |
var c = sheet.getLastColumn(); | |
var data_counter = r * c; | |
var nowCounter = 0; | |
var todayCounter = 0; | |
var randCounter = 0; | |
var randbetweenCounter = 0; | |
var reNow = /.*NOW.*/; | |
var reToday = /.*TODAY.*/; | |
var reRand = /.*RAND.*/; | |
var reRandbetween = /.*RANDBETWEEN.*/; | |
if (data_counter !== 0) { | |
var dataRange = sheet.getRange(1,1,r,c); | |
var formulaCells = dataRange.getFormulas(); | |
formulaCells.forEach(function(row) { | |
row.forEach(function(cell) { | |
if (cell.toUpperCase().match(reNow)) { nowCounter ++; }; | |
if (cell.toUpperCase().match(reToday)) { todayCounter++ }; | |
if (cell.toUpperCase().match(reRand) && !cell.toUpperCase().match(reRandbetween)) { randCounter++ }; | |
if (cell.toUpperCase().match(reRandbetween)) { randbetweenCounter++ }; | |
}); | |
}); | |
} | |
return [nowCounter, todayCounter, randCounter, randbetweenCounter]; | |
} | |
/** | |
* Returns counts for ArrayFormula functions in individual Google Sheet | |
* @param {object} sheet - the individual sheet within a Google Sheet | |
* @returns {array} of ArrayFormula function counts | |
*/ | |
function identifyArrayFormulas(sheet) { | |
// how many cells have data in them | |
var r = sheet.getLastRow(); | |
var c = sheet.getLastColumn(); | |
var data_counter = r * c; | |
var arrayCounter = 0; | |
var reArray = /.*ARRAYFORMULA.*/; | |
if (data_counter !== 0) { | |
var dataRange = sheet.getRange(1,1,r,c); | |
var formulaCells = dataRange.getFormulas(); | |
formulaCells.forEach(function(row) { | |
row.forEach(function(cell) { | |
if (cell.toUpperCase().match(reArray)) { | |
arrayCounter ++; | |
}; | |
}); | |
}); | |
} | |
return arrayCounter; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment