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

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;

}

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;

}

}

}

Thanks. That did the trick :).

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

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.

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

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.

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.

"

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?

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

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.