As you've noticed, the Forms service doesn't just append responses at the end of the current data; it also inserts a new row into the spreadsheet to be ready for the next submission. In this "Before" screenshot, notice that the "data table" managed by Forms has a grey background, while the additional credential columns don't:
Now, the "After" screenshot. Once a couple of forms have been submitted, we've got a gap in the credentials!
We can recover from the form submission activity by using an installable Spreadsheet Form Submission trigger function. (See Understanding Triggers and Understanding Events, if you've never dealt with trigger functions in Google Apps Script before.)
The algorithm we'll use is:
Given the range containing the new form submission, offset to the right and down to obtain the unused credentials, along with any introduced gaps.
Remove rows that have blanks. This is accomplished using javascript tricks including the Array.filter() method.
Clear the original credential range, and update it with the tidied credentials.
Here's the code:
/**
* Spreadsheet Form Submission installable trigger function.
* Each form submission will bump the username and password (credential)
* columns down. This function will close them up again.
*
* @param {event} e See https://developers.google.com/apps-script/understanding_events
*/
function onFormSubmit( e ) {
var credColumns = 2; // # of columns of credential info to right of form
var sheet = e.range.getSheet();
var data = sheet.getDataRange().getValues();
var toBottom = sheet.getLastRow() - e.range.getRow() + 1; // # rows from new submission to bottom of sheet
var credentialRange = e.range.offset(0, e.values.length, toBottom, credColumns);
var crA1 = credentialRange.getA1Notation();
// Filter the credentials, removing blanks
var credentialData = credentialRange.getValues()
.filter(function(cred) {
return cred.join('') != '';
});
debugger; // pause if running in debugger
// Write tidied credentials to spreadsheet
credentialRange.clearContent();
credentialRange.offset(0, 0, credentialData.length, credentialData[0].length).setValues(credentialData);
}
/**
* Form Submission test function, adapted from
* http://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas/16089067#16089067
*/
function test_onFormSubmit() {
var extraColumns = 2; // Non-Form columns, to the right
var dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
var data = dataRange.getValues();
var numFormColumns = data[0].length - extraColumns;
var headers = data[0].slice(numFormColumns);
// Start at row 1, skipping headers in row 0
for (var row=1; row < data.length; row++) {
var e = {};
e.values = data[row].slice(numFormColumns);
e.range = dataRange.offset(row,0,1,numFormColumns);
e.namedValues = {};
// Loop through headers to create namedValues object
for (var col=0; col<headers.length; col++) {
e.namedValues[headers[col]] = e.values[col];
}
// Pass the simulated event to onFormSubmit
onFormSubmit(e);
}
}
Now after a form is submitted, the sheet gets nicely cleaned up:
Alternative
If you're convinced that you need to keep those non-form columns in place, you've now got a way to do so. However, consider another way.
Instead of having your unused credentials stacked up waiting for Form Submissions, you could keep them on a separate sheet. Then, using a different form submission trigger function, you would only need to pick the next unused credentials from that sheet, and append them to the new row. Of course, you'd want to mark them as used, as well.
This approach should be more efficient than the code provided above.
Why not just delete the added row?
With a one-liner, we could use a trigger function to delete the newly added row:
function onFormSubmit( e ) {
e.range.getSheet().deleteRow(e.range.getRow()+1);
}
There is a danger with this approach. Since form submissions are asynchronous, there is a possibility that multiple users may be submitting forms at pretty much the same time. When that happens, this trigger function would be invoked multiple times, and the blind deletion of a row could erase a user's submission.
The Lock Service can't help us with this, either. While we could use Lock to ensure that only one invocation of onFormSubmit() was running, we have no control over the form itself, or the insertion of form responses.
