Record Arrival and Departure Time To A Google Sheet

Does anyone know a way to enable an Apple Shortcut or, preferably, an Automation, that would automatically with no manual intervention, record the following in a Google Sheet or equivalent:

  • Depart a specific location and record “departure” plus location, time and date to the Google Sheet.

  • Arrive at specific location and record “arrive” plus location, time and date to the Google Sheet.

I’ve seen that there is a method to use Google Forms and that can pass data to a Google Sheet but was wondering if there was another way?

I’m not sure of the if the arrival/departure triggers are accurate or timely since I never used them. For Google Sheets entries, I use this method.

Thanks. Will check it out!

So I’m not sure how you can do it with Google Sheets, but if you’d be open to logging the info initially in Numbers then I would create a shortcut something like this

Then create personal automations which run immediately and without notification when you arrive and leave each of the locations you want to monitor - the automation should just ask to run the ‘log location’ shortcut. This example records the street address and the date/time of the event (note that if you select for it to record the full address you may find that the address covers more than 1 column and row). As you also want to record ‘Arrives’ or ‘Departs’, you’ll probably want to create 2 similar shortcuts, one of which adds Arrives and the other adds Departs to the data recorded.

You could then copy and paste the data from Numbers to Google Sheets.

Location triggers can be a little temperamental - I have one set for when I’m arriving home, and sometimes it triggers when I’m approaching my neighborhood but still on the main road, and other times it doesn’t trigger until I’m only 50yds from home - which may mean that the Get Current Location command could give you slightly differing addresses each time it runs.

Oh, and in this example I created a Numbers spreadsheet called Trip Logging, that had a sheet called Trips and a table called Logging - without that file existing with that sheet and table name the shortcut would fall over.

It does leave you with the Numbers app open and in the foreground - not sure how much of an issue that would be.

This may work. I’m not tied to Google Sheets - Apple Numbers would work fine. And I’m fine with all the variances that come with the location tracking capabilities of the iPhone. I am interested in capturing some data on my commute into and out of NYC for work and it seems that Shortcuts capabilities are at a point where I can actually do this without any human intervention.

You could probably just set your trigger location to the appropriate size and then rather than putting in Street Address, select to report the Zip Code - that should be detailed enough for you - you could probably even have it just log it as Work or Home based on what the Zip Code is.

Yeah, I use the ‘location’ item to trigger things now and it puts a radius around a specific location vs using a specific address.

So your Apple Numbers approach works except for one minor item. The “Current Time” item noted above passes both data and time together into one cell (i.e. ‘Apr 19, 2024 at 8:55PM’). I tried splitting your function up into two actions - one that had “Current Time” as just date and anther that has “Current Time” as just time, however they put those data points in the same “Column A” of the spreadsheet instead of “Column A” for ‘date’ and “Column B” for "time’. It does not seem that the shortcut options for Numbers has the ability to put the values in different columns. This is not a huge deal but curious if you have any thoughts on this minor item?

OK, so it you add the Current Date variable twice and format one of them just as the date and the other just as the time then it does put the info in separate cells

Thanks for putting out the challenge - I’m enjoying poking around and learning myself :nerd_face:

Interesting. I tried (before my last post and before you suggested) to add the 2nd “Current Date” variable yet Shortcuts was not playing nicely for me and could not figure it out. Will give it another shot.

Happy you are enjoying the ‘challenge’. :slight_smile:

My ‘end game’ with this exercise is to basically try to record how long it takes me to commute to NYC and back on the days I go into the office by recording arrival and departures from the radius around both my home and NYC office. And then, once I get those basics down, I want to get even more specific to detail items within the trip (each way), where I’d also flag/record sub-steps - when I arrive at train or bus depot, when I arrive/depart in NYC itself, etc.

Today, I use Shortcuts to send texts to my wife when I depart specific areas so she knows when I’m on way back home on days I’m in NYC - so the tracking items we are discussing would just be an extension of that.

Why I’m doing this? I’m curious of having the actual data around my commuting time.

Quick Follow Up: Figured it all out. Got the two date/time variables to feed into different cells. Also created duplicate Shortcuts that have an additional “Arrival” and “Departure” flag. And they are all feeding into a Numbers spreadsheet. So will give this all a test run next week and make any adjustments as needed. Should just turn all of this into an App Store app. :stuck_out_tongue:

1 Like

So the shortcuts and automations ran this AM with mixed results. When I had my iPhone open and ‘active’ everything worked fine. They ran and recorded the event in my Numbers spreadsheet.

However, if my phone was locked and I wasn’t actively using it, the Shortcuts failed. I seem to have the right settings in place - see screenshots below. Does anyone know how to ensure these run and record the timestamp in Numbers (I confirmed I have the app in open state on my iPhone) even if I’m not actively using my iPhone at the time the Shortcut triggers?

I’ve had similar issues trying to open my garage door opener app when I arrive in my neighborhood - if the iPhone isn’t unlocked at the time that it tries to run it fails - which kinda defeats the purpose of having an automation.

Exactly! What is the point? Completely defeats the purpose.

It was the same with sending texts via Shortcuts automagically although some recent changes to the app finally enabled them to be sent without having to manually confirm.

So silly.

So some folks are saying that you need to enable the ‘run automatically/immediately’ both in the individual shortcut (done) and in the Shortcuts section of the Settings app - however I’m not seeing that option within the Settings app.

I think I might have a privacy setting set that’s meant to minimize the impact of someone shoulder surfing your passcode then stealing your phone and that might have hidden the setting - if you look in Settings > Shortcuts do you see the option?

I’m not seeing that either.

The closest thing I see is an “Allow Running Scripts” option but that does not seem right.

Oh well. Will have to wait until Shortcuts updates this or I will write up some app requirements and find a Swift Dev. :joy:

I am late to this thread, but I have used this method to record an entry to a Google Sheet.

This isn’t directly related, but, I do have an automation to write an entry to a calendar when I arrive and leave certain locations. Those work without issue. I don’t have my phone open / unlocked and they work.

1 Like

Which category of Personal Automation should I use?

I would suggest arrive, and leave are the most obvious choices for arriving and leaving a location, but if the location has WiFi, maybe triggering through that would be an option too?