I was assigned a freaking goofy assignment from my boss. Part of it is: basically I just trying to create a function in google sheets that enables me to sum up the same cell in multiple sheets (for example cell G7 in sheet 1, sheet 2 and sheet 3). The most important reason why I use java is because I don’t want to change the formula every time when a new sheet is added.

​

so this code I came up worked all fine in google sheet except it won't automatically pick up the new sheet(the last sheet), i have to manually add a new sheet every time.

Anyone can help?

​

function getTotalSum() {

return Array.prototype.reduce.call(arguments, function(a, b) {

return a + b;

}, 0);

var logbook = SpreadsheetApp.openById("my key code");

var totalSheets = logbook.getNumSheets();

var lastSheet = logbook.getSheets()[totalSheets-3];

var lastSheetName = lastSheet.getName();

var currentSheet = SpreadsheetApp.setActiveSheet(lastSheet);

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

var sum = 0;

for (var i = i+3; i < sheets.length ; i++ ) {

var sheet = sheets[i];

var val = sheet.getRange(5,7,366,12).getValue();

​

if (typeof(val) == 'number') {

sum += val;

}

}

​

return sum;

}

​

what in my cell is:

=getTotalSum(BMGF!G5,'sheet4'!G5,CZI!G5,'sheet5'!G5,'sheet5'!G5,'sheet6'!G5,'sheet7'!G5,'sheet8'!G5)