r.pannkuk@gmail.com
Riley Pannkuk
  • Posts
  • Projects
    • Rumble TV >
      • Website
    • Dischord >
      • Website
    • Ping >
      • Website
    • Temple of the Water God >
      • Website
    • E.A.T: Escape Alien Terrestrials
  • Code Samples
    • [JS] Google Form Email Response
    • [JS] Google Form Trello Integration
    • [JS] Trello Card Organizer
    • [C++] Metrics Integration
    • [Python] Zero Metrics Integration
  • About Me
  • Résumé

[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");
}
Powered by Create your own unique website with customizable templates.