Fun Scripting in Apps Script Hackathon @ NC State

Editor’s Note: Guest Author Md. Ahsanur Rashid is a MS student in the department of Computer Science at North Carolina State University. He recently participated in Google Apps Script Hackathon.

When Google Apps Script Hackathon arrived at NC State, I took up the opportunity to learn Google Apps Script. In school, one of the problems that every group of friends always face is finding the best time to meet somewhere and have fun. Of course, we can use Google Calendar but what we needed was a collaborative way to choose best time and location for all of us to meet. Without a script or some sort of automation this can get kind of crazy and especially in large group settings. Lots of phone calls, emails, endless pings, buzzes and knocks. But it does not need to be this way because we can use Google Apps Script. So, here is how we solved the problem by writing a Google Apps Script.

Step 1 - Every weekend, each friend on the list will be sent an email with a link where he/she can choose the desired time before a preset cutoff. To make the form more user friendly, we designed the form to allow users to choose from a list box rather than directly entering a value.

function sendReminder() {
  // get the sheet and last row data
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  for ( var i = 2; i <= lastRow; i++) {
    // get the email and name for each row of the form
    var userEmail = sheet.getRange(i, getColIndexByName("Email")).getValue();
    var userName = sheet.getRange(i, getColIndexByName("Name")).getValue();
    // send an email to the user
    MailApp.sendEmail(userEmail, "Please send us your best time!", 
      "Hi" + userName +",\n\n" + "Please fill the form at ....",
      {name : "Best Time Chooser"});
  }
}

Step 2 - At the cutoff time, our script will process all the responses from all the friends. We configured an Apps Script trigger which will execute the getBestTime() function at the cutoff time on Sunday. The script reads the selected time preferences from the spreadsheet using Spreadsheet Service in Apps Script. The most popular response wins. In case of a tie, a default time (Sunday) is chosen.

Step 3 - After calculating the most popular time preference, the script uses Mail Services to send an email to each friend. All the friends are sent an email to let them know the chosen date.

A very useful feature in Apps Script is debugging. And it could not have been easier to debug in Google Apps Script. We can use Logger.log(); to log the values and debug based on those. Using View->Logs inside the script editor we can see the log file.

To summarize, it was an interesting experience using Apps Script at Google Apps Script Hackathon. Google Apps Script is quite intuitive and great for automating manual tasks.

Posted by Md. Ahsanur Rashid