Automatic / IFTTT / Google Sheets integration to Make Car Log

  • 5
  • Idea
  • Updated 3 years ago
  • Implemented
Archived and Closed

This conversation is no longer open for comments or replies and is no longer visible to community members.

I have used Automatic with some IFTTT recipes and Google Spreadsheets and Google Scripts to create a fairly functional car log. I'll be updating this thread with instructions on how to make it your own.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb

Posted 3 years ago

  • 5
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Things you'll need...

1. Make a copy of the Log: https://docs.google.com/spreadsheets/d/18PaMbHTIO1LJkZJdu-LRJxzd-NuiaHuA39FDUuwAkRI/edit?usp=sharing

2. Make a copy of the supporting fill up log: https://docs.google.com/spreadsheets/d/1isXlWf-emBK61xE92IPsRr1uAdphO0jfKTs_fnu4EPo/edit?usp=sharing

Note 1: When you make copies of these documents, place them in a folder in your drive /IFTTT/Automatic/

Note 2: When you make copies of these documents, it also makes copies of the underlying scripts (which I'll get to later, don't worry they won't do anything yet).

3. Get some IFTTT recipes:
The Main Recipe: https://ifttt.com/recipes/295056-log-my-trips-in-a-useable-format-to-google-drive
The Odometer Updater: https://ifttt.com/recipes/295377-log-gas-fill-ups-in-google-drive
The Odometer Updater Verification: https://ifttt.com/recipes/295383-send-verification-of-fill-up-recording

4. Make sure your IFTTT is connected to your Gmail. This won't work without it because the scripts work intimately with your Gmail Account.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Now, clear the log. But do it in a very particular way.

1. Unhide all the sheets that are hidden.
2. Delete all rows in "Raw_Data" Sheet EXCEPT FOR THE LAST ONE. (I know that one is mine, we'll deal with it later I promise).
3. Set "Old" and "New" on the "Trigger" Sheet, to 1.
4. Delete all rows in the "Trip_Log" Sheet EXCEPT FOR THE LAST TWO. (I know those are mine, we'll deal with them later, I promise).
5. Hide the "Raw_Data" and "Trigger" Sheets. We are done with them.
6. Leave all the other sheets alone for now... We'll get there.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Next, clear the gas log by deleting all but the last row. We'll get rid of it later.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Now you should have two "relatively" clean documents. You're going to want to rename them to whatever the IFTTT recipes are pointing to.

If you are using my recipes, your names should be "Your Car Name Trips" and "Gas_Trigger".  Both should be in the your drive folder /IFTTT/Automatic.

If you know what you're doing with IFTTT, by all means, name them whatever you want.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Next is script manipulation... Yay!!! If you don't know anything about programming, don't worry.  I'm going to make it all better.

1. Open up the "Gas_Trigger"
2. Go to Tools>Script Editor
3. In a new window, Open up your "Car Log".
4. Click on the address bar and copy the URL for your "Car Log"
5. Return to the Script Editor.
6. Paste your URL over my URL on line 20.
7. In the Script Editor, go to Resources>Current Project Triggers
8. Click on the blue words to set up a new Trigger.
9. Set the Dropdown Menus as "Time_Check", "Time-Driven", "Minutes Timer", "Every 15 Minutes"
10. Save the trigger and close the pop-up.
11. Click on the "Select Function" box next to the spider. Select "Time_Check".
12. Press the play button.
13. You will be prompted to authorize the script to run. It will tell you it is going to access your spreadsheets and send email as you. This is okay. Authorize the scripts.
14. We are done with the "Gas_Trigger"

Let's move on.
Photo of Darren Harkins

Darren Harkins

  • 124 Points 100 badge 2x thumb
Line 6 says to paste the URL over the URL in line 20.  Line 20 of the sheet I copied above does not have a URL in it, only lines 45 and 47 do.

This is my second attempt at setting up this sheet and would like some clarification on what URL needs replaced.

I see that you are using this to track 2 vehicles, V50 and S80, and I think that's where my problems originated last time I tried this.

Alternately, if you could provide code to only track one vehicle or tell me what needs removed, that would be even better.

I know just enough about Javascript to make minor edits.
Photo of Jonathan

Jonathan, Alum

  • 4,232 Points 4k badge 2x thumb
Thanks for sharing all this information! I'm sure our community will really appreciate it as well.

We look forward to your continued success with Automatic. :)
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Now we need to set up the "Car Log" scripts to run. Don't worry, no code here, just three triggers to setup.

1. Open up the "Gas_Trigger"
2. Go to Tools>Script Editor
3. In the Script Editor, go to Resources>Current Project Triggers
4. Click on the blue words to set up a new Trigger.
5. Set the Dropdown Menus as "Checker", "Time-Driven", "Minutes Timer", "Every 5 Minutes"
6. Save and create a new trigger.
7. Set the Dropdown Menus as "Oil_Change", "Time-Driven", "Week Timer", "Every Monday", "Whatever time you want"
8. Save and create a new trigger.
9. Set the Dropdown Menus as "Oil_Change", "Time-Driven", "Week Timer", "Every Sunday", "Midnight to 1AM"
10. Save the triggers and close the trigger window.
11. Click on the "Select Function" box next to the spider. Select "Checker".
12. Press the play button.
13. You will be prompted to authorize the script to run. It will tell you it is going to access your spreadsheets and send email as you. This is okay. Authorize the scripts.
14. We are done with the "Car Log"

Let's move on.
Photo of culmac

culmac

  • 124 Points 100 badge 2x thumb
So far, 'Pretty amazing stuff' a couple points,
In script editor the name at the top came up 'copy of gasser', I renamed to 'gasser' same with the other file, I deleted 'copy of'

In the above para, first line reads (Open up the "Gas_Trigger") should be 'Car log'?
also in the above para, line 7 and line 9 both refer to 'Oil Change' what should the second trigger (line 9) refer to?
Thx
Jim,
culmac@gmail.com
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Renaming the script files is no big deal, you can do it if you want they will still be tied to the sheets regardless.

Here are some screenshots for the triggers.
The first is for the triggers associated with the Gas_Trigger. The second is for triggers associated with the Car_Log. To answer the question in words though, the 3rd trigger should run Row_Hider.


Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Speaking of the Oil Change, I forgot to add this IFTTT recipe.  You'll need it (or something similar) to process the emails that the log sends.

https://ifttt.com/recipes/295953-oil-change-notification
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Yes, Step 1 should read:

Open up the Car Log
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Now you are ready to go for a drive. But before you do.
Make sure your IFTTT Recipes are turned on and pointed to the right files.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
For your drive, you're going to want to go somewhere, turn off the car, then return. You need at least two legs in your trip.

Write down your odometer reading upon completion.

When you get back wait about 15 minutes (for the triggers to fire from IFTTT and in your spreadsheet).

Open the car log.  Your 2+ trips should be on the "Trip_Log" sheet. Now you can delete the rows which contain my trips.

After you do, go to the menu Car Menu>Enter Odometer.  This will prompt you for an odometer reading. In the future, when you want to enter a manual Odometer reading, this is how you do it.  Note that this is different from sending IFTTT text messages of the Odometer reading, that is a bit more automatic.

Your last trip in the log should now have the correct odometer reading. Delete the odometer reading from the previous trips. If you want, you can do some fancy maths to figure out what they should have been.

The "Car Log" is now up and running.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Next time you get gas, after you are done send IFTTT a text formatted the following way. This works best if you fill up, as all the code assumes that is what you are doing, since that is what I do when I get gas. If you don't fill up every time you get gas, you probably don't want to use this function.

#gas ODOMETER/TOTAL PRICE/GALLONS

Don't put a "$" before the total price.

After about 15 minutes, you should get a text back saying the same thing with your MPG added to the end.

______

When you get home, go into the "Gas_Trigger" and delete the row that contains my data.

Now the "Gas_Trigger" should be up and running.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Last step!

Go into the "Car Log" and then the "Stats" sheet.

Where it says "Dec 2014" enter "MM/01/YYYY" where MM and YYYY are then Month and Year that you took the first logged drives on.

The rest of the months should update to a bunch of zeros and blanks.

Now the stats are all yours.
Photo of culmac

culmac

  • 124 Points 100 badge 2x thumb
Looking good, just waiting for my gas conformation.

one issue, "Where it says "Dec 2014" enter "MM/01/YYYY" where MM and YYYY are then Month and Year that you took the first logged drives on"

I entered 05/31/2015 and it changed the months to May, July, August..etc
It skipped JUNE , I changed the date to 05/30/2015 and it worked ok.

Jim
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
For this one, the problem was because you put 05/31/2015. If you had put 01 instead of 31, it should have worked just fine. Glad that the 30th is working for you, though you might want to change to the 1st just to be sure.
Photo of culmac

culmac

  • 124 Points 100 badge 2x thumb
Thanks for your assistance, I missed that :-)
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
So what does this do...

It keeps an estimate of your odometer.

It allows you to correct your odometer periodically.

It allows you to keep track of your fill-ups.

It tells you to get an oil change and check your brakes every 10000 miles.

It lets you keep track of maintenance performed on your car.

It visualizes your driving habits and stats on a graph.

It hides non-important (non fill-up and no-note) trips every Sunday.

I think that's about it, there might be more.

Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
DISCLAIMER: I wrote these instructions as I was performing them and the process worked! That being said, I know what I'm doing. If you don't know what you're doing and the instructions don't work for you, sadly you probably did something wrong.  I'm willing to help out, but don't expect 24 hr tech support.

Additionally, I fully plan on making my car log better and adding more features.  I think that you should to. These instructions were posted on 28 May 2015. Depending on when you read this and copy my spreadsheets, things may be different. I'll try to update this thread as I update my Car Log if the changes I make warrant an update.

If you don't understand how everything works, I implore you to figure it out so you can make this car log work for you.
Photo of Amy

Amy

  • 68,970 Points 50k badge 2x thumb
This is amazing, Nathan! Thank you so much for sharing all of this!
(Edited)
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
UPDATE ALERT!

I added a route efficiency notifier. Basically, I wrote a formula in google sheets that accepts your start and end gps positions from IFTTT. It takes those and plugs them into the Google Maps API. This spits out the shortest distance driving path between those two points. The car log then determines if the distance you drove was within 5% of the shortest path. If it is not, the log sends your phone a nasty gram, scolding you for your bad behavior.

Now my Automatic gives me feedback during the drive and after the drive.

Implementation instructions:

1. Add this to the end of the Main IFTTT Recipe "|||=mapdistance("{{StartLocationLat}},{{StartLocationLon}}/{{EndLocationLat}},{{EndLocationLon}}")"

2. If you built your log using my instructions before 30 May 2015. Paste this code into the RowAdder() function above the line that reads "return 1".

      var long_trip = newrange.getCell(1,19).getValue();    if (long_trip>0.05){
      var distance = newrange.getCell(1,4).getValue()*(1-1/(1+long_trip));
      MailApp.sendEmail({
          to: "trigger@recipe.ifttt.com",
          subject: "#LongTrip",
          body: "SHAME!!! You just drove out of your way by "+distance.toFixed(2)+" miles. Get Directions!"
      });
    }

3. If you built your log using my instructions before 30 May 2015. Change the 19 in line 51, to a 20. Line 51 should read "newrange.getCell(1,20).clear({contentsOnly: true});"

4. If you built your log using my instructions before 30 May 2015. Paste this code at the very bottom of the Car Log Script.

function mapdistance(gps){
  var start = gps.slice(0,gps.indexOf("/"));
  var end = gps.slice(gps.indexOf("/")+1,gps.length);
  SpreadsheetApp.getActiveSpreadsheet().toast(start+" / "+end);
  var directions = Maps.newDirectionFinder()
     .setOrigin(start)
     .setDestination(end)
     .setMode(Maps.DirectionFinder.Mode.DRIVING)
     .getDirections();
  var distance = directions.routes[0].legs[0].distance.value;
  return(distance / 1000 * 0.621371);
}

5. Implement this IFTTT recipe: https://ifttt.com/recipes/295783-send-shame-notifications-for-driving-inefficient-routes
Photo of Darren Harkins

Darren Harkins

  • 124 Points 100 badge 2x thumb
First, let me say this is awesome and is really the data I'm wanting the Automatic adapter to give me.

That said, these scripts are horribly broken at this point.  I have tried, unsuccessfully, 3 times now to get them to work for me, and they just don't.  Due to the personalizations made by the author, all the scripts are intricately tied together and when one breaks, the rest come crashing down around it.

I have tried to get the author of these awesome spreadsheets to respond to me with some form of remediation, but it has been a week now with no response.

If you are comfortable with Javascript and can edit out the personalizations the author has added, these will probably be easily adapted for your needs.

If you don't know Javascript, don't waste your time trying to get these to work for you, they won't.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Hello, I haven't received any requests about this project. I also haven't noticed that it is broken. Still works exactly as I expect it to. I would be glad to help if you need it.
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
The only thing I would say is that I no longer openly share the project because I'm using a few APIs with it now and I would rather not share those keys publicly.
(Edited)
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
**UPDATE**
Everything you've read above this isn't exactly useless, but there are lots of changes.

First here are new links to the public documents. They are as clean of copies as I could make and I don't use them, so they will always be a "fresh start."
Car Log
Gas Trigger

Main changes:
1. The Gas Trigger supports multiple (2) cars now, read the code commenting to enable it. The Car Log will still work independently without the Gas Trigger.
2. I switched from EMAILS triggering alerts to MAKER API calls triggering alerts in IFTTT. That means you'll need to enable that channel, get an API key, and build some appropriate recipes. Read the documentation in IFTTT, it's not that tough.
3. I switched from APPS SCRIPT implementation of Google Maps Distance Calculator, to an API call. So you need to set up a project in the developer console for your script, turn on the Directions API, get a key, and paste it in the appropriate place in the code.  If you don't want this feature, don't do anything, when the API is called it will fail and the script will return useless text.
4. I added functions to install the triggers for you, so there is less of a chance you can screw that part up.  Just read the code commenting and follow instructions.

Bottom Line:
The Car Log will work without setting up the notifications. It will also work without setting up the Google Maps Distance comparator. It will also work without the Gas Trigger.  If you want all those extra functions, you're going to have to read the comments and some API documentation to get them working.  If all you want is data, just set up the Car Log.

Hopefully this helps...
-Nathan
Photo of Nathan

Nathan

  • 2,128 Points 2k badge 2x thumb
Recipes I'm using at the moment that will work with the files linked above.

Trip Pusher
Gas Trigger
Handler for Notifications

This conversation is no longer open for comments or replies.