Count number of rows in sheet then subtract on a Google spreadsheet with Google Apps Script

Question

I'm trying to set dynamic getRange function, so I use getRange(row, column, numRows, numColumns) function and my example looks like:

var range = sheet.getRange(7,11,sheet.getLastRow(),sheet.getLastColumn());

And the result is ex. sheet.getRange(7,11,200,30);

But when I want to use this range, for example, to put some data to the sheet, function doesn't use starting point (startRow - 7, startColumn - 11). So I tried subtract it from getLastRow() and getLastColumn() and it works but gives me decimal number (example lastRow = 193.0) and the function crushed

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Arkusz1');
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn() - 2;
  var lastColumnParse = parseInt(lastColumn, 10);
  var range = sheet.getRange(1,1,lastRow, lastColumn);
  var formatting = range.getBackgrounds();
  var data = range.getValues();

  for(var y = 1; y < data.length; ++y){
    var message = '';

    for(var x = 0; x < data[y].length; x++){

      if(formatting[y][x] == '#ffff00'){
        var header = data[0][x];
        message += ' ' + header + ' - ' + data[y][x] + ' zł, ' + lastRow + ' ' + lastColumn;  
      };

      data[y][lastColumn] = message
    }
  }

  range.setValues(data);
}

And the error message is:

The message said that wide range is not correct. Inputted 7 but it should be 6.

In the console, we can see, that x = 7.0, not 6.

I cannot find the mistake. Please help me.

Rubén Rubén · Accepted Answer · 2017-06-04T15:16:36

The problem is that in JavaScript/Google Apps Script the array index is 0 (zero) based, and row/columns indexes are 1 (one) based. In other words, the length for an array with seven elements is seven but the index of the last element is six.

Count number of rows in sheet then subtract on a Google spreadsheet with Google Apps Script

1 Answers