Google Script to Copy Row Above to Blank Row Below

robot video

I had a spreadsheet that entered blank cells when there was more than one admin for a WordPress site.

So if Site_1 had two admins, I’d get two rows of data. The first row for the site would have-

siteURL | siteTitle | siteAdmin

but the second row for that site would have something like-

< blank > | < blank > | siteAdmin

I started to just drag down and fill but there was lots of data and it just felt like something for the machine to do.

The following Google Script did it for me in a few seconds despite a couple thousand rows of data. Not rocket science but it might be handy for someone and it was a really convenient example of variables and loops when I had a conversation with my son last night.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var last = sheet.getLastRow();//how many times should I do this?

  for (i = 1; i < last; i++) { 
    var test = sheet.getRange(i, 2);
    Logger.log(test);
  //looks to see if the cell is empty
    if (test.isBlank()) {
      var rewind = sheet.getRange(i-1, 2, 1, 2).getValues();//gets values from the row above
      sheet.getRange(i, 2, 1, 2).setValues(rewind);//sets the current range to the row above
    } 
  
  }
}

Comments on this post

No comments.

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL