Automating feedback forms with Google Apps Script

feedback.gs

// Copy this script into the feedback spreadsheet TEMPLATE

/************************ OnOpen *******************************/
function onOpen(e) {
  var ui = SpreadsheetApp.getUi().createMenu("Shortlink")
    .addItem("Show URL and QR code", "showQrAndLink")
    .addToUi()
}

/************************ GLOBALS *******************************/
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName('formData');

/************************ SHOW LINK *******************************/
// Return a popup window with the bit.ly shortlink and a QR code
/******************************************************************/
function showQrAndLink() {
  var html = HtmlService.createTemplateFromFile('popup').evaluate().setHeight(500).setWidth(600);
  return SpreadsheetApp.getUi().showModalDialog(html, 'Feedback Link');
}

/************************ GET SHORTLINK *******************************/
// Call the bit.ly API through your public token and return a shortlink
/**********************************************************************/
function getShortLink() {
  // Get a bit.ly API token. More at https://support.bitly.com/hc/en-us/articles/230647907-How-do-I-find-my-OAuth-access-token-
  // This only needs to be set in the feedback template.
  var accessToken = "YOUR_BITLY_TOKEN";
  var encoded = encodeURIComponent(ss.getFormUrl());
  
  var params = { method: 'GET' }
  
  var url = "https://api-ssl.bitly.com/v3/shorten?access_token=" + accessToken + "&longUrl=" + encoded;
  try {
    var resp = UrlFetchApp.fetch(url, params);
    if(resp) { 
      var resp = JSON.parse(resp);
      return resp.data.url;
    }
  } catch(e) {
    SpreadsheetApp.getUi().alert("bit.ly error", e.message, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

pdSubmission.gs

// Copyright 2017 Brian E. Bennett

// Permission is hereby granted, free of charge, to any person obtaining a copy of this software 
// and associated documentation files (the "Software"), to deal in the Software without restriction, 
// including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, 
// and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, 
// subject to the following conditions:

// The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT 
// LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
// IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
// WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE 
// OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.


// These functions will run on a form submission to create a folder populated with feedback documents using the templates.
// The entire process is documented at https://blog.ohheybrian.com/2017/11/automating-feedback-with-apps-script/

// Set the email salutation
var yourName = "YOUR_NAME_HERE" 

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu("Feedback Generator").addItem("Setup", "setup").addToUi()
}

/************************ SETUP *******************************/
// Authorize various scopes and set a form submit trigger
/**************************************************************/

function setup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('createFolder').forSpreadsheet(ss.getId()).onFormSubmit().create();
}

/************************ CREATE FOLDER ***********************************/
// Builds a Drive folder for the submitted session
// return Obj {folderId, title, owner, hours}
/********************************************************************************/

function createFolder() {
  // Template folder
  var FEEDBACK_FOLDER = DriveApp.getFolderById('YOUR_PD_FOLDER_ID');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('formData');
  
  // Get the sheet data, initialize an array to return
  var data = sheet.getDataRange().getValues();
  var returnObj = [];
  
  // Loop the sheet, start on row 1
  for(var i=1; i<data.length; i++) {
    
    var folder = data[i][6];
    
    // if the folder field is empty
    if(!folder) {
      
      // Build a readable date from the PD date in column B
      var dateObj = new Date(data[i][1]);
      var date = (dateObj.getMonth()+1) + "/" + dateObj.getDate() + "/" + dateObj.getFullYear();
  
      // open the PD folder and create a new folder inside to hold PGPs
      // Set folder name to, "Date - Workshop title" for easy sorting in Drive
      var newFolder = FEEDBACK_FOLDER.createFolder(date + " - " + data[i][3]);
    
      // Add the session information to the array
      // Batch processing will help fix mistakes if you miss a folder later.
      returnObj.push({
        folderId: newFolder.getId(),
        title: newFolder.getName() + " Feedback",
        ownerEmail: data[i][5],
        ownerName: data[i][4]
      });
      
      sheet.getRange((i+1),6).setValue(newFolder.getId())
    } 
  }  
  // Send the folder to get the template files created
  populateFolder(returnObj);
}

/************************ POPULATE FOLDER ***********************************/
// Populate a Drive Folder with documents necessary for session feedback and PGPs
// param {Object}
// requires folder ID, title, owner name, and owner email
/********************************************************************************/

function populateFolder(returnObj) {
  // These are the templates you already created
  // Put in the ID of each below
  var FORM_TEMPLATE = 'YOUR_FORM_TEMPLATE_ID';
  var SS_TEMPLATE = 'YOUR_SPREADSHEET_TEMPLATE_ID';

    // loop through the object sent 
    for(var i=0; i<returnObj.length; i++) {
    
      // Get the folder with Drive API to control sharing emails
      // The `folder` variable is also used to put template copies in the right place
      var folder = DriveApp.getFolderById(returnObj[i].folderId);
    
      // Set the submitter as an editor on the folder
      // This will send a custom email to the presenter with information about the session.
      Drive.Permissions.insert(
        {
          role: 'writer',
          type: 'user',
          value: returnObj[i].ownerEmail
      }, 
         folder.getId(),
      { 
         sendNotificationEmails: false 
      });
    
      // Set up form. Make a copy of the template and set the name
      var form = DriveApp.getFileById(FORM_TEMPLATE);
      var formCopy = form.makeCopy(folder).setName(returnObj[i].title);
      
      // Store the ID to link it to the spreadsheet
      var formId = formCopy.getId();
    
      // Setting the form Title needs to be done through FormApp
      FormApp.openById(formId).setTitle(returnObj[i].title) 

      // Copy the spreadsheet into the folder, get the ID
      var ss = DriveApp.getFileById(SS_TEMPLATE).makeCopy(folder).setName(returnObj[i].title + " (Responses)");
    
      // Get the spreadsheet ID
      var ssId = ss.getId();
      
      // Open the feedback spreadsheet to clean it up.
      var ssResp = SpreadsheetApp.openById(ssId);
    
      // Link the form
      FormApp.openById(formId).setDestination(FormApp.DestinationType.SPREADSHEET, ssId);
    
      // reset the name of the form for easier collection in the dashboard.
      // Delete 'Sheet 1' to keep the spreadsheet clean
      ssResp.deleteSheet(ssResp.getSheetByName("formData"));
      ssResp.getSheetByName('Form Responses 2').setName("formData");
      
      // Pass information to the presenter with instructions
      sendPresenterEmail(returnObj[i].owner, returnObj[i].name, folder.getId());
  }
}

function sendPresenterEmail(owner, stringName, folderId) {
  var msg = "<b>Hello " + stringName.split(" ")[0] + ",</b><p>Thank you for submitting your workshop. Your feedback form is now ready.</p><p>Open the spreadsheet <b><a href='" + DriveApp.getFileById(folderId).getUrl() + "' target='blank'>in the event folder</a></b> to get the feedback link and QR code. Copy and paste these items into your materials for attendees.</p><br /><br />Sincerely, <br /><br /><p>" + yourName + "</p>";
  GmailApp.sendEmail(owner, 'Automated Message: Your Workshop Folder is ready', msg, { htmlBody: msg, noReply: true });
}

popup.html

<!-- In the Feedback TEMPLATE, go to New > HTML File.
     Name it 'popup' and replace the text with the code below. -->

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      #link { width: 100%; font-family: monospace; font-size: 36px; font-align: center; }
      #qrContainer { display:block; width: 100%; height: auto; }
      #qr { display: block; width: 60%; height:auto; margin: 10px auto 0; }
    </style>
  </head>
  <body>
    <div id="link"><?!= getShortLink(); ?></div>
    <div id="qrContainer"><img id="qr" src="" /></div>
    
    <script>
      document.addEventListener("DOMContentLoaded", function (e) {
        google.script.run.withSuccessHandler(insertImg).getShortLink();
      });
      function insertImg(url) {
        var url = encodeURIComponent(url);
        
        // Post the shortlink to a QR Code Generator API to append to the popup as an image.
        var src = "https://api.qrserver.com/v1/create-qr-code/?data=" + url + "&size=250x250"
        document.getElementById("qr").src = src;
      }
    </script>
  </body>
</html>