Last active
May 24, 2018 13:36
-
-
Save kallehauge/4cb5d97c69a608f17729 to your computer and use it in GitHub Desktop.
A Google Custom Function to sum up the values of a specific cell across multiple sheets.
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
/** | |
* Variables. | |
* | |
* You should not have to alter anything in the function, | |
* only these variables. | |
*/ | |
// The name of the sheet the dynamic date exists on. | |
var dateSheet = 'Sheet1'; | |
// The cell of the sheet defined above in the "dateSheet" variable, | |
// that the script should update the date in. | |
var dateCell = 'A1'; | |
// Name of the "Refresh" menu tab and link. | |
var refreshName = 'Refresh'; | |
/** | |
* Add a "Refresh" menu tab and link. | |
* | |
* We need this to have a way to trigger a callback that will update | |
* a specific cell. | |
* You can read more about it in the DocBlock for refreshLastUpdate(). | |
* | |
* @See http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet | |
*/ | |
function onOpen() { | |
// Define the menu item. | |
// The refreshName variable is defined at the top of the document. | |
var entries = [{ | |
name : refreshName, | |
functionName : "refreshLastUpdate" | |
}]; | |
// Add the menu item. | |
var sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
sheet.addMenu(refreshName, entries); | |
}; | |
/** | |
* Update a cell with the current date+time. | |
* | |
* We do this to work around the lack of references to real fields. | |
* Custom functions doesn't update when a field it fetches values from | |
* is updated, so we needed another way to recalculate custom functions. | |
* | |
* @See http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet | |
*/ | |
function refreshLastUpdate() { | |
// Update the date cell with the current time and date. | |
// The "dateSheet" and "dateCell" variables are defined outside of this function, at the top of the file. | |
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dateSheet).getRange(dateCell).setValue(new Date().toTimeString()); | |
} | |
/** | |
* Get the sum of a cell from multiple sheets. | |
* | |
* @param {string} Sheets - Reference a cell or write a comma-separated string with the sheets to sum up. | |
* @param {string} Range - The range we want to fetch values from. Ex: "A1" or "A1:A6". | |
* @param {string} Datetime - A dynamic date string. | |
* | |
* @return {number} Sum - The sum of the field across multiple sheets. | |
* @customfunction | |
*/ | |
function SUMSHEETSCELL(sheets, range, datetime) { | |
// Dummy data - We need these when we test functionality inside the editor. | |
// var sheets = 'Sheet3,Sheet2,Sheet4,Sheet5', range = 'A1'; | |
// Get the active Spreadsheet and sheets. | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var allSheets = ss.getSheets(); | |
// Parse sheets-string to an array of sheet-names. | |
var sheets = sheets.split(','); | |
// Prepare all sheet-names to a new array. | |
// We do this to get the actual name of all the sheets in the Spreadsheet | |
// and format them to lowercase so we're case-insensitive. | |
var sheetNames = []; | |
for (i = 0; i < allSheets.length; i++) { | |
sheetNames.push(allSheets[i].getName().toLowerCase()); | |
} | |
// Loop through each of the user-provided sheet-names and find a match | |
// with the active Spreadsheet sheets. | |
// We do this to fetch the sheet-object with all values etc inside. | |
// When we have found a match, we add the values of the user-provided range | |
// and sum the values of the range. - This sum will then be added to the | |
// total sum that we return at the end of the function. | |
var totalSum = 0; | |
for (i = 0; i < sheets.length; i++) { | |
// Get the index of the sheet-object. | |
var sheetIndex = sheetNames.indexOf(sheets[i].toLowerCase()); | |
// If the sheet exists (the index is bigger than 0. False will result in -1). | |
if (sheetIndex > 0) { | |
// Get the current sheet. | |
var sheet = allSheets[sheetIndex]; | |
// Get the values of the provided range. | |
var values = sheet.getRange(range).getValues(); | |
// Get the sum of the range. | |
var sum = eval(values.join('+')); | |
// Add the sum of the range to the total sum. | |
totalSum = totalSum + sum; | |
} | |
} | |
// Return the total sum of the range across the sheets. | |
return totalSum; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment