Track Your Work Hours with Amazon Alexa

screen-shot-2016-10-24-at-12-11-44-pm

There are so many options for keeping track of your working hours these days. Some are simple, and some not so much. As a former freelancer, I could have only dreamed of the day when I could tell a smart, snarky cylinder such as Alexa to keep track of my working hours. Utilizing IFTTT, Google Sheets and Initial State I can now create beautiful dashboard that shows when I’m working and how often. This is great for any freelancer that would want to share this kind of dashboard with a client to let them know exactly when they’re working on their project.

How is this going to work?

We’re going to set up an IFTTT recipe that uses Alexa to send your clock in data to a Google Sheet. The Google sheet will parse that information, and use a custom function to send those times to Initial State. I’ve already done all the leg work by creating the custom function. Also, by saying “I created the customer function,” I mean my friend Colin at Acklen Avenue and co-worker Broc basically told me what to type. Regardless, all you have to do is copy and paste what Broc, Colin and I created.

Project Difficulty: Easy like Peyton Manning on a Sunday morning.

Estimated Time to Complete: About an hour

What you’ll need:

  1. An Amazon Echo
  2. An Initial State Account
  3. An IFTTT Account
  4. A Gmail account for Google Sheets

Step 1: Amazon Echo

If you don’t have an Amazon Echo, you can purchase one here.

Step 2: Initial State

Sign Up

Signing up for Initial State is easy! Just go to initialstate.com/register to create your account. Type in your email, password, click register and you’re done!

You can watch this video if you’d like a walk through of how to sign up:

Create a home for your punch card data 

This is where you’ll create a location for your data to post.

  1. Click the cloud icon in the top right of your bucket menu to create a new https bucket.

Jamie example key covered

  1. You can name your bucket anything you wish. Mine is named “Clocking In and Out.” You can also go through this process multiple times for different clients. For instance, you could make a bucket named, “Initial State Punch Card” if you were tracking hourly work for Initial State, and make another one called, “Amazon Punch Card” if you were doing tracking hours for Amazon.

screen-shot-2016-10-18-at-12-53-05-pm

  1. Click Configure Endpoint Keys
  2. Optionally, to make it easier to recognize my API endpoint key, I changed my bucket key to Punchcard.
  3. Below that, you can change your access key if you wish. Using whichever one pops up is fine.
  4. Click create

Once you’ve gone through those steps, you’ll see you’ve created a new bucket ready to fill up with some data. Next you’ll set up your IFTTT accounts and get your Echo to fill that bucket up!

 

Step 3: IFTTT

  1. Create your IFTTT account here: https://ifttt.com/join
  2. Create a new recipe
  3. Click My Recipes
  4. Click Create a Recipe in the top right’

Screen Shot 2016-06-20 at 1.16.49 PM 

  1. Click the blue text that says “this.”

Screen Shot 2016-06-20 at 1.17.40 PM

    1. Search for Amazon Alexa, and then click the Alexa icon.

screen-shot-2016-10-18-at-1-17-30-pm

  1. Select the first option that says, “Say a specific phrase.”

screen-shot-2016-10-18-at-1-18-31-pm

  1. Type in the phrase that you want to trigger Alexa to start tracking your work time. I used the phrase, “clock me in.”

screen-shot-2016-10-18-at-1-19-35-pm

  1. Click, “Create Trigger.”
  2. Click the blue text that says, “that.”

screen-shot-2016-10-18-at-1-21-28-pm

  1. Search for Google Drive and click on the Google Drive icon.

screen-shot-2016-10-18-at-1-22-09-pm

  1. Click “Add row to spreadsheet.”

screen-shot-2016-10-18-at-1-22-59-pm

  1. Here you can either create a name for your spreadsheet, and have IFTTT create a new sheet after your first trigger, or name it a sheet you’ve already created to have it add to a sheet already on your drive. We’ll make sure it goes to the right folder after the next couple of steps.
  2. This part looks complicated, and it is, but all you have to do is copy and paste. All this script below is saying is, “When I use this trigger phrase with Alexa, send the date and time to Google Sheets, parse it, and when I trigger Alexa to clock me out take that parsed information and send it to Initial State.”

screen-shot-2016-10-18-at-1-24-27-pm

Paste this into the formatted row box:

{{TriggeredAt}} ||| =IF(ISODD(ROW()), “Started”, “Stopped”) ||| =onEdit(IF(ISEVEN(ROW()),ROUND(((DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)), “\w+ \d{2}, \d{4}”)) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)), “\d{2}:\d{2}[A|P]M$”))) – ( DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), “\w+ \d{2}, \d{4}”)) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), “\d{2}:\d{2}[A|P]M$”)))) * 24, 2),””))

Note: Big thanks to IFTTT user danamerrick. I totally used what they had done with the do button application, and added our custom Google Sheets function to it.

  1. Type the folder path for your Google Sheet. For Example, the image below shows that in Google Drive, my spreadsheet will be located in a folder called Punchcard that is located in my IFTTT folder.

screen-shot-2016-10-18-at-1-29-21-pm

  1. Click create action

 

THESE NEXT STEPS ARE IMPORTANT!!!!! 

  1. That was painless enough, so let’s do all of that over again. Every step will be exactly the same EXCEPT you will name your Alexa trigger phrase something like, “clock me out.” Technically, you could do this process once and name your trigger phrase something like, “Initial State Project.” However, I think having the separate phrases send data to the same sheet helps me organize my information a little better. This way when I say, “clock me in,” Alexa creates a row of when I’ve clocked in, and then when I say, “clock me out,” it will create another row for when I’ve clocked out. Once your clock in and clock out times have been received, Google Sheets will parse the data, calculate how long you worked, and send it to your dashboard!
  1. Before you trigger your first clock in, we’ll need to create our custom function in Google Sheets.

 

EXTRA! 

In my dashboard, I thought it would be cool to have my clock in and clock out times listed as well as have some text that would say if I’m currently working or not. To do this, I simply made an IFTTT recipe that tied together Amazon Alexa and the Maker Channel. When I told Alexa, “Trigger Clock Jeff In.” It not only triggered the Google Sheets recipe, but it triggered my maker channel recipe to send a message to Initial State that said, “Working Now.” I also did the same for “Trigger Clock Jeff Out,” and had it send “Not Working.” You can see how to use the IFTTT maker channel with Initial State in my Amazon Alexa Scoreboard post here.

Step 4: Google Sheets

What we’re going to do here is create a custom function. If you’re familiar with Google Sheet, Excel or any other spreadsheet application you probably already know a few basic function to manipulate or arrange your data. Here we’re going to be creating a function called, “onEdit.” This means wherever this function is used, it will send whatever data is in that cell to Initial State.

  1. Go to Google Sheets in your Gmail account.
  2. You’re going to need to either create the excel sheet that you defined in your IFTTT recipe, or open the one that you designated for this project. Remember, the sheet has to be in the same location and have the same name as what you put in your IFTTT recipe. Otherwise, your data will go to a different place, and it won’t work.
  3. Once you’re in the correct google sheet, go to the Tools tab and open the Script Editor.

screen-shot-2016-10-19-at-9-02-15-am

  1. Here is where you’re going to put your Java Script code. Copy and paste the code below into the script editor. Take note that you’ll need to replace the part of the URL that says “INSERT_ACCESS_KEY_HERE” with your Initial State access key, and the replace the part that says “INSERT_BUCKET_KEY_HERE” with your Initial State bucket key. Remember you created these 2 keys as part of the Initial State step of this tutorial above. The section of the URL that says “TimeSpent” will be the name of the Tile that is updated in your bucket. You’re welcome to change that as well.
function onEdit(e) {

{

var url = 'https://groker.initialstate.com/api/events?accessKey=INSERT_ACCESS_KEY_HERE&bucketKey=INSERT_BUCKET_KEY_HERE&TimeSpent=' + e;

var response = UrlFetchApp.fetch(url);

}

}

What does all of this mean?

We start with the word “function.” This tells the script editor that we are creating a new function. We then follow it with the text, “onEdit.” This is the name of your new function. The “ (e)” next to that represents the variable data that will be inputted into Google Sheets, and then sent to Initial State. Under that, we tell the script editor that when this script is used to send the data in the parenthesis to Initial State using our API URL Endpoint.

screen-shot-2016-10-19-at-10-05-06-am 

  1. Press save!

Congrats! You’ve created your own custom function in Google Sheets. Now, if you tell Alexa, “Trigger Clock Me In.” Work a few hours, and then tell Alexa, “Trigger Clock Me Out,” it will send your clock in and clock out times to your Google Sheet, do a little math, and shoot it over to Initial State. Try it out!

 

 

3 comments On Track Your Work Hours with Amazon Alexa

Leave a reply:

Your email address will not be published.

Site Footer

Initial State