PDA

View Full Version : For Loop ONLY completes LAST job



paco_santigo
09-03-2010, 09:21 PM
So I have a spreadsheet here: GoogleDocs (https://spreadsheets.google.com/ccc?key=0AlJPTZ4v7oYidHNLbmExdmU3RnZwNFdJUWQtT3pqQ3c&hl=en&authkey=CNq_hPcE)

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
09-03-2010, 09:41 PM
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
09-03-2010, 09:58 PM
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
09-03-2010, 10:27 PM
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
09-03-2010, 10:42 PM
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/appsscript/class_sheet.html#getRange

Old Pedant
09-03-2010, 10:48 PM
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
09-03-2010, 10:49 PM
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
09-04-2010, 12:49 AM
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
09-04-2010, 01:45 AM
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?