How to make a Google Script fill in the closest row of cells to the top with a value and then stop without repeating

Vote count:

0

I am trying to create a function (using Google Scripts) that fills in the row of blank cells closest to the top in Google Spreadsheets with a certain value. The problem is that I don't want it repeating itself when it is done or overwriting existing data. I figured I could use some while and for loops to make this work, but so far I have had little success. Here is my existing code:

function add() {
 var done = false
 while(done === false) {
  for(var i = 2; i <= SpreadsheetApp.getActive().getSheetByName("Sheet3").getMaxRows(); i++) 
   while((SpreadsheetApp.getActive().getSheetByName("Sheet3").getRange(i, 1).isBlank()) === true && (SpreadsheetApp.getActive().getSheetByName("Sheet3").getRange(i - 1, 1).isBlank()) === true) {
     SpreadsheetApp.getActive().getSheetByName("Sheet3").getRange(i, 1).setValue("'... Complicated'")
     SpreadsheetApp.getActive().getSheetByName("Sheet3").getRange(i, 2).setValue(Session.getActiveUser())
     SpreadsheetApp.getActive().getSheetByName("Sheet3").getRange(i, 3).setValue(Session.getActiveUser().getEmail())
     done = true;
  }

 }
}

I was thinking that after the text had been filled in to the closest blank cell to the top, the while loop outside the for loop should make the formula stop, because of the done = true script that I put at the bottom of the second while loop, but it didn't work. Any ideas?

asked 14 secs ago

Josh Smith

27

How to make a Google Script fill in the closest row of cells to the top with a value and then stop without repeating