Copy Paste Cell to Empty Cell in a Specific Range - google app script sheet

user_16763805 0
Viewed 74

Im very new to google app script and have been playing around was trying to get a piece of code to work. basically i was trying to copy a cell and paste it into the next blank cell in a specific range of cells.

I got it to work! But im wondering if there is a more optimized/better way to write this as a learning moment

function pastenext() {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getSheetByName('Questions')
  var r=s.getRange("C8:C17");
  var up = s.getRange("E5")
  
  if (r.getCell(1, 1).isBlank()) {
      r.getCell(1, 1).setValue(up.getValue())
    }
  else if (r.getCell(2, 1).isBlank()) {
      r.getCell(2, 1).setValue(up.getValue())
    }
  else if (r.getCell(3, 1).isBlank()) {
      r.getCell(3, 1).setValue(up.getValue())
    }   
  else if (r.getCell(4, 1).isBlank()) {
      r.getCell(4, 1).setValue(up.getValue())
    } 
  else if (r.getCell(5, 1).isBlank()) {
      r.getCell(5, 1).setValue(up.getValue())
    } 
  else if (r.getCell(6, 1).isBlank()) {
      r.getCell(6, 1).setValue(up.getValue())
    } 
  else if (r.getCell(7, 1).isBlank()) {
      r.getCell(7, 1).setValue(up.getValue())
    }   
  else if (r.getCell(8, 1).isBlank()) {
      r.getCell(8, 1).setValue(up.getValue())
    }   
  else if (r.getCell(9, 1).isBlank()) {
      r.getCell(9, 1).setValue(up.getValue())
    } 
  else if (r.getCell(10, 1).isBlank()) {
      r.getCell(10, 1).setValue(up.getValue())
    }   
}
google-apps-script javascript
1 Answers

Just use loop to make it much shorter, from this:

  if (r.getCell(1, 1).isBlank()) {
      r.getCell(1, 1).setValue(up.getValue())
    }
  else if (r.getCell(2, 1).isBlank()) {
      r.getCell(2, 1).setValue(up.getValue())
    }
  else if (r.getCell(3, 1).isBlank()) {
      r.getCell(3, 1).setValue(up.getValue())
    }   
  else if (r.getCell(4, 1).isBlank()) {
      r.getCell(4, 1).setValue(up.getValue())
    } 
  else if (r.getCell(5, 1).isBlank()) {
      r.getCell(5, 1).setValue(up.getValue())
    } 
  else if (r.getCell(6, 1).isBlank()) {
      r.getCell(6, 1).setValue(up.getValue())
    } 
  else if (r.getCell(7, 1).isBlank()) {
      r.getCell(7, 1).setValue(up.getValue())
    }   
  else if (r.getCell(8, 1).isBlank()) {
      r.getCell(8, 1).setValue(up.getValue())
    }   
  else if (r.getCell(9, 1).isBlank()) {
      r.getCell(9, 1).setValue(up.getValue())
    } 
  else if (r.getCell(10, 1).isBlank()) {
      r.getCell(10, 1).setValue(up.getValue())
    }

To this:

for(var i = 1; i <= 10; i++)
  if (r.getCell(i, 1).isBlank()) { // If the specific cell found is blank
    r.getCell(i, 1).setValue(up.getValue());
    break; // exit the loop after set value
  }

The use of break is just the same as the else if in previous lengthy one, so it will just find the satisfying condition for the current data then ignore the other conditions or the other conditional else if blocks are skipped/ignored after it found the satisfying condition.

user_146318850
Related