0

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)

1

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

  • Interesting. Will that work with additional columns in the second table though? I will have many formulas in following columns that would need to propagate down following the usual rules (adjusting the formula values by 1 row, etc). – Slav May 25 '17 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.