How to start autofill at last cell in one column to last row of adjacent column in Apps Script/Googl-CodePudding

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".

 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);
  
}

CodePudding user response：

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]);
}

CodePudding user response：

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.

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.