Google Apps Script Data Cleaning

Project Challenge: Clean Data, Automation, Collaboration

Recently, I've been diving deeper into the world of Google Apps Script. It's enabled me to unlock more features with Google Spreadsheets, and I'm excited to explore more possibilities with Google products, such as Gmail, Google Docs, and especially Maps. I've currently been using Apps Script to clean up data for various clients and to help automate certain functions, with the end-goal in mind being to save time and increase efficiency and productivity.

Sample Project: UCLA Hedrick Dining Survey

My first solution for this survey project was to produce a powerful and well-structured Excel sheet that would ensure clean and consistently formatted data. I used VBA and advanced Excel formulas to create a dashboard of results and an overview of all dishes featured on the survey. Using color scales and pivot tables, I helped the restaurant managers easily identify the day's results, and also measure these results against the performance of other dishes served throughout the quarter.

This method was powerful in that the data could clean itself and automatically re-calculate with the push of a few buttons. Overall, it took about 5 minutes to download new survey data every day and to update the sheet. However, there were some issues with this:

  • Lack of collaboration: As a file on a shared network, it could only be open for editing by one person at a time.
  • Overly complex: While the buttons and automation greatly distilled the amount of work needed to clean and format the data, it was still more complex than I believed it could be.
  • Continued use of manual updating: Survey data could not be connected directly to the sheet, so someone needed to download the data every day. While it didn't take long, it was still something that could be cut out.

Features

My solution was to move the data to a Google Spreadsheet. This would solve problem #1 - it would make the data completely collaborative and open to multiple users.

To solve problem #3, I discovered an API mash-up service called Zapier that could connect my Wordpress-based survey to my Google Spreadsheet. For $15/month, this eliminated any manual downloading. Additionally, I could use the service for surveys and forms for other clients.

Problem #2 was taken out by a K-O combo of Apps Script and a few very handy formulas. Using the Filter and Array functions and Data Validation, I sorted the data by date. I then created an Apps Script to automatically save a PDF to a folder on Google Drive and to email the PDF as an attachment to a pre-defined contact list.

The script can be viewed as a Github Gist here: https://gist.github.com/hisnipes/8833162

Results

My initial solution using Excel took 5 minutes a day. Using Apps Script and Google Spreadsheets, I reduced this time to less than 1 minute per day. While my initial Excel sheet required a user to press 9 buttons, my new solution only needs a date selection and 1 button.

I'm excited to continue learning about APIs and writing Apps Scripts!


New Solution: Apps Script-enhanced Google Spreadsheet


New Solution: Auto-generated PDFs in Google Drive folder


Old Solution: Clunky Excel Sheet with VBA Macros