The original script was written for the 'old' Forms offering, and hasn't been updated. As a result, the hard-coded reference to Sheet1 is wrong, as 'new' Forms will create a "Form Responses" sheet. The reliance on column numbers is "brittle", as the script assumes that the respondents' email addresses will be in column 2 (B), immediately after the Timestamp. In practice, the order of questions in the spreadsheet depends on the order they were added to the Form - and since a new form is pre-populated by a dummy question, and the email address column is treated as a hidden question in the Form, the email address will NEVER be in column 2, even if you delete the dummy question. So that brittle code is now broken.
And that whole //ignore this warning thing? It's just a comment, it won't stop an error from being detected.
The good news is that there is a better way.
Instead of hard-coding the particulars, we can utilize the Event object that is provided to the trigger function. Read about the Sheets Form Submit event object.
To index into specific columns, I've included a helper function, columnNum(). It's written as a spreadsheet custom function, so it yields 1-based indexes for compatibility with built-in functions.
Code.gs
This script is easier to customize than the original "voTer", as all the variables you need to fiddle with are at the top. The body of the email can be modified if necessary - each line appears as a separate string in an Array.
/**
* To customize the email sent to re-voters, change the values
* of the following variables.
*/
var subject = "Invalid Vote Received"; //Email subject
var electoralOfficer = "your electoral officer"; // Person to contact for re-vote
var emailSignature = "Election committee"; // Signature on email
var timezone = Session.getScriptTimeZone(); // Timezone - use caution
function oneVote(vote) {
var colUserName = columnNum("Username");
// use the provided event object to get references to required objects
var sheet = vote.range.getSheet();
var ballot = vote.range.getRowIndex();
var voter = vote.namedValues["Username"][0];
// Get list of all previous votes
var prevVoters = sheet.getRange(1, colUserName, ballot-1)
.getValues() // Get email addresses as 2-dim array
.join(',') // Join into a comma-deliminated string
.split(',') // Then split into one-dimensional array
// Check whether this voter previously voted
var previousVote = prevVoters.indexOf(voter);
// if they did, remind them then delete the new vote
if (previousVote > -1) {
var previousVoteTime = sheet.getRange(previousVote+1,columnNum("Timestamp")).getValue();
var body = //Email body
["Hi there,",
"",
"You previously voted on #TIMESTAMP#, so this submission will not count.",
"Please contact #ELECTORALOFFICER# if you do want to change your vote.",
"",
"Thanks,",
"#SIGNATURE#"
].join('<br>')
.replace('#TIMESTAMP#',Utilities.formatDate(previousVoteTime, timezone, "MMM d 'at' h:mm a"))
.replace('#ELECTORALOFFICER#', electoralOfficer)
.replace('#SIGNATURE#', emailSignature)
MailApp.sendEmail(voter, subject, body,{htmlBody:body});
sheet.deleteRow(ballot); // Could move to a "spoiled ballots" list instead.
}
}
/**
* Get the index of columnName.
* @customfunction
*
* @param {String} columnName Column header to find
* @param {Number} headerRow Column headers row index (Optional, default 1)
* @returns {Number} Column index, 0 if not found
*/
function columnNum(columnName,headerRow) {
headerRow = headerRow ? headerRow - 1 : 0; // Default: assume column headers in first row
var rows = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
// Validate input
if (headerRow < 0 || headerRow >= rows.length)
throw new Error( "headerRow out of range 1..maxRows" );
var headers = rows[headerRow];
return headers.indexOf(columnName)+1;
}
Install-able trigger
Remember to set up the trigger to run when forms are submitted.
Test function
For testing trigger functions, it's best to simulate the events that will be received under normal operation. This function reads the existing form responses and feeds them to oneVote() as new submissions. See How can I test a trigger function in GAS?
/**
* Test function simulates multiple form submission events for oneVote()
* trigger function, by reading "active" spreadsheet. Make sure your
* spreadsheet session is open to the Form Responses first.
*
* From https://stackoverflow.com/a/16089067
*/
function test_oneVote() {
var dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
var data = dataRange.getValues();
var headers = data[0];
// Start at last row, work backwards to allow for deletions, skip headers in row 0
for (var row=data.length-1; row > 0; row--) {
var e = {};
e.values = data[row];
e.range = dataRange.offset(row,0,1,data[0].length);
e.namedValues = {};
// Loop through headers to create values & namedValues properties
for (var col=0; col<headers.length; col++) {
e.namedValues[headers[col]] = [e.values[col]];
}
// Skip invalid rows
if (e.namedValues["Username"] === '') continue;
// Pass the simulated event to oneVote
oneVote(e);
}
}
