Fill a range (multiple columns) down multiple rows

Question

I had success filling a single column (A) with the value found in range A1...

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getDataRange().getNumRows();  
  var rngVal = ss.getRange("A1").getValue()
  ss.getRange("A2:A"+lastRow).setValue(rngVal)

So then I thought I was on easy-street, and I tried to modify/apply that to a larger range by filling a multi-column range with the values found in range C1:H1...

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getDataRange().getNumRows();  
  var rngVal = ss.getRange("C1:H1").getValues()
  ss.getRange("C2:H"+lastRow).setValues(rngVal)

Apparently there is a bit more to this than simply slapping an "S" onto the end of the word "Value".

The error reads as follows:

Incorrect range height, was 1 but should be 10

(FYI: var lastRow = 11)

Btw, I get no error if I use Value instead of Values, although I end up with cells full of the value found only in range C1.

So I'm close.... or way off. One of those.

Help???

Answer 1

The error message is quite explicit... the size of the array must fit into the range in both getValues and setValues. Try it like this :

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getLastRow()  
  var rngVal = ss.getRange("C1:H"+lastRow).getValues();// get an array of 10 "rows" and 6 "columns"
  ss.getRange("C2:H"+(lastRow+1)).setValues(rngVal);//write back this array to a range that has the same size. (starting from Row2 it must ends on lastRow+1 to keep the same number of "rows"
}

This function will shift the range C1:H last Row to C2:H lastRow+1, not sure it is very useful but that's not the point here ;-)

---

EDIT : sorry, I didn't understand exactly your requirement... here is a code that reproduce data from C1:H1 in all rows below

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getLastRow()  
  var rngVal = ss.getRange("C1:H1").getValues();// get an array of the first row
  var rowData = rngVal[0]
  var newData = []
  for(n=1;n<lastRow;++n){newData.push(rowData)}
  ss.getRange("C2:H"+lastRow).setValues(newData);//write back this array to a range that has the same size. (starting from Row2 it must ends on lastRow+1 to keep the same number of "rows"
}

EDIT2 following your comment below :

A word of explanation :

When using range.getValues() we get a 2 dimensions array, meaning an array of arrays that can be represented as follow : [[data1,data2,data3],[data4,data5,data6]] data1, 2 & 3 are the value of the first array (index 0) and data 4,5 & 6 are the values of the second array (index 1). So if you want to get the values in the first array you have to write it like this : value = arrayName[0] and this will return a one dimension array [data1,data2,data3], that's what I used to get rowData.

Now we need to get a 2 dimension array again to be able to write back the new data to a range in the spreadsheet. Therefor we create a new array (var newData=[] or var newData = new Array() does exactly the same), and in the for loop we add the rowData array to this new array... the result will be an array of arrays, that is actually what we were looking for and we can write this directly to the sheet in one single setValues statement.

Answer 2

Ok, this seems to do it...

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getLastRow()  
  var rngVal = ss.getRange("C1:H1").getValues()
  for (var x=1; x<=lastRow; x++) {
  ss.getRange("C"+x+":H"+x).setValues(rngVal);