Guest User

Untitled

a guest
Dec 16th, 2017
77
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.68 KB | None | 0 0
  1. function getTotalSum(cell) {
  2. var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  3. var sum = 0;
  4. for (var i = 0; i < sheets.length ; i++ ) {
  5. var sheet = sheets[i];
  6. var val = sheet.getRange(cell).getValue();
  7.  
  8. if (typeof(val) == 'number') {
  9. sum += val;
  10. }
  11. }
  12.  
  13. return sum;
  14. }
  15.  
  16. function getTotalSum(startrow, startcol) {
  17. var ss = SpreadsheetApp.getActive();
  18. var start = new Date();
  19. var numSheets = ss.getNumSheets(), sum=0;
  20. for(var k=1; k<numSheets; k++) {
  21. var data = ss.getSheets()[k].getDataRange().getValues();
  22. var value = data[startrow-1][startcol-1];
  23. if(typeof(value) == 'number') {
  24. sum += value;
  25. }
  26. }
  27. return sum;
  28. }
  29.  
  30. // global
  31. var ss = SpreadsheetApp.getActive();
  32.  
  33. function onOpen() {
  34. ss.addMenu("Sum", [{name: "Go !!", functionName: "getSum"}]);
  35. }
  36.  
  37. function getSum() {
  38. // get user input
  39. var cell = Browser.inputBox('Enter A1 notation', 'like B21',
  40. Browser.Buttons.OK_CANCEL);
  41.  
  42. // retrieve cell reference from a sheet and get row and col index
  43. var aCell = ss.getActiveSheet().getRange(cell.toString());
  44. var row = aCell.getRow()-1, col = aCell.getColumn()-1;
  45.  
  46. // get number of sheets and set sum to zero
  47. var numSheets = ss.getNumSheets(), sum=0;
  48.  
  49. // iterate through sheets, starting from second sheet
  50. for(var k=1; k<numSheets; k++) {
  51. // bulk load sheet at once
  52. var data = ss.getSheets()[k].getDataRange().getValues();
  53.  
  54. // get value from array
  55. var value = data[row][col];
  56.  
  57. // valid value
  58. if(typeof(value) == 'number') {
  59. sum += value;
  60. }
  61. }
  62.  
  63. // return value to active cell
  64. ss.getActiveCell().setValue(sum);
  65. }
Add Comment
Please, Sign In to add comment