How to start autofill at last cell in one column to last row of adjacent column in Apps Script/Google Sheets?

user_8785198 0
Viewed 185

So I have a table that will have data to the last row except column B. (for example, cols A, C, D etc. stop at row 40, but B stops at maybe row 25). I want to programmatically keep the series going starting at the last cell with data in B and autofill down to the last row in the spreadsheet. (Hopefully a script doing this will recognize the series and not just copy the same data to all the empty cells. When I do it manually it works.) I have something started here but I can't figure out how to call out the range of where to start the series. I get an error on line 7 "Exception: Range not found".

pic of Google Sheet

 function fillDownFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var sc = ss.getRange("B1:B").getValues();
  var scr = sc.filter(String).length;
  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(scr,2,lr)
  ss.getRange(scr).copyTo(fillDownRange);
  
}
google-apps-script google-sheets
2 Answers

Here is one way you could have the last fill value copy down. The function grabs all of the values, maps the row that needs to fill down and then copies that last value down the rest of the sheet.

function fillDownFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tab = ss.getActiveSheet();
  var sc = tab.getDataRange().getValues();
  var lastRow = sc.length;
  var values = sc.map(function(r) { return r[1]; }).filter(i => i);
  var lastFillRow = values.length;
  var fillDownValue = values.slice(-1);
  tab.getRange(lastFillRow + 1, 2, lastRow - lastFillRow).setValue([fillDownValue]);
}
user_105598680

In addition to the answers already provided, Apps Script already has an autoFill() method that you can use to do this. With this you just have to define a source and a destination range. This works the same as selecting the range and dragging down your mouse manually on the corner.

function fillDownFunction() {
  var ss= SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var lastsheetrow = ss.getLastRow() //last row with data
  
  //last row in the autofill range with data
  var lastrangerow = ss.getRange("B1:B").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()
  
  var sourcerange = ss.getRange(2, 2,lastrangerow-1) //in your screenshot, this would be B2:B6
  var destinationrange = ss.getRange(2, 2, lastsheetrow-1) //This would be B2:B12
  
  sourcerange.autoFill(destinationrange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES)

}

Note that I skipped the header row and offset the range lengths by -1 to compensate. This is because the autofill logic uses the entire range, so it would also take into account "Item2" and repeat it every 6 rows as "Item3", "Item4" and so on. A disadvantage in this case, but may prove useful if you plan to use autofill in more complex ways in the future.

user_123066870
Related