Why does the script get stuck on executing a while loop?

Question

I was trying something in Google Apps Script for my Google Sheets spreadsheet. It basically just copies cells in one sheet, goes to another sheet, pastes it, which updates a bunch of data, then it copies those values, and goes back to the original sheet and transposes those results.

So I had it working as a macro, but I'd like to make it function in a loop, so it fills out all of the rows on a sheet. Below is a copy of my code:

function relcoprow() {
var spreadsheet = SpreadsheetApp.getActive();
var startloc = spreadsheet.getCurrentCell();
var rangetocopy = spreadsheet.getCurrentCell().offset(0,0,1,2);
var countnewcell = 1
var nextcell = spreadsheet.getCurrentCell().offset(countnewcell,0,1,1);
  while (nextcell!="") {
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet4'), true);
  rangetocopy.copyTo(spreadsheet.getRange("A2"));
  spreadsheet.getRange('V1');

  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getRange('W1').activateAsCurrentCell();
  spreadsheet.getRange('V3:V1130').copyTo(spreadsheet.getRange('W3:W1130'), 
  SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  var rangetotranspose = spreadsheet.getRange('W3:W1130');

  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
  rangetotranspose.copyTo(startloc.offset(0,7,1,1130), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, 
  true);
  nextcell = nextcell +1 
    }
  };

When I run the code it get's stuck on this line and doesn't move forward. Because it gets stuck I can 't even see if my while loop works.

Can anyone offer advice on what I should do?

@OlegValter actually if you check in Apps Script, the result of nextcell + 1 is "Range1" and as it loops it becomes "Range11", "Range111" and so on. Absolutely right that it is always true, just thought it was interesting that it is coereced into a string. — dwmorrin
@foose212 - we've pointing out why your while loop condition will never be false, but if you want to see this result for yourself, try using the debugger in the script editor. But the loop should run... there's nothing about "getting past a VAR" as your title says... maybe you could define what your trying to accomplish with a minimal example instead of the complex copying? — dwmorrin
Thank you all for the comments. I see the problem with nextcell, I changed that line to countnewcell = countnewcell +1. I'm sorry I didn't make my problem clear in the first post. I kept getting rejected, so I must of taken out a part of the problem in one of my revisions. The code runs, but it seems to get stuck on the line right above the line var rangetotranspose. I can see the macro work, it copies my initial values, pastes it into sheet 4, then copies the new values and pastes it into the correct column. Then selects that column, and gets stuck there. Any help? — foose212
@foose212 - what do you mean by getting rejected, btw? Re:stuck - thank you for explainging that (edit the correct code into the question, so as others won't get confused). That said,, could you elaborate on what "stuck" means here? Does it perpetually run? Are there any logs? Errors? Basically, how do you know it is really stuck? Try adding a console.log method call somewhere above and below the offending line to see if the line is reached at all — Oleg Valter
Apart from clarifying what stuck and rejected means, could you by any chance provide a copy of the spreadsheet you're working on, free of sensitive information, so that your issue can be better understood? — Iamblichus

foose212 foose212 · Accepted Answer · 2020-07-14T17:48:39

So my loop wasn't stuck. It was just because I didn't have a bit of code to go back to sheet1.

However, all of the help with the while loop really moved me a long after I figured out the problem with active(). My loop now works.

I used the code to go down to the next row after each iteration and make that the active cell.

For my while loop I used !tickerloc.isblank() to let the program know that it needed to keep working.

Why does the script get stuck on executing a while loop?

1 Answers