Skip to content

Instantly share code, notes, and snippets.

@laradevitt
Created February 29, 2020 18:08
Show Gist options
  • Save laradevitt/4fcb1331180a8094a6ad8f5d61ad4098 to your computer and use it in GitHub Desktop.
Save laradevitt/4fcb1331180a8094a6ad8f5d61ad4098 to your computer and use it in GitHub Desktop.
Managing RSVPs with Google Apps Script
var SHEET_MAIN = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
var SHEET_RSVP = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('RSVPs');
var SHEET_EMAIL = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invitees');
function onFormSubmit(e) {
var range = e.range;
var values = range.getValues();
var row = values[0];
var email = row[1];
var validated = validateEmail(email);
if (validated) {
updateRSVPs(range, row);
}
}
/**
* Validate email against list of emails in worksheet.
*
* @return {Number} Index representing row position in the spreadsheet, or
* false if not found.
*/
function validateEmail(email) {
return searchString(email, SHEET_EMAIL);
}
/**
* Update RSVP worksheet with new submission.
*/
function updateRSVPs(range, row) {
var columnCount = SHEET_MAIN.getLastColumn();
var newResponseRange = SHEET_MAIN.getRange(range.getRow(), 1, 1, columnCount);
var match_row = findInRow(row, SHEET_RSVP);
if (match_row !== -1) {
// Prepare to overwrite existing entry.
var newResponseDestinationRange = SHEET_RSVP.getRange(match_row, 1, 1, columnCount);
}
else {
// Prepare to create new entry.
var newResponseDestinationRange = SHEET_RSVP.getRange(SHEET_RSVP.getLastRow()+1, 1, 1, columnCount);
}
// Write to spreadsheet.
newResponseRange.copyTo(newResponseDestinationRange);
}
/**
* Search the given sheet for a match against the submission.
* Only compare two columns.
*
* @param {Array<String[]>} row An array of cell values for a row.
* @param {Spreadsheet} sheet The spreadsheet that contains the RSVP data.
* @return {Number} An index representing row position in the spreadsheet.
*/
function findInRow(row, sheet) {
// Specify the column index of the email and id fields.
var emailIndex = 1;
var eventIdIndex = 4;
// Combine the email and id columns into a string to compare against existing rows.
var compare_row = row[emailIndex] + '#' + row[eventIdIndex];
var range = sheet.getDataRange();
var rows = range.getValues();
for (var i = 0; i < rows.length; i++) {
var each_row = rows[i][emailIndex] + '#' + rows[i][eventIdIndex];
if (each_row.indexOf(compare_row) !== -1) {
return i+1;
}
}
return -1;
}
/**
* Search for a string in a given worksheet.
*
* @param {String} search_string The string to search for.
* @param {Spreadsheet} sheet The spreadsheet that contains the RSVP data.
* @return {Number} Index representing row position in the spreadsheet, or
* false if not found.
*/
function searchString(search_string, sheet){
var textFinder = sheet.createTextFinder(search_string);
var search_row = textFinder.findNext();
if (search_row) {
return search_row.getRow();
}
return false;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment