[JS] Google Form Email Response
Purpose
My team submits weekly status reports using a custom Google Form that links to a spreadsheet for my team's drive. I have them complete this form once a week so that I can log their weekly accomplishments, and compile them into a weekly status report that I am required to send out every week to my professors. By utilizing the form, my teammates are able to keep a running log of all the accomplishments they have made in the weeks they've submitted, which can then in turn be used for their required milestone reports that I generate.
Submission.gs
/** * \brief Upon submitting the form, will email the * user and reply with the answers submitted * for them to check. * * Uses the trigger "OnFormSubmit" which is * a Google-Form-only trigger. */ function onFormSubmit(e) { var doc = SpreadsheetApp.openById(MASTER_SHEET); // The Spreadsheet Document var sheet = doc.getActiveSheet(); // The Form Response Sheet var column = 0; // Column to start with printing var headers = sheet // The top row, used to grab question titles .getRange(1,1,1,sheet.getLastColumn()) .getValues()[0]; var nameRange = sheet // The cell that contains the name of the user .getRange(sheet.getLastRow(), NameColumn); // Converts all names in the event and cells to proper case & spelling. // This is useful because individuals will sometimes submit without capitalization, // with initials, or with other permutations. properNames(e); nameRange.setValue(e.namedValues["Name"]); // This is the body of the email we will eventually send var hbody = "Thank you " + e.namedValues["Name"].toString() + " for submitting the weekly status report. " + "Listed below are your responses:<br><br>"; // For each question that was asked of the user... for(var i in headers) { // Columns start at 1; 0 is technically the row / column numbers. ++column; // The value in the form event var value = e.values[i]; // If the user did not submit a response to this question, continue. if(!value) continue; // StringDuration will take a date value and convert it into "X hours, Y minutes" if(column == DurationColumn) value = StringDuration(value); // Add the question title and their answer to the HTML block. // newLineString will ensure that the string response submitted // will form cohesive bulleted lines. // E.g., if the user submitted the following: // // Accomplishments: // -- Cleaned the Dishes // -- Walked the Dog // -- Made Dinner // // ...it will ensure that it appears that way in HTML. Sometimes // the transition from the form to the cell removes the formatting. hbody += "<b>" + headers[i] + "</b><br>" + newLineString(value.toString(), true) + "<br><br>"; } // Salutation and link to their individual form (see below) hbody += "<br>To view earlier responses, please go to " + "<a href=\"https://docs.google.com/spreadsheet/ccc?key=" + docID[e.namedValues["Name"].toString()] + "\">your individual submission sheet</a>.<br>"; hbody += "<br><br>Thank you,<br><br> " + SenderName + "<br>" + SenderPhone + "<br> " + SenderAddress; // Grab the name of the individual from the event and set the title. var Recipient = e.namedValues["Name"]; var EmailSubject = "Status Report Submission Verification"; // Dispatch the mail event to the user MailApp.sendEmail(EmailAddresses[Recipient], EmailSubject, hbody, {htmlBody:hbody, replyTo:SenderAddress, name:SenderName}); // Copy the event details to their individual sheet which they have access to copyToIndividualSheet(e); } /** * \brief Copies the submission details of a user's response to * a form into their own individual spreadsheet, which * allows them to view it later. */ function copyToIndividualSheet(e) { var doc = SpreadsheetApp // The spreadsheet document .openById( docID[e.namedValues["Name"].toString()] ); var sheet = doc.getActiveSheet(); // The active spreadsheet var headers = sheet // The first row with question titles .getRange(1,1,1,sheet.getLastColumn()) .getValues()[0]; // Row 1 is the headers row. If there is only one row, then no responses exist. // Otherwise we want to move the row down so the latest submission is always // at the top. if(sheet.getLastRow() != 1) { // Find the previous row range var previousRows = sheet.getRange(2,1,sheet.getLastRow() - 1, sheet.getLastColumn()); // Fade the row so that the most recent submissions tands out. previousRows.setBackgroundRGB(238, 238, 238); // Gray previousRows.setBorder(false, // Top false, // Left false, // Bottom false, // Right false, // Vertical false); // Horizontal previousRows.setFontWeight("normal"); // Insert a new row at the top so that it can be populated with the new submission sheet.insertRowBefore(2); } var column = 0; var row = 2; // In each cell, set the new submission data from the event for(var i in headers) { ++column; sheet.getRange(row, column).setValue(e.values[i]); } // Grab the newly created row var currentRow = sheet.getRange(2,1,1,sheet.getLastColumn()); // Format the most recent row currentRow.setBackgroundRGB(255, 255, 255); // White currentRow.setBorder(true, // Top true, // Left true, // Bottom true, // Right false, // Vertical true); // Horizontal currentRow.setFontWeight("bold"); }