Skip to content

Instantly share code, notes, and snippets.

@nickvandewiele
Created June 18, 2015 13:19
Show Gist options
  • Save nickvandewiele/7a4921af2b468fdf6234 to your computer and use it in GitHub Desktop.
Save nickvandewiele/7a4921af2b468fdf6234 to your computer and use it in GitHub Desktop.
A user (with e-mail) fills in its preferences to volunteer on one/more committees and submits the form. The event triggers userSubmitsForm which puts the user's e-mail in the appropriate column with all the other volunteers for that committee.
var sheet = SpreadsheetApp.getActive().getSheetByName('Overview');
function userSubmitsForm(e) {
var user = {name: e.namedValues['Full Name'][0], email: e.namedValues['E-mail'][0],
committees : e.namedValues['Which PDA Committee are you interested in to volunteer?'][0]
};
var committees = user.committees.split(', ');//space is important!
for (var i = 0; i < committees.length; i++){
var committee = committees[i];
add_email(committee, user.email);
}
}
/**
Append argument email
in the column with first cell value equal to argument committee,
in the first cell of this column that is empty in this row.
**/
function add_email(committee, email){
//get column number for this committee
var col_number = -1;
var headers = sheet.getRange('A1:H1');
for (var i = 1; i <= headers.getNumColumns(); i++){
var header = headers.getCell(1,i).getValue();
if (header == committee){
col_number = i;
break;
}
}
var cell = sheet.getRange('a1');
//append email to end of row:
var row_values = sheet.getRange(1, col_number, sheet.getMaxRows(), 1).getValues();
var last_row = row_values.filter(String).length;
cell.offset(last_row, col_number-1).setValue(email);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment