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.
-
Go to Google Sheets in your Gmail account.
-
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.
-
Once you’re in the correct google sheet, go to the Tools tab and open the Script Editor.
- 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.
- 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!