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.
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:
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
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