Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function getTotalSum(cell) {
- var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
- var sum = 0;
- for (var i = 0; i < sheets.length ; i++ ) {
- var sheet = sheets[i];
- var val = sheet.getRange(cell).getValue();
- if (typeof(val) == 'number') {
- sum += val;
- }
- }
- return sum;
- }
- function getTotalSum(startrow, startcol) {
- var ss = SpreadsheetApp.getActive();
- var start = new Date();
- var numSheets = ss.getNumSheets(), sum=0;
- for(var k=1; k<numSheets; k++) {
- var data = ss.getSheets()[k].getDataRange().getValues();
- var value = data[startrow-1][startcol-1];
- if(typeof(value) == 'number') {
- sum += value;
- }
- }
- return sum;
- }
- // global
- var ss = SpreadsheetApp.getActive();
- function onOpen() {
- ss.addMenu("Sum", [{name: "Go !!", functionName: "getSum"}]);
- }
- function getSum() {
- // get user input
- var cell = Browser.inputBox('Enter A1 notation', 'like B21',
- Browser.Buttons.OK_CANCEL);
- // retrieve cell reference from a sheet and get row and col index
- var aCell = ss.getActiveSheet().getRange(cell.toString());
- var row = aCell.getRow()-1, col = aCell.getColumn()-1;
- // get number of sheets and set sum to zero
- var numSheets = ss.getNumSheets(), sum=0;
- // iterate through sheets, starting from second sheet
- for(var k=1; k<numSheets; k++) {
- // bulk load sheet at once
- var data = ss.getSheets()[k].getDataRange().getValues();
- // get value from array
- var value = data[row][col];
- // valid value
- if(typeof(value) == 'number') {
- sum += value;
- }
- }
- // return value to active cell
- ss.getActiveCell().setValue(sum);
- }
Add Comment
Please, Sign In to add comment