Google Sheets While Loop Until Empty Cell Found

Question

I'm trying to run the following script in Google Sheets but I can't get it to stop. Anyone able to advise where I'm going wrong. Thanks

function TriplicateEachLine() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var cc = ss.getCurrentCell();
  //Set New Hedders
  ss.getRange('R1').setValue('Stock');
  ss.getRange('S1').setValue('Weeks');
  ss.getRange('A2').activate();
  //Start of Loop Function
  while (cc > ""){
    ss.getCurrentCell().offset(0, 17).setFormulaR1C1('=R[0]C[-5]-R[0]C[-4]');
    ss.getCurrentCell().offset(0, 18).setValue('12 Weeks');
    sheet.getRange(ss.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();
    ss.getActiveSheet().insertRowsAfter(ss.getActiveRange().getLastRow(), 1);
    ss.getActiveRange().offset(ss.getActiveRange().getNumRows(), 0, 1, ss.getActiveRange().getNumColumns()).activate();
    sheet.getRange(ss.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    ss.getCurrentCell().offset(0, 17).setFormulaR1C1('=R[0]C[-5]-R[0]C[-4]');
    ss.getCurrentCell().offset(0, 18).setValue('8 Weeks');
    sheet.getRange(ss.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();
    ss.getActiveSheet().insertRowsAfter(ss.getActiveRange().getLastRow(), 1);
    ss.getActiveRange().offset(ss.getActiveRange().getNumRows(), 0, 1, ss.getActiveRange().getNumColumns()).activate();
    sheet.getRange(ss.getCurrentCell().getRow() - 2, 1, 1, sheet.getMaxColumns()).copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
    ss.getCurrentCell().offset(0, 17).setFormulaR1C1('=R[0]C[-5]-R[0]C[-4]');
    ss.getCurrentCell().offset(0, 18).setValue('4 Weeks');
    ss.getCurrentCell().offset(1, 0).activate();
  }
  Browser.msgBox("Complete!");
};

You never change the value of cc, so the while condition will never change. — Diego
Hi Diego, thank you for your swift response. I'm not sure I understand though. Apologies if I'm being stupid here but this is my first crack at a apps script. I believed that I had set cc to SpreadsheetApp.getActive().getCurrentCell();. The current cell is moved throughout the function, isn't it? I expected the line ss.getCurrentCell().offset(1, 0).activate(); to continuously move down my spreadsheet until it reached a blank cell and then stopped. Any pointers as to what I can do to change this? Thanks — Steve Ames
The value in cc doesn't get updated. Make these two changes and try again: (1) var cc = ss.getCurrentCell().getValue(); and (2) cc = ss.getCurrentCell().offset(1, 0).activate().getValue();. — Diego
Thanks Diego, it's running better than it was as it is now correctly selecting a cell on .getValue() on each loop but it still isn't stopping when it reaches an empty cell. — Steve Ames

Diego Diego · Accepted Answer · 2018-07-25T16:28:47

There are several issues that could be affecting you:

There is no active spreadsheet.
The active sheet is not the sheet you think it is.
The current cell is not the one you think it is.
You're trying to check the value of the current cell, but you're not getting its value.
Your condition for checking if the cell is empty is wrong.
You never change the value in cc.
Browser.msgBox() is waiting for user input.

You can check the first three issues with Logger.log() statements.

// Check values
Logger.log("Active Spreadsheet Name: " + ss.getName());
Logger.log("Active Sheet Name: " + sheet.getName());
Logger.log("Cell Address: " + cell.getA1Notation());

The fourth issue is easily resolved by simply using getValue(). In this case, I created a new variable to hold it.

var cellValue = cell.getValue();

The fifth issue, checking if a cell is empty should be testing if its an empty string. You can do this a few ways. One is to check for [in]equality (cellValue != "") and another is to check length (cellValue.length > 0).

To make sure that you're changing the value of your variable, you can again use Logger.log() statements. In my example, you can see that I'm updating the cell and the cell value variables in these two lines:

cell = cell.offset(1, 0);
cellValue = cell.getValue();

Lastly, Browser.msgBox() can be tricky. It will pause your script until the user interacts with the UI that it presents. If you run the script from the script editor, you may not see the message box, and so the script will continue to run indefinitely. You can try getting rid of that line and just using Logger.log() while testing, or you can create a custom menu to trigger the script from the spreadsheet UI.

Below is the full example code. You should be able to run it and make the necessary modifications to your original based off of this.

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu("Test Menu")
  .addItem("Find first empty cell", "findFirstBlank")
  .addToUi();
}

function findFirstBlank() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var cell = ss.getCurrentCell();
  var cellValue = cell.getValue();

  // Check values
  Logger.log("Active Spreadsheet Name: " + ss.getName());
  Logger.log("Active Sheet Name: " + sheet.getName());
  Logger.log("Cell Address: " + cell.getA1Notation());

  while (cellValue != "") {
    cell = cell.offset(1, 0);
    cellValue = cell.getValue();
  }
  Logger.log("First Empty Cell: " + cell.getA1Notation());
  Logger.log("Complete!");
  Browser.msgBox("Complete!");
};

NOTE: This does not handle cases where there are no empty cells. You should definitely consider how to best handle that given your specific requirements.

Google Sheets While Loop Until Empty Cell Found

1 Answers