...

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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum