Using a for loop with an if else statement

Question

I'm kinda new to this and I was wondering if I could ask for some help. I'm kinda stuck with this function I'm trying to create in googlescript. Let me give you a rundown on how I want it to work. Given 2 worksheets, it looks at the dataset in sheet 1, and if the (i,12) has a value of Open then it will copy the entire row and paste it to the next empty row on sheet 4 (so it offsets down 1 row). If the value is Closed then it would proceed to evaluate the next i. This goes on until last row with values in sheet 1.

function UpdateList(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var dSheet = ss.setActiveSheet(ss.getSheets()[0]);
    var gSheet = ss.setActiveSheet(ss.getSheets()[3]);
    var lRow = dSheet.getLastRow();
    var lCol = dSheet.getLastColumn();
    var i
    var j = 1

    for (i = 0; i < lRow; i++) { 
        if (dSheet.getRange(i, 12).getValue() == "Open") { 
          dSheet.getRange(i,1,0,lCol).copyValuesToRange(gSheet.setRange(j,1))
        } else {

        }
    }
}

You are only interested in performing some logic if the value is "Open", in an if else statement the else block is optional. You can just omit it in this case. — dspano
– dspano, Commented Apr 21, 2015 at 1:23

Alan Wells · Accepted Answer · 2015-04-21 13:54:22Z

1

You are using getValue() get a single cell value on every loop. That slows your code down. Get the entire column of data, then loop through the array. To do that you need to use getValues() which returns a two dimensional array. If you are only getting data from one column, then every inner array will only have one element.

Getting at the inner arrays of the outer array is different for a single row or a single column. If you get a 2D array of a single row, then there will only be one inner array.

[[cell one, cell two, cell three, etc]]

In the above 2d array, there is only one inner array. So you can easily get the inner array simply by using index zero.

var innerArray = outerArray[0];

If you are getting a single column of data from the spreadsheet, you'll have lots of inner arrays.

[[one],[two],[three],[etc]]

In that case, you can't simply get the zero index of the outer array. In this case you could have two For loops, one nested inside the other one. The outer loop will get each inner array, and the inner loop will get the contents of the inner array.

edited Apr 21, 2015 at 13:54

answered Apr 21, 2015 at 1:22

Alan Wells

31.4k16 gold badges113 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sandrosil Over a year ago

how would you loop through the array? I found out that getValues()[integer][integer] works to find the specific column. Is this what you meant?

Collectives™ on Stack Overflow

Using a for loop with an if else statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related