Created
June 18, 2015 13:19
-
-
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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