Google Sheets Script Editor - JS range.length error

Question

I am trying to create a custom function in Google Sheets which will copy a range of values vertically a given number of times:

So that when applying repeat(A1:A4,3), it pastes vertically A1 3 times, A2 3times, A3 3 times and A4 3 times.

My attempt seems to work well, displaying the result correctly, but it gives an error which I don't understand.

function repeat(range,number){
   var result = [];
   const l = range.length;
   for (var i=0;i<len<i++)
      for(var j=0;j<number;j++)
         result.push(range[i])
      return result;
}

The error displayed by Apps Script is: "TypeError: Cannot read property 'length' of undefined", indicating line 3. Please feel free to guide me on how to use this and whether there are easier/better options, as I am a beginner in both Apps Script and JS.

Answer 1

You say that you are using this as a custom function so your range is actually a 2 dimensional array of values not a Class Range. Look up Custom Functions in the documentation.

If you insert this code into a spread:

function repeat101(values,count) {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  Logger.log(JSON.stringify(values));
}

Like this =repeat101(A1:A4,3)

Where A1:A4 looks like this:

1
2
3
4

Then look at your executions log you get this:

Executions Log: Jul 9, 2021, 2:08:58 PM Info [[1],[2],[3],[4]]

If your familiar with with using the debugger you will immediately recognize that as a 2 d column array

function REPEAT101(values, count) {
  const sh = SpreadsheetApp.getActiveSheet();
  let oA = [];
  values.forEach(r => { for (var j = 0; j < count; j++) { oA.push([r[0]]) } });
  return oA;
}