Google Sheets – How to Repeat Range Under Each Other in Google Spreadsheets

google sheetsgoogle-apps-script

I have a range of two columns with year and dates. In another sheet I want to repeat this range for X amount of times under each other. How can I do this?

So for example I have this range:

Year | Month
2012 | 12
2013 | 01
2013 | 02
2013 | 03

And I want to be able to say: put that range under each other for X amount of times. For example 3 times:

Year | Month
2012 | 12
2013 | 01
2013 | 02
2013 | 03
2012 | 12
2013 | 01
2013 | 02
2013 | 03
2012 | 12
2013 | 01
2013 | 02
2013 | 03

How could I do this?

Best Answer

This little script will repeat any range (with or without the header) to the given amount:

function output(range,amount,header) {
  var output = [];

  // check if range is cell
  if(typeof range == 'string') {
    for(var i=0; i<amount; i++) {
      output.push([range]);
    }
    return output;
  } else {
    // check if header is wanted
    var value;
    if(header == 1) {
      output.push(range[0]);
      value=header;
    } else if(header == "") {
      value=0;
    } else {
      value=0;
    }  
    for(var i=0; i<amount; i++) {
      for(var j=value, jLen=range.length; j<jLen; j++) {
        output.push(range[j]);
      }
    }    
    return output;
  }  
}

In order to get the result from your question, use the following formula:
enter image description here

Under Tools>Script editor you can paste the code. Press the save button and you're on the go !!

I've prepared an example file for you to play around with: Repeat Range

Related Topic