Google Spreadsheet, Creating multiple rows on form submit

Question

I have a google form that writes to a spreadsheet.

Currently on form sumbmit a single new row is created.

What I would like to do is; Have a number field on the form which the user selects a value N. When the form is submitted I would like the spreadsheet to create N rows of identical data.

I have thought about it in several ways. What would be best if it could be automated based on N. Alternatively the value N could be placed in a hidden column H and a script on the spreadsheet could read this and copy the row N times, possibly counting down in column H {N, N-1,...1} if this makes it easier.

Ah, I see what you mean. I was hoping this could be done via the form. I will add the tag after I propose a possible code solution. — Niccolo, Aug 12 '14 at 10:24

score 1 · Accepted Answer · edited May 23 '17 at 11:49

When the form submits a script;

reads the new data
gets the number or required rows (N)
copies a template row to give N blank rows with the correct formatting and functions
copies the new data into place in the new blank rows.

function onSubmit() {
// Sheet to which form submits
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FORM TARGET 1");
var lastRow = sheet.getLastRow();
// READ THE NUMBER OF NEW ROWS FROM THE DESIGNATED COLUMN IN "FORM TARGET 1"
var N = sheet.getRange(lastRow, 1).getValue();

// Sheet to which we will write
var sheetRec = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Target");
var lastColumn = sheetRec.getLastColumn();
var lastRowT = sheetRec.getLastRow();

// Create N new Rows by copying in the template row N times
var Template = sheetRec.getRange(2, 1, 1, lastColumn);
for (j = 1; j <=N; j++) {
Template.copyTo(sheetRec.getRange(lastRowT + j, 1));
}

// Copy new data values into the rows
var Val1 = sheet.getRange(row, 1);
var Val2 = sheet.getRange(row, 5);
var Val3 = sheet.getRange(row, 7);
var Val4 = sheet.getRange(row, 8);
Val1.copyValuesToRange(sheetRec, 1, 1, lastRowT + 1, lastRowT + N);
Val2.copyValuesToRange(sheetRec, 2, 2, lastRowT + 1, lastRowT + N);
Val3.copyValuesToRange(sheetRec, 4, 4, lastRowT + 1, lastRowT + N);
Val4.copyValuesToRange(sheetRec, 5, 5, lastRowT + 1, lastRowT + N);
}

There is a reason I am only copying certain cells from FORM TARGET 1 and why the range cannot be sequential.

I have to use a for loop to copy the template row because copyValuesToRange() & copyFormatsToRange() do not copy functions. I have functions in some columns that act on the copied data.

I have discussed this issue more completely here.

Google Spreadsheet, Creating multiple rows on form submit

1 Answers1