1

Why when I run this function, "1" is appended to the variable, instead of added to it. I guess my syntax is wrong , but couldent figure out why? :\

  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("TEST");

  //get last cell with data on cloumn A
  var columnA = dataSheet.getRange("A" + dataSheet.getMaxRows());
  var Alast = columnA.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1);
  
  //get last cell with data on cloumn C
  var columnC = dataSheet.getRange("C" + dataSheet.getMaxRows());
  var Clast = columnC.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1);
  
  
  for (var counter = Clast + 1; counter <= Alast; counter = counter +1) {
  Logger.log(counter);}
  }

Here is the logger output:

1
11
111
Execution cancelled

Thanks

buntuser
  • 99
  • 7
  • Does this answer your question? [Adding two numbers concatenates them instead of calculating the sum](https://stackoverflow.com/questions/14496531/adding-two-numbers-concatenates-them-instead-of-calculating-the-sum). Your `Clast` variable is a string and `+` concatenates for strings. Use [`parseInt()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/parseInt) or [`parseFloat()`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/parseFloat) to parse your String values to numbers and the calculation will work. – Mushroomator Apr 04 '22 at 07:49
  • Indeed, that was the problem, thank you! – buntuser Apr 04 '22 at 08:17

1 Answers1

2

I thought that columnA.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1) and columnC.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1) return the string value. I thought that this is the reason of your issue. In your script, how about the following modification?

From:

//get last cell with data on cloumn A
var columnA = dataSheet.getRange("A" + dataSheet.getMaxRows());
var Alast = columnA.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1);

//get last cell with data on cloumn C
var columnC = dataSheet.getRange("C" + dataSheet.getMaxRows());
var Clast = columnC.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1);

To:

//get last cell with data on cloumn A
var columnA = dataSheet.getRange("A" + dataSheet.getMaxRows());
var Alast = columnA.getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); // Modified

//get last cell with data on cloumn C
var columnC = dataSheet.getRange("C" + dataSheet.getMaxRows());
var Clast = columnC.getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); // Modified
  • By this modification, Alast and Clast are the values of integer.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165