CODEWITHSUNDEEP
javascriptexcelgoogle-apps-script
Michał
Michał

Reputation: 61

Drag down function automatically from particular cell in Google Script

how could I automatically using Google Script to drag down formulas that are in cells Cx to BAx. The whole range with X will be substitute with that code. I just want to drag it down to the end of sheet.

function autofill(){

  var ValuesFromC = ss.getRange("C1:C").getValues();
  var LastRowFromC = ValuesFromC.filter(String).length;
  LastRowFromC += 1

  Logger.log(LastRowFromC);

  var rangeToFill = "C" + LastRowFromC + ":BA" + LastRowFromC
  ss.getRange(rangeToFill)

@ziganotschka edit

I edit a little bit and got that kind of code:

function autofill(){

  var ValuesFromC = ss.getRange("C1:C").getValues();
  var LastRowFromC = ValuesFromC.filter(String).length;
  var LastRow = ss.getLastRow();
  LastRowFromC += 1;
  var rangeToFill = "C" + LastRowFromC + ":BA" + LastRowFromC;
  var range = ss.getRange(rangeToFill);
  range.copyValuesToRange(ss, 3, 53, LastRowFromC, LastRow)

but it copy as values everyting from the range but I want to copy formula that will be adequate to the cell

Upvotes: 0

Views: 1070

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

You cannot "drag" as such with Apps Script, but you can fill a range of arbitrarily dimensions with the your range of choice.

To do so, use the method copyValuesToRange(sheet, column, columnEnd, row, rowEnd).

Sample:

function autofill(){
  var ss=SpreadsheetApp.getActive().getActiveSheet();
  var LastRowFromC =ss.getLastRow() ;
  var rangeC=ss.getRange("C1:C"+LastRowFromC)
  var rangeToFill="D1:B"+LastRowFromC;
  rangeC.copyValuesToRange(ss, 3, 53, 1, LastRowFromC);
}

If you want to copy formulas only

Sample code:

function autofill(){
  var ss=SpreadsheetApp.getActive().getActiveSheet();
  var LastRowFromC =ss.getLastRow() ;
  var rangeC=ss.getRange("C1:C"+LastRowFromC);
  for(var i=1;i<53;i++){  //53 correcponds to column BA
   var rangeToFill=rangeC.offset(0,i);
rangeC.copyTo(rangeToFill, SpreadsheetApp.CopyPasteType.PASTE_FORMULA); 
  }
}

Upvotes: 2

Related Questions