Using Google For Work / Google Apps To Automate Giveaways

Raffle tickets rolled up waiting to be handed out.

Pulling a ticket blindly from the fishbowl, the winner was selected.  How many have seen this a time or two?

Vino Pair is excited to be a vendor in Kansas City's first annual, sold out Uncorked Event.  Wine makers from all over the world and local food trucks will converge on Union Station, March 26th. There are many giveaways that Vino Pair will be offering all the 3,000 attendees.  Does Vino Pair give out tickets and pull from a fishbowl, hat, etc? No, that's not scaleable with 3,000 people.  Plus, it's 2016.  Is there another, simple option?

Google For Work aka Google Apps, Google Forms, QR Codes, and a little Google App Script and Vino Pair has the following:

  • Easy way to get to Google Form via QR code.
  • Google Form that accepts first, last name, email.
  • Google App Script that gets the count of entries, picks a random winner.
  • Google App Script also emails the winner and Vino Pair.
  • Google App Script "Pick Winner" routine can be activated from a mobile device.
  • No computer is needed to activate winners, just a mobile phone with the Google Drive App.

Here's the Google Form that was used.

Google form editor page

Google form editor page

Assumptions:

  • Most people coming to a wine event are going to bring their smart phones for photos.
  • If there's a group, someone in the group is bound to have smartphone that all can use.
  • Once the link is scanned at the booth, entries can be entered at the user's discretion.
  • Allows easy flow through booth using technology and eliminates the need for paper.
  • Some people will not be able to enter the contest and that's okay.
  • Email will work to notify winners so giveaway items are handed out same-day.

Recreating the example is simple.

Step 1 - Create a new Google Form

After creating the Google Form, nab the Form ID from the url.  See below - the ID is between the red lines.

google form url example

google form url example

Step 2 - Click the three dots in the top right of the Form editor page.  A menu will appear. Select the script editor from the list of options and your screen should look like the following.

Google script editor page

Google script editor page

Put the ID captured from the above step into the code below where it has -------------Google Form ID from URL------------.

function myFunction() {
 var form = FormApp.openById('-------------Google Form ID from URL------------');
 var formResponses = form.getResponses();
 var i = Math.floor(Math.random() * formResponses.length) + 0
 var formResponse = formResponses[i];
 var itemResponses = formResponse.getItemResponses();
 var winning_first_name = itemResponses[0].getResponse();
 var winning_last_name = itemResponses[1].getResponse();
 var winning_email_address = itemResponses[2].getResponse();
 sendemail(winning_first_name,winning_last_name,winning_email_address)
}

function sendemail(fname,lname,email) {  
   MailApp.sendEmail({
     to: email,
     cc: "mike@vinopair.com",
     subject: fname + " Is A Winner!!!",
     htmlBody: "Vino Pair Giveaway Winner Is = " + email + " -- " + fname + " " + lname + " - Please come to the Vino Pair booth to claim your prize!!"     
   });
 }

function onOpen(e) {
  var menu = FormApp.getUi().createAddonMenu(); // Or DocumentApp or FormApp.
  menu.addItem('Start workflow', 'myFunction');
  menu.addToUi();
}

Change the email routine to accurately reflect your giveaway.  Name your project.  Save the code and publish it. You need to enter a description of the code and accept some permissions on your Google Apps account.  It only takes a few seconds and your code is published, then it can be used in your Google Form.

The function called onOpen(e) fires when the Google Form is opened.  This code instructs this Google Form to add a menu option.  This is how it appears in the Google Form once it's published.

Click the puzzle piece to run the script and pick a winner.

Click the puzzle piece to run the script and pick a winner.

Step 3 - Now that the form is complete, it's time to create the QR codes. 

Googled 'QR Code maker' and this was the first result.  Just enter the url of the form into the QR code creator webpage and hit the create button.  You'll be able to download an image of the QR code after a moment from their sponsors.  Now, just print out the QR code and make it available at your booth.   When the code is scanned the user is taken to the form.  Here's a sample screen shot from an Android device.

google form on an android device

google form on an android device

Step 4 - Finally, the form security options will need to be adjusted in order to make sure customers can access your Google Form.   While on the Google Form page, click the gear and the make sure to change the setting to look like these settings below.

Google Forms settings

Google Forms settings

Save your settings.  To select a winner, open the Google Drive App, select the Google Form, and click the puzzle piece.

mobile google form edit view with google apps script puzzle piece shown

mobile google form edit view with google apps script puzzle piece shown

When the winner is selected, simply pull up the email that was cc'ed to you for verification purposes.  Done.

Problems:

  • A person can enter multiple times, but could be eliminated if login was required.
  • The script does not remember who it picked previously.
  • A winner can win multiple times.

Obviously it needs a little work, but for only 15 minutes of work, the results were pleasing.

 

Osage Food Moves Forward With Google Apps

I'm excited to announce that OsageFood.com, an ingredient supply company located in Washington, Mo, with a primary focus in the dairy industry, is 5 Star Net's latest company to transform from Microsoft Office / Exchange to Google Apps.   I use Microsoft Office / Exchange, but what I don't like is the reoccurring upgrades, feature incapability between versions, overly complicated interface, file-contention in a multi-user environment, slow loading in outlook, and, most of all, how expensive it is.  Google Apps is only $50 per user, per year and you get email - 25 gigs worth.  If you need more storage, that can be purchased for a fee, as well. Best of all, there's no long download and software to install.  Just open Google Apps in your browser.



Installing Google Apps was a snap.  After we were able to login to the client's DNS provider, and using our Google Apps Reseller Tools, we were able to stage and stack up to 6 MX records for a painless  switch-over. After a training session with the entire OsageFood.com staff, we began eating lunch so I took the opportunity to switch MX records.  Knowing how DNS records can take hours to update was not a concern as it switched over instantly.  Following the switch-over, we were able to login as each employee, reset the password, and get all the mobile devices setup, too.  Part of the setup required us to migrate data from existing outlook users. Google provides a simple Google Apps Outlook Migration Tool that make the process of storing old outlook data in Google Apps, very simple. 

 

By the time I left the building, the entire staff at OsageFood.com was able to use Google Apps on their desktop computers, laptop computers, tablet computers, and smartphones.  It's like the devices were made for Google Apps.  In fact, there were so many devices in the building that network services had to provision 50 more ip addresses so we could all communicate.  Bill Dickenson, owner of OsageFood.com, sent me this note just a few days after starting with Google Apps, "Everyone seems to be getting the hang of the Google Apps.  Some bugs to work out and still need to learn all the features but everyone seems to think it will be better."  

 

There are many neat features in Google Apps, but my favorite isn't a feature.  It's how Google designed Google Apps to be extensible with the Google Apps Marketplace.  There are so many additional products that can be turned on in minutes.  Pro Athlete uses the Mojo Help Desk solution for a help desk system and it works great.  There are so many others like, Run My Process. It's got 200 connectors, and counting, that allow workflow. For example, workflows to connect to an api, execute a command, retrieve the results, could be automated and setup within a few clicks.

 

I'll be sure to provide an update as to how Bill and OsageFood.com are doing in a few months of using Google Apps.

 

Creating a Management Dashboard Using Google Docs

It's never been easier than now to create a simple management dashboard using the free Google Application Suite.  After setting up some objectives, strategies, and action plans for our fictious company in a Google Doc, I'll show you how to use Google Sites to tie it all together.  Finally, I'll use a Google Spreadsheet to keep track of the data and formulas for all the calculations.  Let's get started.

Go to www.google.com and login.  If you do not have a login, create one using your an email address, and you'll have access to all the tools I using for the article.

Go to docs.google.com and create new spreadsheet.  It will open by default.  First thing is to set a goal.  For our fictious company,  XYZ Corporation, I've set a sales goal of $1.2 million, which is 16.7% higher than last year.  I enter this value in one of the cells in the Google Docs - Spreadsheet.

 
Now we've setup a sales goal, let's add our sales plan to a Google Site.  During your first visit, you'll be asked by Google Sites to setup a site so you'll need to provide a name and template.  I just picked a basic one from the list that was offered at the time this article was written.  I called it Company XYZ Corporation and create a new page called CEO Plan.


The mission, objectives, strategies, and actions plans for acheiving 16.7% growth must be added now.  You might be asking yourself, why am I just now developing the mission, objectives, strategies, and action plans for the current year?  The mission, objectives, strategies, and actions plans must be determined and strategized in the previous year by the decision makers way before the new starts.  If this cannot be acheived in your business, there are greater issues you must deal with.

Here is the mission and the objectives, strategies, and action plans for XYZ Corporation in a Google docs that I am going to cut and paste to the CEO plan page.



Next step is to setup a spreadsheet to track sales.  Go back to the spreadsheet you used to set the sales goal, open it, go to a new tab.  Here's a very basic, scaled-down version of my sales spreadsheet.  I'm tracking the instore and online sales separately and adding one line per date and a total column.  
 

 
Now, I need a spreadsheet to track customers.



Now that we have the goal I going to transfer the mission, objectives, strategies, and action plans to the new Google Sites page.  Next to the objectives, I've added color-coded bullet points that I can click on to see the detailed spreadsheet graph of what's going on with each strategy and action plan.

 

I've setup a new page for Objective 1 and Objective 2 and linked to yellow and red bullet points to these pages.  Clicking on the yellow ball takes me to the first objective page.  I've created a chart in Google Docs Spreadsheets, and published the chart which produces an image tag that I can embed into the html of the Google Sites objective one page.

 

The final page appears like this.

 

As the Google Docs Spreadsheets continue to update, so does this image, dynamically.

Now that's a management dashboard that works.  All built using open tools and ideas easily found on the Internet.