Export Google Fit Daily Steps, Weight and Distance to a Google Sheet

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.

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.

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

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

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

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.

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

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

Thank you!!

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

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!

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

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.

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.

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

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.

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.

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.

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.

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?

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:

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

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.

Jamie and others, I've updated the code sample in the main post to handle multiple metrics at the same time - currently it pulls steps, weight and distance. You need to update the sheet to have the new tab name (Metrics instead of Steps). The history function is also better, it pulls the date range from Google Fit in a single API call rather than making one call per day.

The latest script Works great, but i have a hard time finding more data types.

Like my bike rides and or weight training workouts, which show up in the journal.

Any pointer would be appreciated.

Thanks for this great post.

Henk - look for the link to the API Explorer for Google Fit towards the end of the post. If you sign in with your Google Account you can list all of the data that's available to you. The documentation is atrocious, but the API Explorer is pretty helpful for figuring out what you need.

First of all, thank you for the script : )

I was wondering if there is a possibility to import archived data from google fit (365 days and more), and change distance to kms. Also, last 8 days were imported blank, with just a date, don't know what might be a reason for this

Dom - if you look at line 20 this pulls 60 days of history. You can change this to 365 (or more) to pull for a longer period.

I think the data from the API is in metric units. If not, you could convert in the spreadsheet or multiply by a conversion factor in the script before writing the data (i.e. at line 86).

Not sure why you'd get blank rows. This would happen if there is no data - assume that you see data for those days in the Google Fit app?

Hi there, I've made a mistake and deleted an oAuth Client and now nothing is working anymore, do you have any ideea how to fix this ?

I've created a new client, updated the codes however it doesn't work...

"

401. That’s an error.

Error: deleted_client

The OAuth client was deleted.

Request Details

That’s all we know.

"

Hi Gabriel, not something I've had to deal with before. I'd suggest starting from scratch - new spreadsheet, new Google API Console project - and see if this solves the problem. Something must still be linked somewhere.

Hello,

Thanks for the code. After the initial hiccups I was able to run this on google sheets.

Do you have an example for bucketByActivityType.

I am not able to figure what value should be put in for parameter activityDataSourceId of this object.

Many Thanks

Sachin Jain

Sachin - I have not experimented with activity type. Have you tried not specifying activityDataSourceId? It looks like that should return everything and then you could try filtering down from there. If that doesn't help check the API explorer link near the end of the post.

Thanks. That did the trick :).

I am still working on the code. Will post it here once it is finished.

Maximal amount of days to request is 90

I'll add a user promt to enter amount of days to load, and then load it all by 90-day chunks.

function showPrompt() {

var ui = SpreadsheetApp.getUi(); // Same variations.

var result = ui.prompt(

'Let\'s get old days!',

'Please enter number of days:',

ui.ButtonSet.OK_CANCEL);

// Process the user's response.

var button = result.getSelectedButton();

var text = result.getResponseText();

if (button == ui.Button.OK) {

// User clicked "OK".

n = parseInt(text)

if (n <= 90)

getMetricsForDays(1, n, 'History');

if (n > 90)

while(n>0)

{

if (n-90<0)

getMetricsForDays(1, n, 'History');

else

getMetricsForDays(n-89, n, 'History');

n-=90;

}

}

}

Hello,

Below bit of code fetches based on Month & activity & writes to the history tab the Start Date, End Date, Activity Name & Distance in kms.

It needs a sheet a called ActivityType which is basically a copy paste of all google fitactivity types.

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Google Fit')

.addItem('Authorize if needed (does nothing if already authorized)', 'showSidebar')

.addItem('Get Metrics Month Wise By Activity', 'getMetricsMonthWise')

.addItem('Reset Settings', 'clearProps')

.addToUi();

}

function getMetricsMonthWise(){

var today = new Date();

for(var month=0; month <= today.getMonth(); month++){

var startDate = new Date();

startDate.setDate(1);

startDate.setMonth(month);

startDate.setHours(0,0,0,0);

var endDate = new Date(startDate.getYear(),month + 1, 0 );

endDate.setHours(23,59,59,999);

getMetricsForDays(startDate, endDate, 'History');

}

}

function getMetricsForDays(start, end, tabName) {

Logger.log('Start Date' + start);

Logger.log('End Date' + end);

var fitService = getFitService();

var request = {

"aggregateBy": [

{

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

"dataSourceId": "derived:com.google.distance.delta:com.google.android.gms:merge_distance_delta"

}

],

"bucketByActivityType": { "minDurationMillis": 60000},

"startTimeMillis": start.getTime(),

"endTimeMillis": end.getTime()

};

var response = UrlFetchApp.fetch('URL', {

headers: {

Authorization: 'Bearer ' + fitService.getAccessToken()

},

'method' : 'post',

'contentType' : 'application/json',

'payload' : JSON.stringify(request, null, 2)

});

var json = JSON.parse(response.getContentText());

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName(tabName);

for(var b = 0; b < json.bucket.length; b++) {

var distance = -1;

if (json.bucket[b].dataset[0].point.length > 0) {

distance = json.bucket[b].dataset[0].point[0].value[0].fpVal/1000;

}

var activity = getActivityName(json.bucket[b].activity);

sheet.appendRow([start, end,

activity,

distance == -1 ? ' ' : distance]);

}

}

function getActivityName(activityCode) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName('ActivityType');

var startColumn = 2;

var startRow = 1;

for (var row_num = startRow; row_num < 120; row_num++) {

var cellValue = sheet.getRange(row_num, startColumn).getValue();

if (cellValue == activityCode) {

var activityName = sheet.getRange(row_num, startColumn-1).getValue();

return activityName;

}

}

return 'INVALID CODE:' + activityCode;

}

Hi Robert.

This is exactly what I was trying to figure out. I stopped and googled - thanks man, this is great!

Hi - Thanks much for the howto. I'm getting hung up at the authorization stage. It goes through such that I see I have given access (in the permissions page of my account) for the application to Google Docs, but NOT for Google Fit. Every time I try to do anything off the menu, it repeats the access process.

Any advice? Could the problem be trying to give the script access to fit data on a different google account than the spreadsheet?

Thanks for any help or pointers!

John, sorry for the slow reply. Have you tried disconnecting from Google Fit and then authorizing again? See the instructions near the end of the post. I'd also try running the Reset Settings menu option in the script as well before attempting to connect again.

Hi Robert - thanks for the reply. The app never registered with Fit, so I couldn't deauthorize there. Did try the reset settings. I've moved on to doing it in Python (easier data munging for me), but your post was quite helpful in learning the basics. Thanks!

This is amazing - Thanks Robert!

I have done all the steps you have outlined but get a 403 error message, containing "access not configured". The error messages reads:

"Request failed for {URL from Line 56 - which is th Fetch App URL} returned code 403. Truncated server response: { "error": { "errors": [ { "domain": "usageLimits", "reason": "accessNotConfigured", "message": "Access Not Configured. Fitness h... (use muteHttpExceptions option to examine full response).

The authentication came back with a success so not sure why is causing the missing access. Any idea how to solve this?

Thanks,

Matthias

Hi Matthias, that looks like a problem with the Google Fit API configuration. Check that you have copied the ClientID and ClientSecret over to the script correctly. Also go to the Google API Console and check that the Google Fit API is enabled.

Thanks for the quick reply Robert, really appreciate it. Problem was magically solved over night - I am assuming this may have something to do with oauth verification that can take a bit of time.

Again, thanks for this amazing article and your help!

Thank you! I got it working and it was neat. I got a little lost in the "Open the Google API Console" paragraph but worked it out and was able to retrieve my data and learn more about Google APIs in the process. I feel powerful! Thanks.

Thank you for the great job! Worked fine. Fantastic tips from the comments too.

This is exactly what I was looking for but I appear to be stumbling with either a newbie error or a change to the Google API Console ?

Trying to follow the instructions step by step and nothing seems to match after I've added the Fitness API?

The next step says to "go to Keys" has this been renamed to "Credentials"?

From there on I'm not able to find things like the application type, redirect URL and most importantly the Client ID and Client Secret.

Any help would be greatly appreciated.

Regards,

Darren

Darren, sorry for the slow response. Yes, click on Credentials and then look under OAuth 2.0 client IDs. If you have client listed click the name to go to a page that has the client ID and secret. If not, click Create credentials and pick the OAuth client ID option. Let me know if you're still having any problems.

Well done! I could follow this easily and get my spreadsheet up and running in 10 minutes. Kudo's to you!

Robert - this is a great guide - thanks for putting this together.

I am stuck on the redirect URL, though. When I enter it in the API console,

it gives me an error:

Error

Request contains an invalid argument.

Tracking Number: c3721854952865243

Send feedback

In the Google sheet, the Fit menu shows up, as does the sidebar. When I click activate on the sidebar, it takes me to a an error page:

Error: redirect_uri_mismatch

Any ideas?

Thanks!

KB

Kevin, the redirect URL looks like https://script.google.com/macros/d/{SCRIPTID}/usercallback. You need to replace {SCRIPTID} with the actual ID (select 'Project properties' from the File menu and find the Script ID in the spreadsheet script editor). If the script id is 1234 then the URL is https://script.google.com/macros/d/1234/usercallback

Aha! I had kept the brackets in there. Works beautifully now! Thanks.

All working now, thanks Robert. I'll continue to follow this article to see what others people do with it. Now to "gamify" my step counts and get moving. Enjoying coding doesn't generally encourage exercise.

Hello Robert, thank you for putting this together. I was preparing to transcribe 4 years' worth of weight data by hand and now I won't have to, once I tweak the code to import 1500 records or so.

Unfortunately, I am getting the exact same 403 error as Matthias. I hope it resolves overnight for me as well.

I keep thinking that my problem is with my API not being enabled. I'm not sure how to determine whether or not it is. When I click "Enable APIs and Services", I get a list of out-of-box APIs, with mine nowhere to be found. So if that is the problem, I am at a loss for how to correct it.

For now, I will just hope that it will resolve overnight for me, but in case it doesn't, some insight into enabling custom APIs would be awesome. Google's documentation was not helpful, nor was the rest or the internet.

Thanks again!

Agnes

Well. I mostly sorted it out. Turns out I hadn't enabled the Google fitness API. This is my first time working with APIs, and it's all so strange and magical. I've been able to pull today's data plus 80 days. 800 was too much for the software, so I'll figure out some way to programmatically import in chunks.

Thanks again!

This is great! Thanks to all who contributed.

I've been looking for the resting heart rate data point and haven't figured it out. Anyone willing to share that?

Thanks!

Mark

I'm getting this error:

TypeError: Cannot call method "appendRow" of null.

Any ideas?

Asier - it's failed to get an object for the sheet, so my guess is you didn't rename the tab to 'Metrics' - can you check this?

That was the problem.

I completely forgot about that.

Thank you very much,

Regards.

Hello Robert, I now have basic functionality with this sheet and it pulls the data I need. The only problem now is with the automation. It loses authentication daily, so I have never seen it run automatically. Any idea what might be going on?

Agnes - try following the steps in the second to last paragraph of the post to remove Google Fit access, reset and then authenticate again.

Thank you, Robert! I'm not sure why I hadn't noticed that instruction before, but it worked! Last night, data was automatically pulled for the first time.

Hello :) Thank you for posting all this, I was able to set it up. Is there a way to add sleep data as a column as well?

Chi, I don't currently track sleep so it's not something I can test. It is available via the API so should be possible to pull. Check the API explorer link in the article, this is pretty helpful for finding out what's available for your Google Fit account.

Hi

When I try to set up the OAuth consent screen, I'm forced to set up an 'external' one because I don't have a G-Suite subscription. Do I need a G-Suite subscription?

I've got further! Please ignore my last comment/question. I sorted it out when I realised the 'consent screen' had fields that seemed to be mandatory but weren't.

I am getting error 403 on my sheet though. I'll wait a day or two and see if it's just the authorisation pending, unless the error message copied below suggests to anyone that I need to tweak anything else? (thanks in advance for any help!)

request failed for ...googleapis... returned code 403. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "forbidden", "message": "datasource not found or not readable: derived:c... (use muteHttpExceptions option to examine full response).

Hi Gary, I had the same problem back in November. I didn't understand how the whole API thing worked, and hadn't enabled the Google fitness API. Once I had that sorted out, it all went well. I just had to make sure the sheet was authorizing properly so it wouldn't return an authorization error every day.

Thanks Agnes, Yes - I'm a rookie too!

The Google fitness API status is showing as enabled. However, the 'OAuth consent screen' status is still 'Verification status - Not published'.

The consent is set to 'external'. 'Internal' is only an option for Gsuite users, but it states that 'in this mode, your app is available to any user with a Google Account. External apps that request sensitive or restricted user data must first be verified by Google. This process may take 4–6 weeks. In addition, if you selected restricted scopes, you'll need to go through an independent security review, which can cost $15k–$75k.'

I'm not clear if I've set it up incorrectly so that Google thinks I'm collecting sensitive personal data! Or if the consent page will be published soon.

Hey, three others things I've been seeing if would make a difference:

1) 'Secret Manager API' is not enabled. 'Stores, manages, and secures access to application secrets. $0.03/10,000 count'

Not sure I need this, no mention above.

2)'Identity and Organisation' Not sure if I need to complete this...

It seems I'll need a corporate domain and company details.

3)'Policy troubleshooter. Enter the following fields to check if the API call will grant the principal access to a resource.'

Policy troubleshooter' [Principle member e...]

Resource permission pairs'

'Resource: [e.g.] ...265113

Permission [e.g.] Example: "...etIamPolicy"

Hey Gary,

All this GSuite stuff is stuff I never saw during my setup. I think you may have fallen down a rabbit hole at some point. Perhaps Robert has some insight?

Gary, I didn't touch the OAuth consent screen when I got this working. When I look for the project I'm using mine is set to External and everything works fine. For the project the only changes I made were to enable the Google Photos API and set the redirect URL to the script.google.com domain as described below. Given your error it might be that you don't have one of the items that the script is requesting - check the API explorer link near the end of the post to see if that helps. If not, then I'd suggest starting from scratch with a new sheet and a new API project and don't touch any settings relating to the consent screen as you work through setting it up.

Hi Robert,

I wanted to thank you for providing this resource and the detailed steps on how to set this up.

I have found information on the "sleep" metrics on the google fit API in an attempt to add the functionality to read sleep data.

The problem I am running into is that Google fit treats Sleep data as a "session" instead of a "dataset" (steps, distance and weight all fit into this catagory)

Would you be able to provide any insight on how I could possibly read this information? The request URL contains start times and end times which is not present on the ".setscope" URL's that read steps, distance and weight.

It is possible to access this information through the getfitservice/request variable or will this need another function added?

Quinn - I don't have a sleep tracker so it's going to be hard for me to try and figure that one out. If anyone else has managed this please chime in.

Hi Robert,

I've find reason of my 403 error:

"datasource not found or not readable: derived:com.google.weight:com.google.android.gms:merge_weight".

Look's like I have not weight data, right?

Why it's could happend? I've import weight data in Google Fit app from other one (Picooc) and in GF app I can see weight data. The same sitation with steps and distance butI do not user GF for then.

So, I confuse with it. Could you help me, pls. How to fix it.

Thank you.

Nadezda - most likely it's an authorization problem. Follow the steps above to remove authorization and then re-authorize. If this doesn't clear it check the API explorer link to make sure you can see weight data that way.

Robert, thank you for your work! I did everything according your explanation. When I try to get Metrics, I get red error message "Error: Access not granted or expired.Details". Did it several times but problem is stiil here. Thanx in advance.

regards, Alex

Hi Robert,

Recently, I keep getting a time-based error every morning. The Error is: Access not granted or expired. I do go in a re-authorize, and then have it Get Metrics and it works. But then the same issue happens the next morning. How do I get it to do what it was doing before? I went back to your instructions, but I still had the issue today.

Chi, Alex, not sure what could be wrong. Try removing auth from the Fit app and the spreadsheet and then reauth both. Alex, check the API is enabled as well. Chi, sometimes the auth screen gives you the option to limit access to a certain amount of time, could that be the issue?

Thank you one more time, Robert. The problem is solved somehow. :) Since Im getting constant errors in downloading Daily Summaries from Google Takeout yor script is very useful for me.

Thank you a lot for this!! I tried measuring the heart rate too, but I can not seem to find the data source that goes with com.google.heart_rate.bpm. From your code I just edited a few things:

var request = {

"aggregateBy": [

{

"dataTypeName": "com.google.step_count.delta",

"dataSourceId": "derived: com. google.step_count.delta:com. google. android.gms:estimated_steps"

},

{

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

"dataSourceId": "derived: com. google.distance.delta:com. google. android.gms:merge_distance_delta"

},

{

"dataTypeName": "com.google.heart_rate.bpm",

"dataSourceId": "derived: com. google.heart_rate.bpm:com. google. android.gms:{I have no idea what to put here}"

}

],

and I added some other things here:

var steps = -1;

var distance = -1;

var heart = -1;

if (json.bucket[b].dataset[0].point.length > 0) {

steps = json.bucket[b].dataset[0].point[0].value[0].intVal;

}

if (json.bucket[b].dataset[1].point.length > 0) {

distance = json.bucket[b].dataset[1].point[0].value[0].fpVal;

}

if (json.bucket[b].dataset[2].point.length > 0) {

distance = json.bucket[b].dataset[1].point[0].value[0].fpVal;

}

sheet.appendRow([bucketDate,

steps == -1 ? ' ' : steps,

distance == -1 ? ' ' : distance,

heart == -1 ? ' ' : heart]);

}

}

If I find anything I'll post another comment. I also wanted to help Mark, on november 11 he asked if anyone could share the code for measuring the heart rate.

This is a great tool, however the 60 minute token is troublesome.

I run these scripts as schedules, however the "Authorize if needed" which fixes the issue has to be run manually. Is there a way to modify this so I can run this without having the click the sidebar?

Hi James, you shouldn't need to run the authorize script on a schedule. This should be run once when you need to get permissions. If your authorization is expiring run through the steps above to reset everything and try again (reset the script, remove authorization from Google Fit).

Have question about following -

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.

This seems to be changed when I go to Consent screen in Google API Console. Its asking for more values than explained in above paragraph such as authorized domain, Application Homepage link , Application Privacy Policy link etc. Can you help?

Vishal, you shouldn't need to set any of that stuff up. You only need it if you're going to make a public facing app and need to get the consent screen approved by Google and you don't need that for this script.

Hi, I am having issues with the URL (only have a URI option). It states that "The request failed because one of the field of the resource is invalid." Is there any way to get around this?

Jack - the redirect URL? Make sure you haven't left the { } characters in. You want to replace that entire segment with the script ID from apps script.

Thanks Robert for the reply. When trying to activate it on google sheets, seems like it detects it as an unverified app. Is that normal?

Jack - yes, it will warn you if not approved by Google, safe to ignore.

Hi, seems like I am struggling when going back to the sheets. Keeps coming up with errors. I tried redoing all the steps again but am faced again with a new error :

TypeError: Cannot read property 'appendRow' of nullDetails

Any help is much appreciated!

Jack - that looks like it couldn't find the sheet. The script expects a sheet called Metrics for the daily update or History for the longer term pull. Make sure your spreadsheet is configured with the right sheets (or change the code if you want to call the sheets something else).

I've been running this script for about 6 months with minimal issues. I see multiple entries on 12th June having not messed with the scripts, has anyone else seen this?

Thanks Robert for all the help! Its getting there! It now works for me to pull just yesterday's data but the same error appears when I try to pull the past 60 days data. Would it be because I do not have yet 60 days worth of data?

On a separate note, one of the comments did try to write the script for heart rate, do you happen to have any updates regarding pulling both exercising Heart Rates as well as resting Heart Rates for this?

Your help is much appreciated!

Jack - great, glad to hear it. Probably the problem with 60 days is not having enough data, try setting a smaller window. I don't have anything on heart rate. If anyone manages this please post!

Darren - I haven't seen duplicate rows recently but I have seen it happen. Looks like Apps Script maybe does an unexpected retry in some circumstances? If it's a problem it would be possible to check the last row in the sheet to see if the date is a duplicate or not.

How can i add "com.google.body.fat.percentage.summary" ? I can't find the right dataSourceId

Dennis - try the API Explorer linked to in the post. It's pretty handy for finding the right IDs for your account. I don't have anything that provides body fat so can't check this directly.

Thanks so much for this, worked first time flawlessly. Now to import the data into Grafana.

Thank you again for writing up such a detailed explanation of steps that were so easy to follow.

I am new here！

Can I use this code and the AOuth2 get someone's google fit data with his authorization ?

Could you give me some suggestions,Please? Thank you very much！

Hi Momo - yes, if the user signs into the sheet with their account and authorizes it will read their data. I wrote this as a means to get your own data and there are some considerations if you share with someone else, for instance if you give them edit permission to the sheet they will be able to see your secret API key in the script. Generally OAuth is designed for this kind of flow where a user can delegate access to a trusted app. Depending on what you're trying to build Google Sheets might not be the best tool.

Hi.

This code works great. I deployed the code as a web app and am able to populate the History sheet when running the link in-cognito mode using same email to login as the email I used to make the sheet. I was curious to know if someone else uses this deployed link i.e. use some other email id for authorization. I tried that, it threw error Error: Access not granted or expired. (line 454, file "Service").

Can you help?

Hi Rajat, The script saves authentication for the signed in user so if you switch accounts I would imagine that the script authentication does not match the user authentication. You'd need to clear the script settings and authorize again with the new user account.

I got the app working without any issues. Both the 1 day and the 60 day options worked. Now after trying it multiple times, I see that the one day option works but the 60 day doesnt work even though they call the same parent function.

Hi Subham, I just tested the 60 day update and it worked fine for me. Can you try again in case it was a temporary error? If that doesn't help make sure you have the latest code (it's been updated a few times, not sure when you originally got this working) and try resetting and reauthorizing the script. If you're still not up and running can you post the exact error message you see when trying to update? Thanks.

Hi

I am getting this error while deploying as web app.

Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 154, file "Code")

I have added the below code

// This function successfully handles a GET request with safe and supported return type

function doGet() {

clearProps()

showSidebar()

getHistory()

return ContentService.createTextOutput('I just successfully handled your GET request.');

}

can you help here.

Rajat - the problem is that there is no spreadsheet app for a web service and so you can't use it to open a UI. You'll need to handle the OAuth flow using the UI service in the web app. I don't have an example here to share - if I ever do it this way I'll post back with sample code but for now I can't help you further with this.

Hi Robert

Thanks for the message.

I had another query. This .gs script uses the following sensitive scopes:

https://www.googleapis.com/auth/script.container.ui

https://www.googleapis.com/auth/script.external_request

https://www.googleapis.com/auth/spreadsheets

However, the published app, when used by a new user says unverified. I guess, the app has to be verified by google. Is there any workaround to this or this is the only method?

Rajat - I think it depends on how many people you want to make this available to. If it's a personal project you're probably OK. If it's a product you're making generally available then you probably need the Google verification. I know they have been locking down apps script more and more.

Hello Robert,

Thank you for taking the time to show us how you can get the script you wrote working.

It works just great for me. I'd love to take things to the next level and record daily heart points. Steps are good but I'd really like to capture heart point data as well and share it with a group of friends.

I hope you can find the time to update your script to capture this data.

Thanks in advance.

Hello Robert,

thank you for your work.

I would like to ask you some help.

I followed your guide, authorization ok on the google sheets from the "Google Fit" menu.

But when i click on "get metrics from yesterday" i get back this error:

"Exception: Request failed for https://www.googleapis.com returned code 403. Truncated server response: { "error": { "code": 403, "message": "datasource not found or not readable: derived:com.google.distance.delta:com.google.android.gms:merg... (use muteHttpExceptions option to examine full response)"

My "Fitness API" is enabled and on the dashboard tells me 8 request with 100% error.

Client ID and Secret are right in the script, i double checked and I tried to copy past them again.

On the "OAuth consent screen" i just set the name "My Fit App"

My "OAuth 2.0 client" name is "Fit1" but I don't think this is related to the problem.

What am I missing?

Could you help me?

Thank you in advance

Sandy, it sounds like you don't have Google Fit authorized or it's in a bad state. Follow the instructions in the second to last paragraph of the post to reset and see if that fixes the problem.

John, heart points would be a great add. I'll look at it the next time I need to update the script. If you want to take a crack at it use the API Explorer linked above to find the dataTypeName and dataSourceId to add to the list of metrics being fetched.

Robert, Thanks for pointing me in the right direction.

I found the dataTypeName here: https://developers.google.com/fit/datatypes/activity#heart_points

It's com.google.heart_minutes right?

I'll study your script to figure out how dataSourceId is used for the other metrics.

Hello Robert,

so I disconnected the script from google fit.

After on google sheets, from the google fit menu i did "reset settings", re authorized again from the link, authorization ok.

When I do "get metrics" i get the same error.

I really don't know what to do.

Could be that I'm running the script from my browser on my Pc?

Because on the google sheets app from my smartphone i have no menu, i did everything from my pc.

Am I missing something?

On my smartphone tho I installed google sheets app and cloud console.

Thank you for your patience

Hi Sandy, sorry that didn't work. I can think of two possibilities. The first is that you didn't authorize all the required scopes - seems unlikely but you could revoke access again and then check the consent screen carefully to make sure that all the scopes are granted. The other reason could be that your fit account doesn't have distance in it (that looks like the metric with the error). You could use the API Explorer to see which metrics are available. If I think of anything else I'll post back.

Hi,

thank you for the quick answer.

Yea, i have no distance in it.

I'm only interested to monitor my weight.

What should I do?

Thanks again

Sandy - delete the step and distance related lines from the script - you'd need to do this in the request, where the values are parsed and then finally where they are written to the spreadsheet.

I removed this from the script:

"{

"dataTypeName": "com.google.step_count.delta",

"dataSourceId": "derived:com.google.step_count.delta:com.google.android.gms:estimated_steps"

},

,

{

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

"dataSourceId": "derived:com.google.distance.delta:com.google.android.gms:merge_distance_delta"

}

var steps = -1;

var distance = -1;

if (json.bucket[b].dataset[0].point.length > 0) {

steps = json.bucket[b].dataset[0].point[0].value[0].intVal;

}

if (json.bucket[b].dataset[2].point.length > 0) {

distance = json.bucket[b].dataset[2].point[0].value[0].fpVal;

}

steps == -1 ? ' ' : steps,

,

distance == -1 ? ' ' : distance".

Then i removed the column B and D on the spreadsheet, in both sheet "Metrics" and "History".

So now i have, on both sheets, "Date" in A1 and "Weight" in B1. Nothing else.

New error: "TypeError: Cannot read property 'point' of undefined".

What did I do wrong?

I'm really sorry if I did something wrong, I'm not used to do script :( .

thank you again

Sandy, sounds like you are nearly there. It's just returning one dataset now so change dataset[1] to dataset[0] in the remaining code that reads the weight value (two places to change).

It's working.

Thank you Robert.

You are the best!

Thanks, it is working as expected!

Just wanted to know if it is possible to fetch all the weights registered within a single day as well? Right now, only the daily average is fetched.

Hi burningvortex99 - glad that it's working for you. I haven't tried to get more granularity on weight. The script at the moment requests the data for one day - you could reduce the interval and get the data per hour (or whatever you need).

Cool, thanks Robert!

Will try and see if I can reduce the interval.

Hi Robert,

Thanks for providing this info. I'm stuck on this paragraph:

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.

Specifically this sentence "Then choose Web Application as the application type. You need to set the name and the authorized redirect URL."

I don't see an option anywhere to choose Web Application and to set the redirect URL.

Thanks for any help!

John

Hi, Robert!! Thank you for great work!!

1) I want to load data from history for the last 160 days.

When I try to specify more than 100 days, I get an error. I tried to take advantage of the comment by Nexis (on Thursday, April 18 2019) but I also get the error.

getMetricsForDays (, n, 'History'); where H <0.

2) And also, after setting up the trigger, I receive the error "Error: Access not granted or expired."

Thanks for you work!

Alexander

Hello, Robert! i solved the first problem. Added the following lines:

function getHistory() {

getMetricsForDays(120, 140, 'History');

getMetricsForDays(60, 120, 'History');

getMetricsForDays(1, 60, 'History');

But the second problem remained.

Thanks for you work!

Alexander

Hi Alexander, glad you figured out the history. For the authentication problem please follow the instructions in the second to last paragraph of the post to reset your credentials and try again.

John - From your project in APIs and Services (Google Developer Console) click Credentials in the left menu, then the edit icon next to your OAuth2.0 Client, you can add the redirect URI at the bottom of that screen.

Thank you for putting this together. I worked through all of the authorization issues, but now I'm getting "TypeError: Cannot read property 'appendRow' of null". I tried outputting the json to the Logger and it seems to be blank. Am I missing some required config? I'm using your code and the only thing I've updated is the Client ID and Secret.

Thanks again,

-Mike

Mike, sounds like it's not loading the sheet. It expects the sheet to be called Metrics. If it has a different name then this is the error I'd expect.

Ah that was it...thanks!

Hello,

I am also getting the "Cannot read property 'appendRow' of null" when trying to pull data from the past 60 days. Pulling data from today works fine. Does this function not work or does this script only have the ability to pull data from the day it is authorized?

Thank you!

Google have sent out an email referring to changes in the Google Fit API. Will these changes require any update to the code above?

Kai - Could be the same problem? The history version expects a sheet called History so you'll see the same error if the sheet doesn't exist.

Darren - I have seen the email but not had a chance to check it in detail. The current code uses read scopes so that should be OK. I don't know what the changes to the sensitivity mean for the OAuth flow yet. If there is a breaking change I will try to fix it.

Gotcha, didn't realize it expected a "history"sheet. Must've missed it. Thanks you for the help!

A quick note on the 'Updates to Google Fit's Developer Policy and APIs' email. After reviewing the requirements for sensitive scopes there is an exception for personal use:

Personal Use: The app is not shared with anyone else or will be used by fewer than 100 users (all of whom are known personally to you). Note that your app will be subject to the unverified app screen and the 100-user cap will be in effect.

So using this script to export your own Google Fit data will continue to be allowed without any Google review. If you have used this script to create a service that other people use then you probably do need to be prepared to have Google review your implementation.

Hi Robert,

First of all thank you for this script which will allow me to be able to correctly follow my activity.

Unfortunately, I get stuck on the error message "Error: Access not granted or expired.".

I tried the different information that I read in the comments in addition to those in your article.

I started from the beginning 3 times and still have the same problem. My API is activated, access is authorized, in short I do not understand.

Thank you in advance.

Benoît (sorry for my english, i'm french)

Hi Robert,

You can delete my previous message if you want because after several attempts, I deleted everything, turn off the pc and I only got back to it this evening.

I no longer have an error message now (I don't know what was before).

However, I have a new problem: the dates increment well, but not the steps or the distance (I deleted the information relating to the weight because it is not present in my Fit account).

Sincerely sorry to bother you, but I don't understand a thing. In my API dashboard I have no more errors now but the data does not seem to recover.

Thanks in advance if you have a solution.

Benoît

Benoît - my best suggestion would be to go back to the original code including weight and see if you still have a problem. Could be that something broke when you removed that section.

Thanks for your response Robert.

Unfortunately I tested (manually adding weight data in my Google Fit App so there is something to retrieve) but that doesn't change my problem, I still end up with the date without any other data.

I will continue to seek a solution.

Thanks for this work.

Benoît

Hello Robert,

Sorry for all my messages.

I think the problem was with Google Fit.

Indeed, I retrieved the data from my FitBit bracelet via the FitToFit application to Google Fit.

Having some synchronization problems with this application, I switched this morning to another application allowing me to synchronize my FitBit with Google Fit.

So I deleted all the data from my Google account and restarted a synchronization with this new application.

Since the steps (the weight and the distance are not in my Google Fit) are well recovered.

I will now see to retrieve the sleep data.

Thank you very much for your script and your assistance.

Have a good day.

Benoît

Did you manage to get the sleep data?

Massive thanks for this tutorial, I've been trying to get it to work for 3 days and finally managed it by using Google Chrome and going through each step very slowly.

I've outputted the information through Grafana as well as Strava info, my email was sent along with this comment if you are ever interested to see an output.

Thanks again for the work you put in.

"Then choose Web Application as the application type. You need to set the name and the authorized redirect URL."

Where do I do that?

Hi!

i have 2 problems. I followed the procedure as described, copied the code and activated the hourly routine but I get an unexpected result:

I get the result of two days ago and not "yesterday".

If I proceed manually instead I get the desired result.

In both cases I get "calories" only a part of them after 24 hours. and all those actually consumed after 48 hours....

could you help me?

Francesco - I haven't tried to grab calories so I don't have any experience with how this data source behaves. Does other data look right? Also see this article.

This is brilliant - another thank you from me your hard work!

I'm also wanting to get resting heart rate so I'll keep checking back to see if one of you clever people work it out!

I could have messed up somewhere, but I am getting an error saying "Property store is required".

Hi Jordan. I haven't seen that error but searching for it I see some suggestion that it could be due to V8 runtime. In the apps script see if you can disable that.

Super convenient steps!!!

Thanks!!

Robert--to parrot many here, thank you for your hard work. I had one hiccup where I had to add my own email address as a 'test user' before I could authenticate but that could have been due to being signed in to two Google accounts within Chrome while I was working or another step I may not have followed to the letter. In no time at all, I worked through that and populated all my data.

I'm trying to accomplish a few things and am running into some issues. (1) I'd like to split DATETIME into two separate columns DATE and TIME [does the API allow for that?] (2) I'd like to return weight in lb as opposed to kg. In Sheets I can '=CONVERT(C2,"kg","lbm")' but was wondering if I can have the script return that without converting after the fact? (3) Finally, I'd like the script to grab the 'latest' measurements (i.e. day 0) so that if I weighed in this morning and then viewed the sheet at lunch I can see 'today's' numbers. I can easily change the trigger timing, but not sure how to adjust the script to just call yesterday vs. "latest".

Your awesome walk-through has allowed me to chart blood ketones (mmol/L) against daily weight from my WiFi enabled scale and have proved immensely helpful in achieving my health goals. Thank you again!

Hi, Glad to hear you're finding this useful.

As written the script pulls a full day of data so I'm not sure how useful the date vs time thing is. The date passed to the sheet has a set of properties like getHours() the should allow you to break it down in any way you need.

Converting weight is easy - before writing to the sheet add:

weight = weight * 2.205;

To get data for today change the two 1s to 0s in the call to getMetricsForDays() near the top of the script. The code will create a new row each time to call it so you could do this several times a day to get updates or you could delete the last row if it has the same date and then add the latest data depending on what you're trying to achieve.

Hey Robert--I was able to parse out date from datetime and also make the kg to lb conversion pretty easily. Getting data for day 0 is where i was stumped but your comment helped me fix that, as well. I appreciate your time, sir! Thanks again.

I've followed all the steps and encountered no problems. I've authorised the sheet/script to pull my Google Fit data but when I do via the 'Get metrics' option in the menu on sheets, the script inputs a date under the 'Date' column but then there's no data under Steps, Weight or Distance.

Any ideas?

Looking in Google Fit, I can see the data is there, but it just isn't pulling.

Alex, try following the steps to revoke access and reset the script. If that doesn't help then post back.

That's worked. Thanks Robert!

I didn't need distance, and instead wanted calories burnt so I have adapted the script to the below. However, it isn't pulling calories burnt. it is still getting step count and weight though.

var request = {

"aggregateBy": [

{

"dataTypeName": "com.google.step_count.delta",

"dataSourceId": "derived:com.google.step_count.delta:com.google.android.gms:estimated_steps"

},

{

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

"dataSourceId": "derived:com.google.weight:com.google.android.gms:merge_weight"

},

{

"dataTypeName": "com.google.calories.expended",

"dataSourceId": "derived:com.google.calories.expended:com.google.android.gms:from_activities"

}

Any ideas? THanks.

Alex, you need to add the scope for calories to the .setScope call. Use the API explorer to find this. You will probably need to reauthorize the script again after adding the scope.

I was having trouble and getting an Error 403 message when I tried to authorize. To fix the problem make sure you add your google account as a test user on the OAuth Consent screen.

There was an earlier question about converting weight from kg to lb in the script. I see that I can add weight = weight * 2.205; But I'm unsure where to add that.

commenting datasourceid worked for me for getting heart rate data.

{

"dataTypeName": "com.google.heart_rate.bpm"

///"dataSourceId": "derived:com.google.heart_rate.bpm:com.google.android.gms:heart_rate_bpm"

}

This is awesome! Thank you!

I was planning to improve my spanish but guess I'll figure out how to add more data - I'm not a programmer.

I had previously spent so much time trying to get google sheet data from google fit. It's way harder than I would have expected.

Do you have a donation page?

Thanks so much for this, it had been really helpful and worked perfectly for about two weeks

Now, I keep getting failure notifications every couple of days and have to go in and reauthorize the Google sheet's API access. It started June 1st. Has something changed on Google's side somewhere?

Hi Jim, nothing I'm aware of. I'd suggest revoking access from Google Fit, resetting the script and then reauthorizing (see instructions in post). Let me know if you're still having trouble after that.

Is this possible to implement this with multiple users? Having all their data displayed in separate rows?

James, you'd need to manage OAuth for each user which is going to be fairly complex. I'd approach this by having a sheet for each user and then as a separate process merge everything together into another sheet.

I am currently doing a study which monitors the steps of a group of diabetes patients. I would love to have all of their info into one spreadsheet. I already authorized my other google account via the option in the sheet by just logging in. Then I come to see 2 users in the oath user cap now. However I can only see data from the first user authorized. Do you have any repository to an idea like this ?

James, the current script will store one set of credentials at a time. It's probably possible to create a multi-user version, or a multi-sheet implementation of what you're trying to do. It's not something I have time to do with this script though.

This is great thank you.

I'm currently building a project (a remote control that only let's you watch YouTube if you've done enough daily steps :-) ) ... that needs the CURRENT DAILY STEP TOTAL.

Is there anyway for it to pull the steps say every 10 minutes (updating a cell rather than adding new lines)? I'm new to this, so have got it working, but don't know how to amend.

I'll make sure I share the finished project with you and link back here!

Many thanks. Sarah

Hi Sarah, should be pretty easy. Instead of append row make a call like:

SpreadsheetApp.getActiveSheet().getRange('A1').setValue(steps);

Where A1 is the target cell. This will update rather than append. Sounds like a fun project. Good luck!

Thank you for your super quick response.

That works perfectly.

However is there any way to have it update more often rather than just once a day? Ideally once every 10 minutes?

Many thanks, Sarah

Change the 1's in getMetricsForDays(1, 1, 'Metrics'); to 0's - that should pull today rather than yesterday. If this works change the script trigger to run more frequently.

Hi Robert,

this is super useful, thank you!

Could you please help me just replace the the average weight with the minimum value for weight?I cannot seem to find that anywhere..

Thanks,

Ondra

Hi Ondra, See https://developers.google.com/fit/datatypes/aggregate - it looks like weight has average, max and min. Try changing value[0] to value[2] when fetching the weight. The script is pulling data daily, if there isn't more than one measurement per day I'd imagine that all three would be the same.

Ahh, that's where:) I saw there were three values, but could not find where to change it in the script. Thanks again!

Hello Robert,

Thank you very much for all the help you provided.

I'm really dumb and I can't implement more data to get.

I took a look at the API Explorer link, but I didn't understand anything.

Is there any way to get all the data and put it in the columns, something more generic?

Thank you again

Gustavo, I can't help much more as I don't have a lot else in my account. The trick is to look for the data source name and ID, and to check if you need any additional OAuth scopes.

for people who are getting the "403. Truncated server response: { "error": { "code": 403, "message": "datasource not found or not readable" error- the issue for me was that I didn't have data for all of the data types the script looks for in my Google Fit account (hence it not finding it...). The way to solve it was to remove the bits about the data types I didn't need, and leave in the bits I do want to keep

Cheers!

Hi Robert, me again:)

I adapted the script so that I have to possibility to pull more days at once (as sometimes I do not have the time to to it every day) - like this:

getMetricsForDays(0, 3, 'Metrics'),

I added more functions like this so that I can choose how many days I want to pull (you get the picture, I hope).

Up till yesterday, it was working with no problems, but since yesterday, when I pull more days than one, I get the dates in a strange format. The first day is displayed as before, but second and all subsequent dates get displayed as "day-1 23:00:00"; plus it now adds one more row, which has only date "today 23:00:00" with no other values...

Is there any issue in the script, or could it be a change in the fit API?

Hi Ondra. I don't think it's the script. I just tested pulling 60 days and got reasonable dates. Could be something in your sheet - check the format for the column.

I'd also suggest setting up a trigger, see the instructions in the post. That way you don't need to remember to use the menu, it will just pull in the last day every day.

Hi Rob, thanks for the quick reply.

I also noticed that I have different-yet-similar issues with my other google fit connections, which together with the time they emerged suggests they might be tied to the change to daylight saving time.

I will definitely try to set the trigger (which I do not remeber being here when I first got the script from you, by the way, thanks!)

I also had that same date issue, here's an except of my date log

11/6/2021

11/7/2021

11/7/2021 23:00:00

11/8/2021 23:00:00

11/9/2021 23:00:00

Daylight saving change DID indeed occur on Nov 7, 2021

Before that date, my numbers of steps matched the one I had in the Google fit App, after that date, those numbers won't match.

(oh BTW, Robert, thank you so much for that scriptin the first place)

Really useful article. I've been tearing my hair out for the past few days trying to expand this to include sleep data, but no luck. It'd be very interesting to see whether anyone cracks this in the end, I notice a few other commenters have similar ambitions.

First this is incredible and I'm happy I was able to follow along to get it working, though I have one thing that I am so stuck on.

I for the life of me cannot figure out what exactly is being outputted under "Distance".

An example, for yesterday the Distance walked on Fit was 1.04km. However the output that I get is "2142.894508" which I don't even know what I'm to do with that piece of information that doesn't tie to any distance that I know of.

According to the documentation distance is in meters. Not sure why you're seeing such a big discrepancy between Fit and the API here. Do steps line up for the same period?

thanks for the amazing work.

I am getting this problem

Exception: Request failed for https://www.googleapis.com returned code 403. Truncated server response: { "error": { "code": 403, "message": "datasource not found or not readable: derived:com.google.weight:com.google.android.gms:merge_weight.

thank you.

Jojan - 403 is forbidden so check that you have granted access correctly and also try following the instructions to fully remove access and then authorize again.

thanks for reply Robert,

seems like there was problem with weight or such, i tried removing the code form appscript for weight and it worked fine.

Hi Robert -

This is a "problem solved", but it might help someone else if they make the same mistake.

Thank you for your patience debugging newbie's efforts. This is my first try at an app or an api. It looked promising, but I get an Error 403: The developer hasn’t given you access to this app. (No one else seems to have gotten this particular one yet.) I've double-checked the url doesn't have curly brackets.

When I test the app function by function, it's getting stuck on getMetricsForDays. Error: Access not granted or expired. (line 466, file "Service". I gather that's a google file.

I figure this should work even in test mode (since I didn't plan to hand it over to anyone else). I tried adding myself as a Test User (since I hadn't published the app) and then I tried Publishing (without review). And ... it presto!

So the missing step (for me) was "publish MyFitApp on the OAuth consent screen".

Thanks for helping me get my head around this environment. I love being able to manipulate my own data finally!

Cheers -

Renee

Hola Robert,

Soy de Perú, no se ingles y es la primera vez que intento hacer algo con la api, estoy estancado en esta parte:

"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')."

Despues de encontrar el Fitness API no encuentro la llave donde crear el cliente ID, tal vez sea porque desde que publicaste la api hasta la fecha a cambiado la interfaz, espero puedas ayudarme ya que lo que intento hacer es algo parecido pero con las horas de sueño.

Gracias por la ayuda de antemano y gracias nuevamente por ayudarnos a todos aquellos que tenemos la inquietud por la programación aun que no tengamos los estudios.

Saludos desde Perú.

Hola Antonio, estoy usando Google Translate, así que perdone cualquier error. Sigo viendo la misma pantalla. Vaya a Google Cloud Console y seleccione su proyecto cerca de la parte superior izquierda. Desde la pantalla principal, seleccione API y servicios y luego Pantalla de consentimiento de Oauth y complete todo lo que necesite. A menos que la interfaz sea diferente en Perú, no estoy seguro de qué más podría estar pasando.

Hi Robert, gracias por responder tan pronto;

Si pude configurar la ID del cliente, ahora el error que tengo es el que casi todos tienen:

"Excepción: No se pudo completar la solicitud para el código devuelto 400 de https://www.googleapos.com. Respuesta del servidor truncada:{"error":{"message":"Requiere ay least one... (usar la opción muteHttpExceptions para examinar toda la respuesta)."

Creo que el error se debe a qué yo no tengo datos de pasos ni peso y ya que no guardo datos me da error, la pregunta es se puede controlar dicho error?. Lo otro es que yo quiero sacar datos de sueño y que uso mi band de Xiaomi con el aplicativo Zeep Life al cuál he conectado con Google Fit, dicho esto los dos aplicativos se sincronizan perfectamente; pero quiero probar si puedo sacarlo los datos a el Google sheets. Espero puedas entenderme, gracias.

Tendrá que usar API Explorer para limitar la solicitud a los datos que están disponibles, o probar con una cuenta que tenga los campos que se solicitan.

Hi Robert and all! Great tool, thx a lot!

How can I make the script to overwrite the results of the previous day on the Metrics tab? I would like to have only one row with the latest result.

Call sheet.clear(); right before sheet.appendRow(...);.

If you use sheet.clear(), headers will be removed too, so you would prefer clearing only content after the second line.

I found a good way for that at https://stackoverflow.com/questions/71494974/how-to-clear-values-from-range-keeping-headers-in-google-sheet-script

And if your want to format distance as Kilometers, change "distance = json.bucket[b].dataset[2].point[0].value[0].fpVal;" to "distance = (json.bucket[b].dataset[2].point[0].value[0].fpVal) / 1000;"

Hi Robert,

I haven't seen anybody conquer the body fat percentage code.

In the API explorer there is no Data Source ID for the data name. It returns:

{

"dataSource": []

}

Where do you go from here?

I don't have this so no way to debug it. I think you'll probably need to post this in the Google forums and see if anyone there can help. Please post back if you find out.

I'm not tech savvy and most blogs make my eyes roll over. Thanks for writing this up as it was super easy to follow even with Google changing the names of some of the functions you used. After going back and forth with the API bit, I got it working easily. Then I ran into the 90 day max history thing and found the workaround in the comments.

Thanks a bunch! I've been wanting to graph my weight over the past few years but Google Fit's app only shows you calendar years. This worked great.

Thank you for good information. Thanks for completing all the steps.

However, when I try to load yesterday's data or load data from 60 days ago, "Error: Access not granted or expired." appears and no data is entered. How can I solve this?

Yubin - follow the instructions near the end of the post for resetting authorization.

I've added new lines on my Metrics and Historic tabs to get data for 2023, but it is not working. I've already reset settings and authorized it again, but I can't get new data there... not sure what else I should do.

Wow. I just want to, like, click on “Export”, pick a common file format, and tell it a destination, to get my data….Oh! It’s not _my_ data! It’s data I _gave_ them! Dopey my reasonably expectious me be!

Hi Robert,

Thank you so much for putting this together! I do not program in Java, and have been wanting to extract some of my data from Google Fit that doesn't necessarily come with the data when doing a Google Takeout, so I really appreciate you putting together this post, it really was easy getting it up and running.

I was able to modify your script to also bring in the aggregated calories expended as well as the aggregated heart points. One data type I'd like to bring in, but it's not working is move minutes. I'd like to bring in the total move minutes from a day.

According to the API documentation (https://developers.google.com/fit/datatypes/aggregate), the data type and field of aggregated move minutes is the same as the instantaneous data type (https://developers.google.com/fit/datatypes/activity#move_minutes).

So long as I have the right dataTypeName and dataSourceId (I do, I found them here: https://stackoverflow.com/questions/41173213/getting-active-time-from-google-fit-rest-api), I should be able to bring in this data just like I did with calories and heart points. Unfortunately, something is wrong and it doesn't bring any move minute data into the google spreadsheet. I get all the other data, but not move minutes. The OAuth permission scope is the same for other activity data types: https://www.googleapis.com/auth/fitness.activity.read.

I've added in the following to the var request:

{

"dataTypeName": "com.google.calories.expended",

"dataSourceId": "derived:com.google.calories.expended:com.google.android.gms:merge_calories_expended"

},

{

"dataTypeName" : "com.google.heart_minutes",

"dataSourceId" : "derived:com.google.heart_minutes:com.google.android.gms:merge_heart_minutes"

},

{

"dataTypeName": "com.google.active_minutes",

"dataSourceId": "derived:com.google.active_minutes:com.google.android.gms:merge_active_minutes"

}

I've also made these changes in the for loop:

var steps = -1;

var weight = -1;

var distance = -1;

var calories = -1;

var heartmin = -1;

var movemin = -1;

if (json.bucket[b].dataset[0].point.length > 0) {

steps = json.bucket[b].dataset[0].point[0].value[0].intVal;

}

if (json.bucket[b].dataset[1].point.length > 0) {

weight = json.bucket[b].dataset[1].point[0].value[0].fpVal;

}

if (json.bucket[b].dataset[2].point.length > 0) {

distance = json.bucket[b].dataset[2].point[0].value[0].fpVal;

}

if (json.bucket[b].dataset[3].point.length > 0) {

calories = json.bucket[b].dataset[3].point[0].value[0].fpVal;

}

if (json.bucket[b].dataset[4].point.length > 0) {

heartmin = json.bucket[b].dataset[4].point[0].value[0].fpVal;

}

if (json.bucket[b].dataset[5].point.length > 0) {

movemin = json.bucket[b].dataset[5].point[0].value[0].fpVal;

}

sheet.appendRow([bucketDate,

steps == -1 ? ' ' : steps,

weight == -1 ? ' ' : weight,

distance == -1 ? ' ' : distance,

calories == -1 ? ' ' : calories,

heartmin == -1 ? ' ' : heartmin,

movemin == -1 ? ' ' : movemin ]);

}

As mentioned before, all the other data, steps, weight, distance, calories, and heart minutes are pulled into the spreadsheet, just the move minutes are not brought in. I'm not sure what my mistake is.

Thanks!,

Diedrich

Hi Diedrich, I'd start by examining the JSON response. Are you getting anything at all for move minutes? Maybe it's some small difference in how it needs to be parsed.

Hi Robert,

Thanks for your reply. I'm not sure how to check the JSON response, I am not that familiar with Java scripting.

The strange thing is that I get nothing for move minutes. I also don't get any error, either. But, what has changed is that I need to re-authorize the script each day, whereas before adding in the move minutes changes, I didn't need to.

The stackoverflow link I provided in the previous comment claims that it works for that person.

Since I'm not familiar with this type of scripting, I am not sure where to begin debugging it.

Thanks!,

Diedrich

Hi Robert and others interested,

I figured out what the problem was. It was getting the data, but it wasn't being treated correctly. In the function getMetricsForDays, there's a for loop where it looks like there's commands to assign numerical data types. In the case of move minutes, it's an integer value, and I was assigning it a floating point value, fpValue, and it should be intVal, just like the number of steps. After changing this, it worked just fine.

Thanks!,

Diedrich