getRange(cell) not working in google app script

Question

i'm fairly new to scripts. i am trying to set up a google workbook that collates entries from a google form. each month the entries from the form go to a new sheet in the workbook. The last sheet in the workbook totals all previous sheets.

However, as the number of worksheets cannot be fixed i cant write a sum function referencing all previous sheets.

I now want to build a simple function that sums values of the same cell from all existing worksheets.

I can control the name of new sheets so all new sheets will be names SMM1, SMM2, SMM3 etc.

Executing each time gives a "Range not found"
In the worksheet, calling this function with a cell name gives weird numbers. Eg: =totalsheets("E17") gives 31 even though all 5 sheets have 1 filled in E17 this is the script i have written

Code sample:

function totalsheets(cell) {

var sum=0,sname="";

// to find total number of sheets based on fixed 2nd last sheet

var number= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SMM1").getIndex();

for(var i=1; i<=number; i++)

{

  sname="SMM" + i;

  var s= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sname).getRange(cell).getValue();
    sum+=s;

  }

  return sum;

}

Max Makhrov Max Makhrov · Accepted Answer · 2016-05-04T12:34:50

Try this code:

  function totalsheets(cell) {

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

    var sum = 0;

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


      var sheet = sheets[i];
      var sheetName = sheet.getName();

      if (sheetName.substring(0, 3) == 'SMM') {

        var val = sheet.getRange(cell).getValue(); 

        if (typeof(val) == 'number') {
            sum += val;   
        }

      }

  }

  return sum;

  }

I've found similiar question here, it might also help you.

getRange(cell) not working in google app script

1 Answers