Category: Google

  • Daily Google Form Email

    There are many times when setting up a Google Form where you’d like to get a summary of the responses you’re getting day over day. In some instances you may only need the results for the day. In other instances, you may want to continue to see the log as it grows. During these difficult times of the COVID-19 pandemic, my girlfriend is trying to do everything she can to keep her elementary students safe. Part of that includes knowing where they are in the school, how long they’ve been there, and how often they may be leaving to determine any trends if one particular students seems to be leaving often. Or, if someone gets sicks, who they’ve been in contact with. It’s important that the students remain as isolated as possible to help prevent any potential spread to other students or faculty.

    With that being said, she set up a Google Form to serve as a Daily Hall Pass. Now that we have a form set up gathering student responses, what will we need to do to complete the cycle and create an automated email for her at the end of the school day?

    • Create a Google App Script to collect the form responses.
    • Use the DriveApp class and MailApp class to email those responses to your inbox
    • Setup daily email trigger to automate the process

    Creating the Google App Script

    First things first, go to Google Sheet of responses and choose Tools > Script Editor. Google App Scripts will launch a new empty function for you to start with.

    Now we can start building out our function to pull the responses from the spreadsheet. From the code to follow, you’ll notice I’m using a library to assist with grabbing the actual row data called GSheetsUtils. You can navigate to that linked guide to get instructions on how you can add the library for use within your function:

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var responses = GSheetsUtils.getRowsData(sheet);
  Logger.log(responses);
}

    After you’ve added code to get the active spreadsheet, get a reference of the active sheet, and use the GSheetsUtils library to get all data from every row, you can log it out to ensure it’s working appropriately. When you run your function and open up the Logger, it should look something like this:

    Use MailApp to Email Responses

    Now that we know we are gathering the data correctly, we need to create an email to send to ourselves. To do this, we’ll first get a reference to our spreadsheet using the DriveApp class:

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var responses = GSheetsUtils.getRowsData(sheet);
  
  var fileId = ss.getId();
  var file = DriveApp.getFileById(fileId);
}

    Once we grab a reference to the file using the file’s ID, we can assign variables to hold the values needed for creating the email:

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var responses = GSheetsUtils.getRowsData(sheet);
  
  var fileId = ss.getId();
  var file = DriveApp.getFileById(fileId);
  
  var recipient = '<INSERT_YOUR_EMAIL_HERE>'
  var subject = 'Daily Form Email';
  var body = 'Please find the daily form responses attached.';
  var files = [file.getAs(MimeType.PDF)];
  var options = {
    attachments: files
  }
}

    You’ll notice that in order to email the file as an attachment, we use a function getAs() on our file, with a PDF mime type passed in, and add it to a files array. This will give us a PDF copy of our responses attached to our email. Now, using the MailApp class, we can send out the email. For my email I’m checking the see if I have any values, using the getValues() function, by checking against the sheet’s full range of data. If I do have data, I use one set of parameters of the sendEmail() function to send the attachment. If I don’t have data, I use another set of parameters to simply send a text-based email to indicate as much:

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var responses = GSheetsUtils.getRowsData(sheet);
  
  var fileId = ss.getId();
  var file = DriveApp.getFileById(fileId);
  
  var recipient = '<INSERT_YOUR_EMAIL_HERE>'
  var subject = 'Daily Form Email';
  var body = 'Please find the daily form responses attached.';
  var files = [file.getAs(MimeType.PDF)];
  var options = {
    attachments: files
  }
  
  var values = sheet.getDataRange().getValues();
  if (values.length > 1) {
    MailApp.sendEmail(recipient, subject, body, options);
    sheet.deleteRows(2, values.length);
  } else {
    MailApp.sendEmail(recipient, subject, 'No logs today.');
  }
}

    As you can see, I chose to delete the rows, starting after the title row, after the script is run. This is to keep the daily email truly just that, a log representing the current day. That line could be removed if you want to receive the full log as it grows. Now we can hit run to give our email a test. If all goes correctly, you should see something like this in your inbox:

    Setup Daily Email Trigger

    Finally, we can set up a trigger so that this function will run on a schedule for us. Navigate back to the Google App Scripts landing page. Click the menu next to your script and choose the Triggers option. Select Add Trigger and fill out the form to set up when you would like to receive the email.

    Now, every day during the selected time frame, your function will run. It will gather all responses, generate a PDF file to attach to an email, send the email, and delete those responses (if you want it too). And you’re all set 🙂

    Happy coding!