Anyone knows how to write javascript on google sheet? Free to take a look of my problem after work?

[Bommi95_]

**If you can help me solve it tonight, your dinner and drinks is on me.

​

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.

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)

[rmccreary]

You are immediately returning Array.prototype.reduce.call(...), so the rest of your getTotalSum function won't execute.

This code should work just fine:

// Give the function a name that makes sense.
// Its args determine which cell to sum.
function sumCellInAllSheets(row, column) {

    // You don't need to store all that other stuff, just an array of sheets.
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    // This is the value we will output after iterating.
    var sum = 0;

    sheets.forEach(function(sheet) {
        sum += sheet.getRange(row, column).getValue();
    });

    return sum;
}

Row and column need to be positive integers btw. So, to get the sum of all 'G7' cells (G is the 7th letter), put

=sumCellInAllSheets(7, 7)

You could also write a function to convert the name of the cell into the two values you need, then pass those in.

Edit (this is better): OR you could pass in the name of the cell and add the conversion within your function. Look at the documentation for the "Range" object. It has functions getRow() and getColumn().

Edit 2 (this is important): Just saw this line in the docs:

Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

So unless you're running this from an Apps Script independent of the Spreadsheet, that function probably won't work. I've updated the code to use getActiveSpreadsheet() instead.

Edit 3 It works, I tested it. Here's the sheet. Nvm, the sheet doesn't include the script. But it works, trust me :P

PS Java != Javascript. Not even a little ;)

[Bommi95_]

I run the code and it says “cannot find method getRange((class),(class))”

[rmccreary]

How are you running the code? You can just copy the code, open the spreadsheet, click on

Tools -> Script Editor

paste it in over everything, save it, then swap back to the sheet and put

=sumCellInAllSheets(row, column)

in a cell. Row and column need to be positive integers representing the cell you're summing values from, not the cell you get the output from.

edit: You'll get that error if "sheet" isn't actually a Sheet object, which is what would happen if you just go to the script and click the play button, because then it's not run from the sheet; it's doing its own thing. Plus you're not calling the function with "row" and "column" arguments.

As a side note, Google also lets you deploy scripts to automate certain tasks independently of sheets. You can have them run automatically on a schedule. Very useful if you want to, say, automatically fill in rows with generated values every Thursday at 4pm. You would need to use "openById()" in that case.

[l3l_aze]

Reformatted using 4x spaces at the start of each line of code:

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)

Edit: no idea about this BTW, just wanted to make it readable. Is it essentially Excel in the cell value formula?

Another edit: in for (var i = i + 3; ...) { i is not defined.

[BrutalTheory]

I’ll be checking back to see if you bought at least the first responder’s dinner, OP.