Google-sheets – Create a table with arbitrary number of rows based on params

google sheetsgoogle-apps-script

I have a little table with some source parameters, for example:

Name  | n | rate
Term1 | 3 | 2.79
Term2 | 4 | 3.00
Term3 | 2 | 2.59

What I want is, based on the above params, create a big table with the number of rows defined by the sum of "n" column. Basically the "n" column should indicate the number of rows for that particular term, and then go to the next one.

Desired output:

Name  | Seq 
Term1 | 1
Term1 | 2
Term1 | 3
Term2 | 1
Term2 | 2
Term2 | 3
Term2 | 4
Term3 | 1
Term3 | 2

The number of rows in the initial "params" table is also arbitrary, but we can assume that term naming is sequential (if that helps with Regex if required)

Best Answer

I wrote this app script that does this:

function myFunction() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var target = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var vals = s.getRange("A2:B").getValues();
  for (var i = 0; i < vals.length; i++) {
    for (var j = 0; j < vals[i][1]; j++) {
      target.appendRow([vals[i][0], j]);
    }
  }
}

First, this gets the first sheet of the spreadsheet. Then, it assumes the "terms" are the first column of this sheet and the "n" column is the second column (and both have a header row).

It then iterates over the rows of the first sheet and for each "term", it appends "n" rows to the second sheet of the spreadsheet.

So this first sheet:

enter image description here

Produced this second sheet:

enter image description here

Related Topic