Ultimate Guide to Loops in Google Sheets

Google Sheets are handy for doing a variety of tasks. There are too many formulas to accomplish different tasks quickly. However, sometimes you need some iterative control statements to do repetitive manual steps. The good news is that you can use the loops in Google Sheets for repetitive and hectic manual tasks. 🙂

Here, we will discuss loops (for Google Sheets) in detail with examples and explanations. So, let’s start with a quick introduction to Google Sheets scripts.

Introduction to the Google Sheets Scripts for Loops

Google Sheets scripts add power to your Google Sheets. Using Apps Script, we can do a variety of tasks quickly using loops. Let’s look at how to write and run scripts in Apps Script:

Open a Google Sheets document
Click “Extensions” from the main menu
Click “Apps Script” to open a window with the code editor
Write a script, save and run it

Google Sheets use JavaScript for scripting. Therefore, it would be helpful if you are comfortable writing JS scripts. Let’s explore Google Sheet scripts through some examples.

Appending check box to a selection range in Google Sheets Scripts

Consider a Google Sheet where column A has items and column B has prices.

The user has to select the items based on the price. For this, we have to place a checkbox before each item.

There are several steps required to add the checkbox manually; however, if the user has to perform such a task frequently, a short script can solve the purpose:

function insert_checkboxes() {
	var mySheet = SpreadsheetApp.getActive();
	mySheet.getRange('C2:C11').activate();
	mySheet.getActiveRangeList().insertCheckboxes();
};

This script will add checkboxes in the GooglSheets from cell C2 to C11. The function name is the user’s choice; however, there are certain conventions to follow.

Let’s quickly discuss the statements inside this function:

The getActive() function of SpreadsheetApp returns the current active spreadsheet (if there is any, otherwise returns NULL.
The getRange() function selects some range of cells to perform some operation.
Finally, the insertCheckboxes() function inserts the checkboxes in the area specified by the previous statement.
The functions return the currently active range (The user may have manually selected some cells in the sheet or by using the activate function). In case there is no chosen range, the function returns NULL.

Here is the result of executing the script:

This example might not appeal to you because you can manually select a range and insert checkboxes from the insert sub-menu. However, it could save you several clicks while doing regular/ frequent tasks in an extensive job. Let’s have a look at a more sophisticated example.

Solving quadratic equations using Google Sheets scripts

Consider a Google Sheets having values of a, b, & c in columns A2, B2, & C2. You may be familiar with quadratic equations; however, we have given a general quadratic equation with the formula to find roots.

Here, we have a script to find the roots quickly. You have to put the parameters a, b, & c in the cells, and the code will calculate both roots x1 * x2.

function find_quadratic_roots() {
	var mySheet = SpreadsheetApp.getActive();
	var c_range = mySheet.getRange('A2:E2');
	var rows = c_range.getNumRows();
	var a = c_range.getCell(rows, 1).getValue();
	var b = c_range.getCell(rows, 2).getValue();
	var c = c_range.getCell(rows, 3).getValue();
	var disc = b * b - 4 * a * c; 
	disc = Math.sqrt(disc);
	var x1 = ( -b + disc ) / (2 * a) ;
	var x2 = ( -b - disc ) / (2 * a) ;
	c_range.getCell(rows, 4).setValue(x1);
	c_range.getCell(rows, 5).setValue(x2);
};

The resultant sheet looks like this:

In the above code, after getting rows, the next three lines of the script will pick values of variables a, b & c from cells A2, B2 & C2, respectively. In the next four lines, the script calculates two roots in variables x1 & x2. The last two lines place the value of variables x1 & x2 in cells D1 & E1, respectively.

Loops in Google Sheets

The Google Sheets scripts have loops to repeat a pattern of actions. Google Sheets supports mainly three types of iterative or looping structures:

The for loops
The while loops
The do-while loops

Let’s discuss these iterative structures one by one.

The for Loop

Loops help repeat tasks a fixed number of times. Like doing calculations 20 times or applying something in 15 cells. The syntax of for loop is:

for ( initialization; condition; increment/decrement)

Most computer literates are already familiar with these loops because they are common in many programming languages; however, here is an explanation for users with a non-technical background.

There are three sections in the parenthesis of for loop. The first part is for variable initialization. The second part is for condition, and the last is for increment/ decrement. All three sections are optional.

As already discussed, a for loop executes a fixed number of times. Therefore, we use some counter-variables to control the loop iterations.

In the first section, we initialize the controlling variable. In the second section, we give a condition to check if we should stop the loop from further iteration. The third section increments or decrements the controlling variable for counting purposes.

The whole execution procedure is divided into the following steps: –

Initialization (one time only): The variable c is initialized to a value of 1. This statement executes only once.
Condition Evaluation: The loop condition is evaluated. If the condition evaluates to true, the loop’s body executes for one time. Otherwise, the loop is terminated.
Increment/ Decrement: Once the body of the loop executes successfully, the increment or decrement section takes its turn.
Repetition: Repeat 2 and 3 until the condition becomes False.

See a small example for a better understanding:

for ( var c = 1 ; c <= 10 ; c++ )
	//do something

The above code iterates over its body ten times. Here c is a loop control variable.

This loop will iterate ten times. It starts with the value 1 of a variable. Lastly, in the second section, we check whether the value of c is less or equal to 10. The loop will continue unless the value of the controlling variable becomes 11, where the loop will terminate

Now, let’s discuss some for-loop examples in the Google Sheets script.

Example 01: Insert checkboxes using a for loop

Recall our earlier example about inserting the checkboxes. Let’s do the same using the for loop.

function insert_checkboxes() {
	var spreadsheet = SpreadsheetApp.getActive();
	var my_range;
	for ( var i = 1; i <= 10; i++ ) {
		my_range = ('B' + i + ':' + 'B' + i);
		spreadsheet.getRange(my_range).activate();
		spreadsheet.getActiveRangeList().insertCheckboxes();
	}
};

We have selected this example because we have explained most of the statements at the start of this article. Here, we run a loop ten times and select one cell in each iteration. The last line of the loop’s body inserts a checkbox in the chosen box.

Let’s move to a better example.

Example 02: Check empty cells using a for loop

Consider a sheet having values in the first column and checkboxes in the second column. Now user wants to check the checkboxes where values exist. Otherwise, the user will not select the checkboxes in case of missing values.

The user can do this task manually. Imagine what if the user has to do it for one million values? First, the task is time taking. Secondly, there is a margin of error.

To do this task, we have written a for loop to check the values and select the checkbox only if the cell is not blank:

function check_checkboxes() {
	var mySheet = SpreadsheetApp.getActive();
	mySheet.getRange('A1:B11').activate();
	for ( var i = 1; i <= 11; i++ ) {
		my_range = ('A' + i + ':' + 'A' + i);
		var c_range = mySheet.getRange(my_range);
		var rows = c_range.getNumRows();
		var cell = c_range.getCell(rows, 1);
		if (cell.isBlank()==false){
			my_range = 'B' + i + ':' + 'B' + i;
			mySheet.getRange(my_range).activate();
			mySheet.getActiveRangeList().check();
		}
	}
};

The output after executing this code is:

The screenshot clearly shows that the script has checked the boxes where the cell is not blank.

The function getNumRows returns the starting row number of the selected range. The function getCell returns the cell according to parameters, rows, and columns.

Finally, there is an if condition, checking whether or not the cell is blank. If the cell is empty, the function will return true.

Inside the body of the if condition, the first statement selects the cell in the second column, whereas the last line checks the checkbox.

Example 03: Customized copy/ pasting using a for loop

The users of Google Sheets often face typical problems when they copy/ paste the formula in a specific range. The copied formula pastes to all cells, irrespective of the requirement.

There may exist some empty cells where the formula is not required. The corresponding cells don’t contain the values needed for the procedure. Therefore, either error appears in the said cells or 0 appears in the cells.

Please note the value ‘0.00’ in column E because the user copied the formula from cell E1 and pasted it from E2 to E11.

The users have to delete the formula manually to keep the cells empty where the corresponding cell is blank. To avoid these manual actions (of course time taking), the following script is helpful:

function formula_copy() {
	var mySheet = SpreadsheetApp.getActive();
	mySheet.getRange('G1:H11').activate();
	for ( var i = 1; i <= 11; i++ ) {
		my_range = ('G' + i + ':' + 'G' + i);
		var c_range = mySheet.getRange(my_range);
		var rows = c_range.getNumRows();
		var cell = c_range.getCell(rows, 1);
		if (cell.isBlank()==false){
			var to_range = mySheet.getRange('H1:H1');
			var f_range = 'H' + i + ':' + 'H' + I;
			var from_range = mySheet.getRange(f_range);
			to_range.copyTo(from_range,
					 SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
		}
	}
};

This example has a similar approach to of second example. The script checks if the cell is not blank, and the statement paste value from the reference cell. Here is the output:

The while Loop

For loop is a counting loop, whereas while loop is a conditional loop. Usually, we use this loop when some condition may meet at any time (there is no fixed number of times).

The syntax of this loop is:

 while ( condition )

The while loop iterates till the condition become false. We may simulate for loop with the help of a while loop, where we can initialize a variable outside the while loop.

The condition checks the value of the same variable, whereas, inside the loop body, we place a statement to increment the same variable by 1. See the example:

var i = 1;

while (i <= 10){

   //do some task

   i++;
}

This while loop will execute ten times, just like for loop; however, usually, we don’t use a while loop for a fixed number of types. Therefore, we will see some solid examples of a while loop next.

Example 01: Simple searching using the while loop

Consider the sheet having values in column 1. The user has to search for a particular value and do some action in the corresponding cell.

The user wants to terminate when the condition is satisfied. Here, we have the code:

function myFunction() {
	var mySheet = SpreadsheetApp.getActive();
	mySheet.getRange('A1:C25').activate();
	var i = 1;
	while ( i <= 25) {
		my_range = ('B' + i + ':' + 'B' + i);
		var c_range = mySheet.getRange(my_range);
		var rows = c_range.getNumRows();
		var cell = c_range.getCell(rows, 1);
		if (cell.getValue()==100){
			my_range = ('C' + i + ':' + 'C' + i);
			c_range = mySheet.getRange(my_range);
			rows = c_range.getNumRows();
			c_range.getCell(rows, 1).setValue('REACHED');
			break; // terminate the loop
		}
		i++;
	}
}

Here, the script will terminate at cell B10, where value 100 exists. The difference will be more visible if we have hundreds or thousands of items.

We are taking support from the if condition to terminate the loop in this code. Otherwise, while loop works just like for loop, so, first of all, people use these two loops as an alternative; however, next, we will discuss some solid examples.

Output:

Example 2: Identify perfect squares using the while loop

Here is another exciting example of a while loop. The user wants to select a perfect square from existing numbers. A number is a perfect square if its square root is a complete integer

. Like 36 is a perfect square. The square root of 36 is 6.

To find a perfect square, the user has to find each number’s square root and verify that the answer is an integer. Again, it will be time-consuming, and there are chances of error. We have a script to do the same task:

function myFunction() {
  var mySheet = SpreadsheetApp.getActive();
  mySheet.getRange('G1:H25').activate();
  var i = 1;
  var is_square = false
  while ( is_square == false) {
    my_range = ('G' + i + ':' + 'G' + i);
    var c_range = mySheet.getRange(my_range);
    var rows = c_range.getNumRows();
    var cell = c_range.getCell(rows, 1);
    var value = cell.getValue();
    var sq = Math.ceil(Math.sqrt(value));
    if (sq * sq == value){
      my_range = ('H' + i + ':' + 'H' + i);
      c_range = mySheet.getRange(my_range);
      rows = c_range.getNumRows();
      c_range.getCell(rows, 1).setValue('Perfect Square');
      break; // terminate the loop
    }
    i++;
  }
}

Output:

The result is clearly showing that 64 is a perfect square. The script checks each number starting from G1 and moving to the next row in each iteration. The loop terminates when there comes a perfect square in the cell.

We can extend this script to find five or ten perfect squares from a large pool of numbers. We can also write similar scripts to find prime or composite numbers.

The do-while Loops

The do-while loop is the opposite of the while loop because the while loop is a restricted entry loop, whereas the do-while is a restricted exit loop. In many cases, we have to start something and repeat the same/ similar action condition is required. Therefore, in the do-while loop condition comes at the end to repeat the previous steps:

do{

   //perform some task

}
 while ( condition ) ;

The tasks inside the loop’s body will execute at least once. There are various examples where we required a do-while loop.

For example, when a user starts any video game, the game begins, and after the player ends the first game. The system asks whether to repeat it again or not. In the case of yes, the game starts again.

Similarly, if we have to take some valid input, the user enters some information, the system checks, and in case of invalid data, the system asks to enter input again. Let’s see some examples:

Similarly, if we have to take some valid input, the user enters some information, the system checks, and in case of invalid data, the system asks to enter input again:

do{
	//take input 
}while (input is invalid);

Let’s understand this with some examples:

Example 01: Random number generation

Consider the user requirement to generate a random number that satisfies some conditions. For example, the random number should be even.

Users can generate random numbers in Google Sheets; however, the user may have to create random numbers again and again to get the required random number. Here, we are doing the same task using the script:

function generate_random() {
	var mySheet = SpreadsheetApp.getActive();
	my_range = mySheet.getRange('E1:E1').activate();
	rows = my_range.getNumRows();
	cell = my_range.getCell(rows, 1);
	do{
		var value = Math.ceil (Math.random() * 50 + 50, 1);
	}while (value % 2 != 0);
	cell.setValue(value);
}

We have a statement to generate a random number inside the do-while loop body. In the while condition, if the random number is odd, the loop will repeat.

The loop will terminate when the state becomes false, which means the value is even. Therefore, we will always get an even number through this script.

Outputs:

Example 2: Paper setting using a do-while loop

Consider an exciting problem. The examiner has five objective-type questions in the exam; each has three options, where only one option is correct, and two are incorrect. The right option has 1 point, and the wrong choice has a penalty of 1/2 point

The examiner wants to place the correct options such that there will be minimum chances of getting the right options by placing choices randomly instead of solving them.

For example, if a student selects similar options for all questions, say choice two. A maximum of two questions will be correct, and three will be wrong.

For correct questions, the student will get two marks, and for incorrect questions, the student will get a penalty of 1.5 marks. This penalty will cancel out the positive marks of the students. In total, the student will get 0.5 marks only.

Putting it short, he has placed many random options for all questions. To check and select the correct choice, he has written a script:

function generate_random() {
  var mySheet = SpreadsheetApp.getActive();
  var row_no = 2, a, b, c, d, e, is_selected;
  var count_1, count_2, count_3;
  do{
    is_selected = true;
    count_1 = count_2 = count_3 = 0;
    my_range = mySheet.getRange('A'+row_no+':F'+row_no).activate();
    rows = my_range.getNumRows();
    a = my_range.getCell(rows, 1).getValue();
    b = my_range.getCell(rows, 2).getValue();
    c = my_range.getCell(rows, 3).getValue();
    d = my_range.getCell(rows, 4).getValue();
    e = my_range.getCell(rows, 5).getValue(); 
    if (a==1) is_selected = false;
    if (a==1)       count_1 = 1;
    else if (a==2)  count_2 = 1;
    else            count_3 = 1;
    if (b==1)       count_1 += 1;
    else if (b==2)  count_2 += 1;
    else            count_3 += 1;
    if (c==1)       count_1 += 1;
    else if (c==2)  count_2 += 1;
    else            count_3 += 1;
    if (d==1)       count_1 += 1;
    else if (d==2)  count_2 += 1;
    else            count_3 += 1;
    if (e==1)       count_1 += 1;
    else if (e==2)  count_2 += 1;
    else            count_3 += 1;    
    if (count_1 > 2 || count_2 > 2 || count_3 > 2 )  is_selected = false;
    if (count_1 == count_2 - 1 && count_2 == count_3 + 1) is_selected = false;
    if (count_1 == count_2 + 1 && count_2 == count_3 + 1) is_selected = false;
    if (is_selected){
      my_range.getCell(rows, 6).setValue('Selected');
      break;
    }
    row_no += 1;

  }while (true);
}

Each row in the spreadsheet has one option of choice. The script checks each option one by one and selects the first correct/ valid option.

First of all, starting statements will pick choices for each question in variables a, b, c, d & e, respectively. Next, checks ensure that options are according to the requirement. The loop will move to the next row if the option is incorrect.

If the script founds a correct option, the loop will terminate.

Output:

Great, we just have solved some real-world problems using loops in Google Sheets. 🎉

Conclusion

Using loops in Google Sheets can save us time and many repetitive manual efforts. In many cases, the problem size becomes too heavy. Not only is too much time required, but there are chances to make mistakes (as humans are prone to make mistakes). The well-tested script can save us time and get correct results within no time.

It is valuable for beginners to spend more time learning scripts. Developing a complex script may take time. Moreover, learning about best practices for testing the scripts is y important. Once developed, you can reuse these scripts for similar tasks.