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

Updated on Sunday, October 18, 2020

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, weight and distance 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 'Metrics'. Enter 'Date' in cell A1, 'Steps' in B1, 'Weight' in C1 and 'Distance' in D1. 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 and 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 Metrics for Yesterday from the Google Fit menu. You should see a new row added to the spreadsheet with yesterday's date and fitness data.

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 getMetrics() 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.

Note that weight will be blank in the spreadsheet for days with no weight data. Google Fit doesn't return the last known weight, only the known value for days where an update was recorded.

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.

A couple of times working on this script I got my authorization in a bad state and started getting a 400 error response from the API. If this happens run your Google Fit app, click the Profile icon at the bottom and then the Settings icon at the top right. Click Manage connected apps and then disconnect the script from Google Fit. Finally run the Reset Settings option from the menu in the sheet and then authorize again.

I updated this post on Jan 21, 2019 to extend the sample to handle weight and distance as well as steps. I also improved the history function to handle many days in one API call rather than a quick hack I added earlier that pulled a day at a time. I'd recommend using the code above rather than anything included in comments below (at least comments before this update).

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.

Robert Ellison

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.

Henk Wieland

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.

Robert Ellison

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.

dom

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

Robert Ellison

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?

Gabriel

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.

"

Robert Ellison

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.

Sachin Jain

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

Robert Ellison

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.

Sachin Jain

Thanks. That did the trick :).

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

Nexis

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;

}

}

}

Sachin Jain

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;

}

Yannis

Hi Robert.

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

John

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!

Robert Ellison

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.

John

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!

Matthias

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

Robert Ellison

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.

Matthias

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!

Lena

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.

Murilo

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

Darren Wall

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

Robert Ellison

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.

Techedemic

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

Kevin

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

Robert Ellison

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

Kevin

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

Darren Wall

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.

Agnes Krawczyk

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

Agnes Krawczyk

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!

Mark Perrin

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

Asier

I'm getting this error:

TypeError: Cannot call method "appendRow" of null.

Any ideas?

Robert Ellison

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?

Asier

That was the problem.

I completely forgot about that.

Thank you very much,

Regards.

Agnes Krawczyk

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?

Robert Ellison

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

Agnes Krawczyk

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.

Chi

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?

Robert Ellison

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.

Gary

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?

Gary

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

Agnes Krawczyk

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.

Gary

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.

Gary

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"

Agnes Krawczyk

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?

Robert Ellison

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.

Quinn

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?

Robert Ellison

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.

Nadezda

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.

Robert Ellison

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.

Alex

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

Chi

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.

Robert Ellison

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?

Alex

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.

Leafab

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.

James Vondale

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?

Robert Ellison

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

Vishal

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?

Robert Ellison

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.

Jack

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?

Robert Ellison

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.

Jack

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?

Robert Ellison

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

Jack

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!

Robert Ellison

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

Darren Wall

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?

Jack

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!

Robert Ellison

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.

Dennis

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

Robert Ellison

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.

Mark

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.

momo

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!

Robert Ellison

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.

Rajat Anand

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?

Robert Ellison

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.

Subham Mishra

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.

Robert Ellison

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.

Rajat Anand

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.

Robert Ellison

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.

Rajat Anand

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?

Robert Ellison

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.

John Scheermeijer

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.

Sandy

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

Robert Ellison

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.

Robert Ellison

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.

John Scheermeijer

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.

Sandy

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

Robert Ellison

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.

Sandy

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

Robert Ellison

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.

Sandy

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

Robert Ellison

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

Sandy

It's working.

Thank you Robert.

You are the best!

burningvortex99

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.

Robert Ellison

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

burningvortex99

Cool, thanks Robert!

Will try and see if I can reduce the interval.

John Cannon

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

Alexander

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

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

Robert Ellison

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.

Robert Ellison

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.

Mike

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

Robert Ellison

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.

Mike

Ah that was it...thanks!

Kai Lansdell

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!

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.

I Thought He Came With You is Robert Ellison's blog.

Newsletter

Related

Average Server Response Time in Azure Metrics