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

Does anybody know how to prevent the authorization from expiring?

With "Publishing Status" set to "Testing" in the GCP console, the credentials expire after 7 days. If I publish the app and the status becomes "In Production" (without going though any verification steps), the credentials expire within a few hours.

The 7-day expiry seems to be expected behavior (see "How to add authentication to a Google apps script without it expiring?" on stackoverflow) but I haven't found a way around it without having to go through Google's verification process.

Here's my code for fetching sleep data. Make sure you add the appropriate scope (fitness.sleep.read), and then trigger getSleepData().

```

// Populate sleep data

function getSleepData() {

const numDaysToGet = 10;

const tabName = 'GFitImportSleep';

const timezone = 'America/Los_Angeles';

getDailySleepData(numDaysToGet, 0, tabName, timezone);

}

// Format string number of milliseconds using provided timezone and format string

function toDate(millis, tz, format) {

var d = new Date(parseInt(millis));

return Utilities.formatDate(d, tz, format);

}

/*

Load sleep sessions and overwrite the given tab with the data.

First line of output is a description, second line is headers.

*/

function getDailySleepData(fromDaysAgo, toDaysAgo, tabName, timezone) {

let start = new Date();

start.setHours(0,0,0,0);

start.setDate(start.getDate() - fromDaysAgo);

let end = new Date();

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

end.setDate(end.getDate() - toDaysAgo);

const url = `https://www.googleapis.com/fitness/v1/users/me/sessions?startTime=${start.toISOString()}&endTime=${end.toISOString()}&activityType=72`;

const fitService = getFitService();

const response = UrlFetchApp.fetch(url, {

headers: {

Authorization: 'Bearer ' + fitService.getAccessToken()

},

'method' : 'GET',

});

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

Logger.log(json);

let ss = SpreadsheetApp.getActiveSpreadsheet();

let sheet = ss.getSheetByName(tabName);

sheet.clear({ formatOnly: false, contentsOnly: true });

sheet.appendRow(["This sheet is generated via Apps Script using the GFit API. Use the 'Google Fit' menu to regenerate manually. If seeing authorization errors, click 'Reset Settings' and then 'Authorize' in that menu."])

sheet.appendRow(["startTimeMillis", "start time", "endTimeMillis", "end time", "end date", "duration (h)", "data"]);

for(var s = 0; s < json.session.length; s++) {

const session = json.session[s];

const duration_m = (session.endTimeMillis - session.startTimeMillis)/(1000 * 60 * 60);

sheet.appendRow(

[session.startTimeMillis,

toDate(session.startTimeMillis, timezone, 'h:mm:ss a MM/dd/yy z'),

session.endTimeMillis,

toDate(session.endTimeMillis, timezone, 'h:mm:ss a MM/dd/yy z'),

toDate(session.endTimeMillis, timezone, 'MM/dd/yy'),

duration_m,

session]);

}

}

```

i wanted to add sleep data too but i just couldn't find the documentation of google for below lines anywhere. how can i find sleep data's dataTypeName and dataSourceId ?

{

"dataTypeName": "derived: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.distance.delta",

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

},

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

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 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,

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

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!

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.

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