Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    For Loop ONLY completes LAST job

    So I have a spreadsheet here: GoogleDocs

    The code I have is :
    Code:
    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    This doesn't make sense to me:
    Code:
      // 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:

    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    This doesn't make sense to me:
    Code:
      // 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:

    Code:
      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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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!).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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:
    Code:
    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();  
        }
      }
    }
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Finally found an answer:
    Code:
    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.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •