CODEWITHSUNDEEP
javascriptgoogle-sheetsgoogle-apps-scriptcustom-function
tftd
tftd

Reputation: 17062

Google Script stops running because there are no passed arguments

I have a function which I have been using in google sheets for ages and it worked just fine. After I created a new sheet document and added the function, it stopped working. I have been trying to figure out what has google changed in their API but without any success. This is my function:

/**
 * Sums a cell across all sheets
 * 
 * @param {string} cell The cell (i.e. "B2")
 * @return number
 * @customfunction 
 */
function sumAllSheets(cell) 
{

  var sum = 0;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet().getName();
  var sheets = ss.getSheets();

  for (var i=1; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();
    var sheetRange = sheet.getRange(cell);
    sum += parseInt(sheetRange.getValue());
  }

  return sum;
}

What is basically does is it goes through all of the sheets and sums the the value from a single cell (i.e. cell B2 in all sheets). In previous versions I was able to pass the argument cell as a string by calling the function like so =sumAllSheets("B2"). This worked just fine. However with the new update the cell argument is undefined. Any ideas what's wrong or has been changed? I couldn't find anything in the google documentation.

Upvotes: 0

Views: 108

Answers (1)

JPV
JPV

Reputation: 27302

Are you excluding the first sheet from the sum? If not, "i" should be set to 0 in the first statement of your loop. Also try this variation of your code

function sumAllSheets(cell) {

var sum = 0;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
    var v = sheets[i].getRange(cell)
        .getValue();
    sum += parseInt(v ? v : 0);
}

return sum;
}

Here's an alternative version

function sumCellOnAllSheets(cell) {
return SpreadsheetApp.getActive()
    .getSheets()
    .map(function (sh) {
        return Number(sh.getRange(cell)
            .getValue())
    })
    .reduce(function (a, b) {
        return a + b;
    })
}

Upvotes: 1

Related Questions