Autofill Script Google Spreadsheet Script

Question

I need you to help me out with this. I need a script on my Google Spreadsheet.

I need the script to do exactly what CTRL+A, CTRL+D does.

So it's auto filling the whole page based on the first row.

In terms of details.

Let's say there are two sheets, Sheet1 and Sheet2.

Now Sheet2 is a database. So every cell and row has plain value/text.

In Sheet1 I have the simplest formulas in each cell of row 1.

..Column 1....Column 2....Column 3...and so on
=Sheet2!A1, =Sheet2!B1, =Sheet2!C1,

So when you select the first row, grab the dot and drag down it will populate formulas into rows bellow and they will be like

=Sheet2!A1, =Sheet2!B1, =Sheet2!C1,
=Sheet2!A2, =Sheet2!B2, =Sheet2!C2,
=Sheet2!A3, =Sheet2!B3, =Sheet2!C3,

So when you press CTRL+A it will select the whole Sheet1 then when you press CTRL+D it will populate the formulas to every row in that worksheet.

My goal is to get script to be doing this.

So far I wrote

function f5(){
var sheet = SpreadsheetApp.getActiveSheet();
var allData = sheet.getDataRange();
var range = sheet.getRange(1,1,allData.getNumRows(),7)
range.activate();
};

Which selects the desired range but I don't think this is correct way going forward. I also was thinking of keypress simulation but can't get find any documentation saying that this can be done.

swimmingwood swimmingwood · Accepted Answer · 2013-11-13T16:27:11

Something following should work:

  var oneRowCopy = sheet.getRange(1,1,1, sheet.getLastColumn());
  var targetRows = sheet.getRange(2,1,sheet.getLastRow()-1, sheet.getLastColumn());
  oneRowCopy.copyTo(targetRows);

Autofill Script Google Spreadsheet Script

1 Answers