For loop with an if else statement Google-apps-script

Question

I have sheet. In column A I have simbol ☑ in some cells. I need that active cell moves to cell after last cell with ☑ simbol. Now code doesn't work as I want. Please help me to change code. Thanks.

Here is my code

 function LastCellInCollA() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var cell = sheet.getRange(2, 1, 1, 1);
  var i
  sheet.setCurrentCell(cell);  
  for (i = 2; i < 146; i++)
    var valuei = sheet.getRange(i, 1, 1, 1).getValues();
  {
    if (valuei = "☑️") 
    {  
      sheet.setCurrentCell(sheet.getRange(i, 1, 1, 1));
      i = i + 1;
        }
  }
}

Are you saying you need this script to move from the current active cell to the next cell with ☑️ ? — ross
– ross, Commented Dec 18, 2019 at 10:10
@ross No. I need that active become cell without symbol ☑I add screenshot joxi.ru/brRjK5GUYJ7G1r Please see. Sheet renew som times and quontity of symbol ☑ increase. So I want that script moves active cell to next clear cell after cell with ☑ — Sergei I.
– Sergei I., Commented Dec 18, 2019 at 10:26
I still find difficult to understand what you are trying to achieve. Could you actually describe what steps the code should do? Maybe a sheet with a wrong and good example. Also I don't think that setCurrentCell() should be used here. You are only changing the selected cell in UI — Raserhin
– Raserhin, Commented Dec 18, 2019 at 10:49
@Raserhin Exactly I need change active cell in UI. Here more screenshot joxi.ru/J2byBVYsGXGxPm — Sergei I.
– Sergei I., Commented Dec 18, 2019 at 10:55

ADW · Accepted Answer · 2019-12-18 10:55:47Z

1

Is that checkmark an ASCII symbol or are you using the Google Sheet checkbox?

If you are using the Google Sheet checkbox, then the following script may work for you.

The recommended way is to not hit the sheet in each loop. So the script does the math and goes directly to the relevant cell instead of going row-by-row.

function LastCellInCollA() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var data = sheet.getRange('A2:A').getValues();
  var d = 0;
  while (data[d][0] == true) { d++; } // If you are using ASCII symbol, replace 'true' with the symbol
  var lastRow = d + 2;
  var cell = sheet.getRange(lastRow, 1);
  sheet.setCurrentCell(cell);
}

answered Dec 18, 2019 at 10:55

ADW

4,2471 gold badge17 silver badges23 bronze badges

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

1 Comment

Sergei I. Over a year ago

Thanks. I use exactly simbol ☑

Collectives™ on Stack Overflow

For loop with an if else statement Google-apps-script

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