I Thought He Came With You is Robert Ellison’s blog about software, marketing, politics, photography and time lapse.

Export Google Fit Daily Steps to a Google Sheet

Updated on Saturday, December 8, 2018

Google Fit Daily Step Export

Google Fit is a great way to keep track of your daily step count without needing to carry a Fitbit or other dedicated tracker. It's not easy to get that data out though, as far as I can tell the only way is Google Takeout which is not made for automation. Luckily there is an API and you can do almost anything with Google Sheets.

If you're looking to export your step count this post has everything you need, just follow the instructions below to get your spreadsheet up and running. This is also a good primer on using OAuth2 with Google Apps Script and should be a decent starting point for a more complex Google Fit integration. If you have any questions or feedback please leave a comment below.

To get started you need a Google Sheet, an apps script project attached to the sheet and a Google API Project that will provide access to the Fitness API. That might sound intimidating but it should only take a few minutes to get everything up and running.

In Google Drive create a new spreadsheet and call it whatever you like. Rename the first tab to 'Steps'. Enter 'Date' in cell A1 and 'Steps' in cell B1. To grab history as well create another tab called 'History' with the same headers. Next select 'Script editor...' from the Tools menu which will open a new apps script project.

Give the apps script project a name and then select 'Libraries...' from the Resources menu. Next to 'Add a library' enter 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF ad click Add. This will find the Google OAuth2 library. Choose the most recent version (24 at the time of writing) and click Save. Then select 'Project properties' from the File menu and make a note of the Script ID (a long series of letters and numbers).

Open the Google API Console. Create a new project and name it something like 'Google Fit Sheet'. From the Dashboard click Enable APIs and Services and find and select the Fitness API. Then go to Keys and create an OAuth Client ID. You'll be asked to create a consent screen, the only field you need to enter is the product name (i.e. 'My Fit App'). Then choose Web Application as the application type. You need to set the name and the authorized redirect URL. The redirect URL is https://script.google.com/macros/d/{SCRIPTID}/usercallback replacing {SCRIPTID} with the actual Script ID you made a note of above. After adding this make a note of the Client ID and Client Secret.

Go back to the apps script project and paste the code below into the Code.gs window:

Right at the top of the code there are spaces to enter the Client ID and Client Secret from the API Console. Enter these and save the project.

Switch back to your Google Sheet and reload. After reloading there will be a Google Fit menu item. First select Authorize... You'll get a screen to authorize the script and then a sidebar with a link. Click the link to authorize the script to access your Google Fit data. You can then close the sidebar and select Get Steps for Yesterday from the Google Fit menu. You should see a new row added to the spreadsheet with yesterday's date and step count.

The final step is to automate pulling in the data. Go back to the apps script project and select Current project's triggers from the Edit menu. Add a trigger to run getSteps() as a time driven day timer - I recommend between 5 and 6am. You can also click notifications to add an email alert if anything goes wrong, like your Google Fit authorization expiring (in which case you just need to come back and authorize from the Google Fit menu again.

At this point you're all set. Every day the spreadsheet will automatically update with your step count from the day before. You can add charts, moving averages, export to other systems, pull in your weight or BMI, etc. I want to add a seven day moving average step count to this blog somewhere as a semi-public motivational tool... watch this space.

If you are looking to extend this sample to other data types then this API explorer page is very helpful for finding data types that the API documentation doesn't list.

Comments

Dan

This is great! Everything worked up until the end... I get the sidebar with the "Authorize" link, which I click and get a "sign in" page listing my google account name. I click ALLOW, only to receive a new page with just the following:

Google Apps Script

Authorization is required to perform that action

Also, is it correct that you put the header "Steps" in B2? Or should it be B1?

Finally, is there a way to make it go back and get a week of historical steps? I'm trying to pull the last week of step count, and the web UI and app don't seem to have a way to do this easily, so I was hoping your script would help.

Robert Ellison

Hi Dan - you probably need to authorize the script as well as the API access to your Google Fit data. If you can't get it working send a screenshot of where you're getting stuck to [email protected] and I'll see if I can figure it out.

Steps should be in B1. Thanks for pointing this out, I've fixed this in the instructions above.

When I set this up I copied historical data from the app. Click the menu and go to Timeline and at the top pick Day view and Steps. Should be easy to copy out the data this way. Alternatively in the code at the top of getSteps() there are two dates for the start and end of the period, both of which have a -1 to get to yesterday. You could change this to -2 to get the date before and so on.

Gustavo El Khoury Seoane

This is great! I'll be playing with Google Fit data on my spare time and this is super helpful. Thanks for sharing!

A quick thing: could you share the link to the Reference guide for the Google Fit API that you're using, in case we need to explore further? I couldn't find it in the Google Apps Script reference material

Rob

I'm having trouble at the same point as Dan. Everything works until clicking 'Authorize' in the sidebar in Google sheets - after clicking 'Allow' I get a page stating 'Authorization is required to perform that action.'

I should note that I don't have a GCP account - though that didn't stop me from authorizing the API.

I did note that there is a Google Sheets Add on called 'Fit Sync' that looks very similar and worked for me. I haven't had a chance to dig into the difference.

Rob

Robert Ellison

Gustavo, the API is linked to in the comment right above the getSteps() function in the code sample.

Robert Ellison

Rob, you do need the Google Fit API to be enabled in the API console. I'm not clear if you've done this or not from the mention of not having a GCP account. If you happen to come back to this and need help feel free to email me at [email protected] and let me know where you're getting stuck.

Josiah Vorst

Very helpful, works great for me. Thanks for taking time to post this!!

Rodney

Thank you so much for taking the time to write this up. Well done and I have my project up and running!

Xabier

Thank you!!

Vinay

This script works for me but when I try to get weight data it fails in getting response.

I am using below for weight

"dataTypeName": "com.google.weight.summary",

"derived:com.google.step_count.delta:com.google.android.gms:merge_weight"

On the response line I get "datasource not found error" error

Robert Ellison

Hi Vinay, I just experimented with this and I think you need to make two changes. For dataSourceId you need derived:com.google.weight:com.google.android.gms:merge_weight and then when reading the weight it's in fpVal rather than intVal in the JSON response. I got this working with a modified copy of my existing script (I'm just doing a weight version, not attempting to read weight and steps at the same time). Hope this helps, good luck!

AD

Hi There,

I'm a total idiot with programming but was able to follow your steps. Thanks so much!

Is there a way I can get historical data and also data for calories burnt?

Regards,

Andrew

Robert Ellison

Hi Andrew, for older data see the date logic at the top of getSteps (i.e. setDate(start.getDate()-1)). By changing the two -1 values to -2 you'd get the day before yesterday. You could pass a parameter into getSteps for the number of days ago and then loop through as much history as you need. For calories it looks like com.google.calories.expended is probably the right dataSourceId. See the previous couple of comments for a discussion of getting weight, this would be a similar adaptation.

AD

Hi Robert, thanks for the quick response.

I really want to have all historic data in a google sheet: specifically calories burnt per day, or activity level (steps or minutes) per day from back as early as 2015 when I started using the app.

When you say:

"You could pass a parameter into getSteps for the number of days ago and then loop through as much history as you need."

Could you be a bit more specific/step-by-step. This is my first ever project even opening up script editor.

Robert Ellison

Andrew, I'll try to provide some more sample code but probably won't have time until the weekend.

Robert Ellison

Andrew - I found some time today. The code above has been updated with a simple loop and menu item to load history. You need to add a History tab to the sheet with the same headers and then run the new menu item to load 30 days of step count data. If you need more then just change the numbers in the getHistory function. 1 and 30 pulls from 1 day ago to 30 days ago, you could change to 31 and 60 to grab the next 30 days of data. This is a quick hack that makes an API call for each day so you might run out of quota if you try to do too much in one day. Good luck, let me know if this helps.

Robert Ellison

Vinay, one more thing on weight. It looks like com.google.weight.summary will only return data if you recorded a weight measurement in the date range. It doesn't do anything sensible like assume your weight is the same until a new reading is entered. So need to handle this and ignore days without data.

Treasa Lynch

I disagree that Google Fit is a great way to keep count of steps now that they have changed the user interface. All of the above should not be necessary to get the data out. It is a basic requirement in a fitness app. If Google doesn't provide it as a straight forward piece of data when it is inflicting heart points which I don't want or need on me, then the app has ceased to be any use at all.

Robert Ellison

Vinay, the weight script worked for me the first time I ran it and then stopped. It looks like it should never have worked because it's missing a scope (fitness.body.read). Requesting that scope doesn't trigger re-authorization either so I added a method to clear the script property store. So, you should use the version below and run clearProps() from the script editor before authorizing again from the spreadsheet and then you should be good.

EMERSON OLIVEIRA RIZZATTI

Hi Robert,

I from Brasil and wanna thanks for taking time to post this!!

I need to get the distance (km) from my Workouts.

I tried to replace some lines in your code:

//permissions

.setScope('xxxx.googleapis.com/auth/fitness.activity.read xxxxx.googleapis.com/auth/fitness.location.read')

//request

var request = {

"aggregateBy": [{

"dataTypeName": "com.google.location.sample",

"dataSourceId": "derived:com.google.location.bounding_box:com.google.android.gms:aggregated"

}, {

"dataTypeName": "com.google.distance.delta"

}],

But received the error 400: "datasource not found"

I tried a lot of options to replace the "googleapis.com/fitness/v1/users/me/dataset:aggregate", but nothing work.

Can you help me?

Robert Ellison

Hi Emerson, You're nearly there. com.google.distance.delta is right for the dataTypeName, you need derived:com.google.distance.delta:com.google.android.gms:merge_distance_delta for the dataSourceId. Here's the full code for a working sample:

Jamie

Hi Robert,

Thanks for writeup and code, I was able to set it up and pull my steps. I see you helped Emerson out with a script to pull distance in the comments. I assume theres a way to combine these into a single script to pull both into the same spreadsheet? Also, what other data is accessible from the Fit API? Is there a way to pull duration (Move Minutes)?

Thanks,

Jamie

Robert Ellison

Hi Jamie. If you follow the link to the API Explorer at the end of the post and authenticate with your Google Account you'll get a list of all the data points that are available to you. This is how I figured out what to pull for distance and I'm sure duration is in there as well. I haven't tried pulling multiple data points at the same time yet. I'm sure it would work with consecutive API calls, you'd just need to make sure you have all the correct scopes when you authorize the API. I'm looking to get steps and weight at the same time - when I get around to this I'll post another code sample if I find a better way to do it.

Add Comment

All comments are moderated. Your email address is used to display a Gravatar and optionally for notification of new comments and to sign up for the newsletter.