For Loop ONLY completes LAST job

[paco_santigo]

So I have a spreadsheet here: GoogleDocs

The code I have is :

function addition() {
  // Declare Variables
  var inputCell, outputCell, tempValueHolder;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  // Declare the range of the input numbers: Column C
  var inputRange = sheet.getRange("C4:C41");
  // Declare the Range of where you want the output numbers: Column D
  var outputRange = inputRange.offset(0, 1);
  // Calculate the column and row count. columns = 1, rows = 37
  var colNum = inputRange.getNumColumns()-1;
  var rowNum = inputRange.getNumRows()-1;
  // Run the for loop
  // There is only one column of data, C
  for (var i=0; i<=colNum; i++);
  {
    // There are 37 rows of data, 4 through 41
    for (var j=0; j<=rowNum; j++);
    {
      // Declare column and row info for cells
      inputCell = inputRange.getCell(j,i); //from Column C
      outputCell = outputRange.getCell(j,i); // to Column D
      // Copy whatever is in Column D
      tempValueHolder = outputCell.getValue();
      // Set Column D's cell to be whatever is in Column C PLUS what WAS in Column D
      // It's just adding Column C's value to Column D's
      outputCell.setValue(inputCell.getValue()+tempValueHolder);
      // Clear Column C
      inputCell.clearContent();  
      // next row (supposedly)
    }
  }
}

What I'm trying to do is take Values entered in the C column and add them to the existing number in column D of the same row. I do this from the starting cell: C4 down the rows to C41. Again, taking the value in C4 and adding it to D4... or C4+D4=newD4, all the way down to row 41.

So here's the problem. Whenever I run the function it only completes the last line in the loop, as in the last row (41). If I change the range from C4:C41 to C4:C#, it always only completes the loop with the # row. I can't figure out why. I have spent 6 hours JUST on this problem. Please someone with more knowledge help me. Thank you.

[Old Pedant]

This doesn't make sense to me:

  // There is only one column of data, C
  for (var i=0; i<=colNum; i++);
  {

If there is only one column, then why do you want/need a LOOP on the columns?

I have never even looked at Google spreadsheets, but *ASSUMING* that the getRange function works the way you are using it, I'd think you could just do:

function addition() {
  // Declare Variables
  var inputCell, outputCell, tempValueHolder;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  // Declare the range of the input numbers: Column C
  var inputRange = sheet.getRange("C4:C41");
  // Declare the Range of where you want the output numbers: Column D
  var outputRange = inputRange.offset(0, 1);

  var rowNum = inputRange.getNumRows()-1;
  for (var j=0; j<=rowNum; j++);
  {
      // Declare column and row info for cells
      inputCell = inputRange.getCell(j,0); //from Column C
      outputCell = outputRange.getCell(j,0); // to Column D
      // Copy whatever is in Column D
      tempValueHolder = outputCell.getValue();
      // Set Column D's cell to be whatever is in Column C PLUS what WAS in Column D
      // It's just adding Column C's value to Column D's
      outputCell.setValue(inputCell.getValue()+tempValueHolder);
      // Clear Column C
      inputCell.clearContent();  
      // next row (supposedly)
  }
}

BUT...

But having said that, I admit I don't see at all why your code doesn't work.

[paco_santigo]

Originally Posted by Old Pedant

This doesn't make sense to me:

  // There is only one column of data, C
  for (var i=0; i<=colNum; i++);
  {

If there is only one column, then why do you want/need a LOOP on the columns?

I have never even looked at Google spreadsheets, but *ASSUMING* that the getRange function works the way you are using it, I'd think you could just do:

  var rowNum = inputRange.getNumRows()-1;
  for (var j=0; j<=rowNum; j++);
  {
      // Declare column and row info for cells
      inputCell = inputRange.getCell(j,0); //from Column C
      outputCell = outputRange.getCell(j,0); // to Column D
      // Copy whatever is in Column D
      tempValueHolder = outputCell.getValue();
      // Set Column D's cell to be whatever is in Column C PLUS what WAS in Column D
      // It's just adding Column C's value to Column D's
      outputCell.setValue(inputCell.getValue()+tempValueHolder);
      // Clear Column C
      inputCell.clearContent();  
      // next row (supposedly)
  }
}

BUT...

But having said that, I admit I don't see at all why your code doesn't work.

Yeah, I tried this but it gives me an error message about being out of cell range. So Instead of 0, i used 1. No error this time, but still only the last row completed its task.

[Old Pedant]

Hmmm... If the first column of a range is 1, then maybe the first *ROW* of a range is *also* 1???

So maybe your loop *should* go from 1 to 41, not from 0 to 40??

Where the heck are the Google docs for getRange( )??? I couldn't find them (even with Google search!).

[paco_santigo]

It's zero-based, so the first column is 0... the count is 1.
Range info can be found here. http://code.google.com/googleapps/ap....html#getRange

[Old Pedant]

Okay, I found the docs.

I'm not at all sure, but you *might* be addressing the cells wrong.

Pure guess, but maybe try this:

function addition() {
  // Declare Variables
  var inputCell, outputCell, tempValueHolder;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  // Declare the range of the input numbers: Column C
  var inputRange = sheet.getRange("C4:C41");
  // Declare the Range of where you want the output numbers: Column D
  var outputRange = inputRange.offset(0, 1);
  // There is only one column of data for each column:
  var colC = inputRange.getColumnIndex();
  var colD = outputRange.getColumnIndex();
  // and these are the row limits:
  var rowFirst = inputRange.getRowIndex();
  var rowLast = inputRange.getLastRow();

  for (var row = rowFirst; row <= rowLast; ++row )
  {
      // Declare column and row info for cells
      inputCell = inputRange.getCell(row,colC); //from Column C
      outputCell = outputRange.getCell(row,colD); // to Column D
      // Copy whatever is in Column D
      var tempValueHolder = parseFloat(outputCell.getValue());
      // Set Column D's cell to be whatever is in Column C PLUS what WAS in Column D
      // It's just adding Column C's value to Column D's
      outputCell.setValue(parseFloat(inputCell.getValue())+tempValueHolder);
      // Clear Column C
      inputCell.clearContent();  
    }
  }
}

[Old Pedant]

Yeah, it's really confusing... When they grab a range and treat it as 2D array, clearly it is zero-based.

But the sheet numbering is clearly 1-based.

Ugh. I really don't know.

I'd probably try it with a smaller range and put in some alerts to see what values I'm getting.

[paco_santigo]

Finally found an answer:

function update() {
  var inputCell, outputCell, tempValueHolder;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var inputCell = sheet.setActiveCell("C4");
  for (var i=0; i<=38; i++)
  {
    outputCell = inputCell.offset(0, 1);
    tempValueHolder = inputCell.offset(0, 1).getValue();
    outputCell.setValue(inputCell.getValue()+tempValueHolder);
    inputCell.clearContent();
    inputCell = inputCell.offset(1, 0);
  }
}

Basically I start in C4 and do relatively the same thing. I don't know why but I seem to suspect that the Ranges were making things difficult.

Now, I still have a problem. haha. The load is greater doing it this way and doesn't look nice while updating. I tried playing around but can't figure out how to copy the entire range (C4:C41) and ADD it to the range (D4: D41) instead of doing it one by one. I can copy the range in oneshot, but couldn't figure out how to add it to another range, row for row.

[Old Pedant]

Can't begin to guess. I'm suprised this is slow, though, for that few cells.

Did you try it with Chrome browser? Still slow there?