Anyone else get stuck with fixing their family's computer problems? Recently I had a family technical issue that was actually fun to solve and didn't involve removing malware. My family owns Rasmus Auctioneers, a global auctioneer company based in Alexandria, Virginia. The company uses Google Apps for their entire business workflow, so their documents, calendars, etc. are all connected.
We manage the shipping process on-site using a tablet. In this fast-paced environment and with limited time, we need a quick, easy solution. When an auction ends, the clients fill out a Google Form that saves their shipping and payment information to a Google Spreadsheet. This information then needs to be sent to a specific mover for processing -- but the mover doesn't need all the data, just a few important pieces. All of this needs to happen with as little human intervention as possible.
Our solution was to create an Apps Script within the spreadsheet, which means our new functionality works in the tablets used on the auction floor. The function below runs when the sheet is opened, automatically sorting the sheet to put the newest client on top. A simple dialog determines which data is important for a particular auction; the script then finds the data we need, sends it to the right person, and adds a calendar event as a reminder.
function sendData() { // Sort the rows var sheet = SpreadsheetApp.getActiveSheet(); sheet.sort(4); // What row do we want? var myValue = Browser.inputBox("What row?"); // Variables var emailRecipients = "me@mydomain.com"; var emailSubject = "Moving data for "; var valueToShow = ""; // Get cell values var clientName = sheet.getRange("B" + myValue).getValue(); var movingDate = sheet.getRange("D" + myValue).getValue(); valueToShow += "Name: " + sheet.getRange("B" + myValue).getValue() + "\r\n"; valueToShow += "Moving Date: " + sheet.getRange("D" + myValue).getValue() + "\r\n"; valueToShow += "Size: " + sheet.getRange("E" + myValue).getValue() + "\r\n"; // Send email MailApp.sendEmail(emailRecipients, emailSubject + clientName, valueToShow); // Add to calendar CalendarApp.createAllDayEvent("Move today! " + clientName, new Date(movingDate)); }
Looking at the source code, we can see how we collect the data from the relevant columns, add them to a variable, and in one line send it via Gmail. Adding a reminder to our calendar also takes just one line.
Do you have a repetitive process that bugs you? Wish there was a way to automate something? You might be able to solve your problems the same way I helped out my family -- with Apps Script! Feel free to leave a comment on some ideas or suggestions for future projects.
