How To: Sync Fitbit Data to Google Spreadsheet vs 2 (Aug 2020)

The following article is a step by step guide on how to sync Fitbit data into a Google spreadsheet.

This method of synchronizing Google Spreadsheet with Fitbit data is based on the work of Simon Bromberg. It uses a new Fitbit OAuth2 and Google’s OAuth2 authentication method (tested as of 2.Aug.2020). You can find more about the sync scripts here: https://github.com/simonbromberg/googlefitbit

So let’s start.


Copy Interday.gs script

The original interday.gs script released by Simon Bromberg no longer works with the latest Fitbit API. I’ve made a couple of changes to the script for it to continue working. Just not that this latest version is coded to sync only the latest n number of days from Fitbit into Google Spreadsheet, instead of the original range between dates.

Copy the following new version of the script:

// interday.gs -- download daily totals from Fitbit
// Doesn't require special permission, just follow setup and authorize
// Original script by loghound@gmail.com
// Modifications - Simon Bromberg (http://sbromberg.com)
// Modifications - Mark Leavitt (PDX Quantified Self organizer) www.markleavitt.com
// Modifications 2020 - Jozef Jarosciak - joe0.com
// https://github.com/qslabs/FitbitDailyData/blob/master/FitbitDailyData.gs
// You are free to use, modify, copy any of the code in this script for your own purposes, as long as it's not for evil

/*
 * Do not change these key names. These are just keys to access these properties once you set them up by running the Setup function from the Fitbit menu
 */
// Key of ScriptProperty for Firtbit consumer key.
var CONSUMER_KEY_PROPERTY_NAME = "fitbitConsumerKey";
// Key of ScriptProperty for Fitbit consumer secret.
var CONSUMER_SECRET_PROPERTY_NAME = "fitbitConsumerSecret";

/*
 * You also need to add the callback URL to your app at dev.fitbit.com in the following format (replace {PROJECT KEY} with your project key:
 * https://script.google.com/macros/d/{PROJECT KEY}/usercallback
 * Go to your app at dev.fitbit.com and click "Edit Application Settings", add the URL in the Callback URL textbox, one URL per line (can do multiple in a single app)
 */

var SERVICE_IDENTIFIER = 'fitbit'; // usually do not need to change this either

// Default loggable resources (from Fitbit API docs).
var LOGGABLES = ["activities/log/steps", "activities/log/distance",
                 "activities/log/activeScore", "activities/log/activityCalories",
                 "activities/log/calories", "foods/log/caloriesIn",
                 "activities/log/minutesSedentary",
                 "activities/log/minutesLightlyActive",
                 "activities/log/minutesFairlyActive",
                 "activities/log/minutesVeryActive", 
                 "sleep/startTime",  
                 "sleep/timeInBed",  
                 "sleep/minutesAsleep",  
                 "sleep/awakeningsCount",  
                 "sleep/minutesAwake",  
                 "sleep/minutesToFallAsleep",  
                 "sleep/minutesAfterWakeup",  
                 "sleep/efficiency",
                 "body/weight", "body/bmi", "body/fat",];

function getFitbitService() {
    // Create a new service with the given name. The name will be used when
    // persisting the authorized token, so ensure it is unique within the
    // scope of the property store
    if (!isConfigured()) {
        setup();
        return;
    }
                 
    return OAuth2.createService(SERVICE_IDENTIFIER)
    
    // Set the endpoint URLs, which are the same for all Google services.
    .setAuthorizationBaseUrl('https://www.fitbit.com/oauth2/authorize')
    .setTokenUrl('https://api.fitbit.com/oauth2/token')
    
    // Set the client ID and secret, from the Google Developers Console.
    .setClientId(getConsumerKey())
    .setClientSecret(getConsumerSecret())
    
    // Set the name of the callback function in the script referenced
    // above that should be invoked to complete the OAuth flow.
    .setCallbackFunction('authCallback')
    
    // Set the property store where authorized tokens should be persisted.
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('activity nutrition sleep weight profile settings')
    // but not desirable in a production application.
    //.setParam('approval_prompt', 'force')
    .setTokenHeaders({
        'Authorization': 'Basic ' + Utilities.base64Encode(getConsumerKey() + ':' + getConsumerSecret())
    });
}

function clearService(){
    OAuth2.createService(SERVICE_IDENTIFIER)
    .setPropertyStore(PropertiesService.getUserProperties())
    .reset();
}

function showSidebar() {
    var service = getFitbitService();
    if (!service.hasAccess()) {
        var authorizationUrl = service.getAuthorizationUrl();
        var template = HtmlService.createTemplate(
                                                  '<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' +
                                                  'Reopen the sidebar when the authorization is complete.');
        template.authorizationUrl = authorizationUrl;
        var page = template.evaluate();
        SpreadsheetApp.getUi().showSidebar(page);
    } else {
        Logger.log("Has access!");
    }
}

function authCallback(request) {
    Logger.log("authcallback");
    var service = getFitbitService();
    var isAuthorized = service.handleCallback(request);
    if (isAuthorized) {
        Logger.log("success");
        return HtmlService.createHtmlOutput('Success! You can close this tab.');
    } else {
        Logger.log("denied");
        return HtmlService.createHtmlOutput('Denied. You can close this tab');
    }
}

function getUser() {
    var service = getFitbitService();
    
    var options = {
    headers: {
        "Authorization": "Bearer " + service.getAccessToken(),
        /*"oAuthServiceName": SERVICE_IDENTIFIER,
         "oAuthUseToken": "always",*/
        "method": "GET"
    }};
    var response = UrlFetchApp.fetch("https://api.fitbit.com/1/user/-/profile.json",options);
    var o = Utilities.jsonParse(response.getContentText());
    return o.user;
}
                 
                 
                 
function showPrompt() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
      'Let\'s get to know each other!',
      'Please enter your name:',
      'First Date:',
      'Last Date:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
                 var text1 = result.getResponseText();
                 var text2 = result.getResponseText();
                 
  if (button == ui.Button.OK) {
    // User clicked "OK".
    ui.alert('Your name is ' + text + '.');
  } else if (button == ui.Button.CANCEL) {
    // User clicked "Cancel".
    ui.alert('I didn\'t get your name.');
  } else if (button == ui.Button.CLOSE) {
    // User clicked X in the title bar.
    ui.alert('You closed the dialog.');
  }
}                 
                 

// function setup accepts and stores the Consumer Key, Consumer Secret, Project Key, firstDate, and list of Data Elements
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    
    //var app = UiApp.createApplication().setTitle("Setup Fitbit Download"); // UiApp API deprecated, may need to eventually replace with HTML service
    var app = HtmlService.createApplication().setTitle("Setup Fitbit Download"); // UiApp API deprecated, may need to eventually replace with HTML service
                 
    app.setStyleAttribute("padding", "10px");
    
    var consumerKeyLabel = app.createLabel("Fitbit OAuth 2.0 Client ID:*");
    var consumerKey = app.createTextBox();
    consumerKey.setName("consumerKey");
    consumerKey.setWidth("100%");
    consumerKey.setText(getConsumerKey());
    
    var consumerSecretLabel = app.createLabel("Fitbit OAuth Consumer Secret:*");
    var consumerSecret = app.createTextBox();
    consumerSecret.setName("consumerSecret");
    consumerSecret.setWidth("100%");
    consumerSecret.setText(getConsumerSecret());
    
    var projectKeyTitleLabel = app.createLabel("Project key: ");
    var projectKeyLabel = app.createLabel(ScriptApp.getProjectKey());
                 
    var firstDate = app.createTextBox().setId("firstDate").setName("firstDate");
    firstDate.setName("firstDate");
    firstDate.setWidth("100%");
    firstDate.setText(getFirstDate());
    
    var lastDate = app.createTextBox().setId("lastDate").setName("lastDate");
    lastDate.setName("lastDate");
    lastDate.setWidth("100%");
    lastDate.setText(getLastDate());
    
    // add listbox to select data elements
    var loggables = app.createListBox(true).setId("loggables").setName(
                                                                       "loggables");
    loggables.setVisibleItemCount(4);
    // add all possible elements (in array LOGGABLES)
    var logIndex = 0;
    for (var resource in LOGGABLES) {
        loggables.addItem(LOGGABLES[resource]);
        // check if this resource is in the getLoggables list
        if (getLoggables().indexOf(LOGGABLES[resource]) > -1) {
            // if so, pre-select it
            loggables.setItemSelected(logIndex, true);
        }
        logIndex++;
    }
    // create the save handler and button
    var saveHandler = app.createServerClickHandler("saveSetup");
    var saveButton = app.createButton("Save Setup", saveHandler);
    
    // put the controls in a grid
    var listPanel = app.createGrid(8, 2);
    listPanel.setWidget(1, 0, consumerKeyLabel);
    listPanel.setWidget(1, 1, consumerKey);
    listPanel.setWidget(2, 0, consumerSecretLabel);
    listPanel.setWidget(2, 1, consumerSecret);
    listPanel.setWidget(3, 0, app.createLabel(" * (obtain these at dev.fitbit.com)"));
    listPanel.setWidget(4, 0, projectKeyTitleLabel);
    listPanel.setWidget(4, 1, projectKeyLabel);
    listPanel.setWidget(5, 0, app.createLabel("Start date (yyyy-mm-dd)"));
    listPanel.setWidget(5, 1, firstDate);
    listPanel.setWidget(6, 0, app.createLabel("End date (yyyy-mm-dd)"));
    listPanel.setWidget(6, 1, lastDate);
    listPanel.setWidget(7, 0, app.createLabel("Data Elements to download:"));
    listPanel.setWidget(7, 1, loggables);
    
    // Ensure that all controls in the grid are handled
    saveHandler.addCallbackElement(listPanel);
    // Build a FlowPanel, adding the grid and the save button
    var dialogPanel = app.createFlowPanel();
    dialogPanel.add(listPanel);
    dialogPanel.add(saveButton);
    app.add(dialogPanel);
    doc.show(app);
}

// function sync() is called to download all desired data from Fitbit API to the spreadsheet
function sync() {
    // if the user has never performed setup, do it now
    if (!isConfigured()) {
        setup();
        return;
    }
    var colIndex = 1
    var dateColDone = 0;
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    doc.setFrozenRows(4);
    doc.getRange("R1C1").setValue("Sheet last synced: " + new Date());
    doc.getRange("R2C1").setValue("Battery");
    doc.getRange("R3C1").setValue("Last Sync");
    doc.getRange("R4C1").setValue("Date");
    var user = getUser();
    var options = { headers:{
        "Authorization": 'Bearer ' + getFitbitService().getAccessToken(),
        "method": "GET"
    }};
    // prepare and end date, and a list of desired data elements
    var dateString = getLastDate();
    var activities = getLoggables();
    // for each data element, fetch a list beginning from the firstDate, ending with today
    Logger.log("here");
    try {
        var devices = UrlFetchApp.fetch("https://api.fitbit.com/1/user/-/devices.json",options);
        var device = JSON.parse(devices.getContentText())[0];
    } catch (exception) {
        Logger.log(exception);
        Browser.msgBox("error getting device info for " + user["displayName"]);
    }
    for (var activity in activities) {
        var currentActivity = activities[activity];
        try {
            var result = UrlFetchApp.fetch("https://api.fitbit.com/1/user/-/"
                                           + currentActivity + "/date/" + getFirstDate() + "/"
                                           + dateString + ".json", options);
        } catch (exception) {
            Logger.log(exception);
            Browser.msgBox("Error downloading " + currentActivity);
        }
        var o = JSON.parse(result.getContentText());
        // set title
        var workingCol = (colIndex + !dateColDone).toString()
        var headerCell = doc.getRange("R1C"+workingCol);
        headerCell.setValue(user["displayName"]);
        headerCell = doc.getRange("R2C"+workingCol);
        if (device != null) {
            headerCell.setValue(device["battery"]);
        }
        else {
            headerCell.setValue("error no device");
        }
        headerCell = doc.getRange("R3C"+workingCol);
        if (device != null) {
            var syncDate = new Date(device["lastSyncTime"]+"Z");
            headerCell.setValue(Utilities.formatDate(syncDate, "GMT", "EEE, d MMM yyyy HH:mm"));
        }
        else {
            headerCell.setValue("error no device");
        }
        var titleCell = doc.getRange("R4C"+colIndex.toString());
        var cell = doc.getRange("R5C"+colIndex.toString());
        
        // fill the spreadsheet with the data
        var index = 0;
        for (var i in o) {
            // set title for this column
            var title = i.substring(i.lastIndexOf('-') + 1);
            titleCell.offset(0, !dateColDone + activity * 1.0).setValue(title);
            
            var row = o[i];
            for (var j = row.length - 1; j >= 0; j--) {
                var val = row[j];
                
                if (!dateColDone) {
                    cell.offset(index, 0).setValue(val["dateTime"]); // set the date index
                }
                cell.offset(index, !dateColDone + activity * 1.0).setValue(val["value"]);
                index++;
            }
        }
    }
    colIndex+= 1 + !dateColDone;
    dateColDone = 1;
}

function isConfigured() {
    return getConsumerKey() != "" && getConsumerSecret() != "";
}

// function saveSetup saves the setup params from the UI
function saveSetup(e) {
    setConsumerKey(e.parameter.consumerKey);
    setConsumerSecret(e.parameter.consumerSecret);
    setLoggables(e.parameter.loggables);
    setFirstDate(e.parameter.firstDate);
    setLastDate(e.parameter.lastDate);
    
    var app = UiApp.getActiveApplication();
    app.close();
    return app;
}

function getProperty(key) {
    Logger.log("get property "+key);
    return PropertiesService.getScriptProperties().getProperty(key);
}

function setProperty(key, value) {
    PropertiesService.getScriptProperties().setProperty(key, value);
}

function setConsumerKey(consumerKey) {
    setProperty(CONSUMER_KEY_PROPERTY_NAME, consumerKey);
}

function getConsumerKey() {
    var consumer = getProperty(CONSUMER_KEY_PROPERTY_NAME);
    if (consumer == null) {
        consumer = "";
    }
    
    return consumer;
}

function setConsumerSecret(secret) {
    setProperty(CONSUMER_SECRET_PROPERTY_NAME, secret);
}

function getConsumerSecret() {
    var secret = getProperty(CONSUMER_SECRET_PROPERTY_NAME);
    
    if (secret == null) {
        secret = "";
    }
    
    return secret;
}

function setLoggables(loggables) {
    setProperty("loggables", loggables);
}

function getLoggables() {
    var loggable = getProperty("loggables");
    
    if (loggable == null) {
        loggable = LOGGABLES;
    } else {
        loggable = loggable.split(',');
    }
    
    return loggable;
}

function setFirstDate(firstDate) {
    setProperty("firstDate", firstDate);
}

function setLastDate(lastDate) {
    setProperty("lastDate", lastDate);
}

function getFirstDate() {
   // var todayDate = new Date;
  
  var firstDate = subDaysFromDate(new Date(),60);
  
  firstDate = firstDate.getFullYear()
    + '-'
    + ("00" + (firstDate.getMonth() + 1)).slice(-2)
    + '-'
    + ("00" + firstDate.getDate()).slice(-2);
  
  return firstDate;
  /*
  return firstDate.getFullYear()
    + '-'
    + ("00" + (todayDate.getMonth() + 1)).slice(-2)
    + '-'
    + ("00" + todayDate.getDate()).slice(-2);
  
  
  
  var firstDate = getProperty("firstDate");
    
    if (firstDate == null) {
        firstDate = formatToday(); // default value; feel free to change this
    }
    */
  //  return firstDate;
}

function getLastDate() {
 
    var lastDate = subDaysFromDate(new Date(),0);
  
  lastDate = lastDate.getFullYear()
    + '-'
    + ("00" + (lastDate.getMonth() + 1)).slice(-2)
    + '-'
    + ("00" + lastDate.getDate()).slice(-2);
  
  return lastDate;
  
}

function formatToday() {
    var todayDate = new Date;
    return todayDate.getFullYear()
    + '-'
    + ("00" + (todayDate.getMonth() + 1)).slice(-2)
    + '-'
    + ("00" + todayDate.getDate()).slice(-2);
}

function subDaysFromDate(date,d){
  // d = number of day ro substract and date = start date
  var result = new Date(date.getTime()-d*(24*3600*1000));
  return result
}



// function onOpen is called when the spreadsheet is opened; adds the Fitbit menu
function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
                       name: "Sync (" + getFirstDate() + " - " + getLastDate() + ")",
                       functionName: "sync"
                       }, 
                       {
                       name: "Setup",
                       functionName: "showPrompt"
                       }, 
                       {
                       name: "Authorize",
                       functionName: "showSidebar"
                       },
                       {
                       name: "Reset",
                       functionName: "clearService"
                       }];
    ss.addMenu("Fitbit", menuEntries);
  
  /*

  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .createMenu('Fitbit-New')
      .addItem('Show prompt', 'showPrompt')
      .addToUi();
*/
  
}

This new version syncs the last 60 days of data to your Google spreadsheet.

If 60 days is not enough, the total number of days to sync can be adjusted on line 404 of the script, just change the number 60, to whatever you desire, but note that I did not test the script with more than 60 days and do not guarantee it’ll work, as Fitbit might have some restrictions on the total number of days to request the sync for.

Create a new Google Spreadsheet

As a first step, create a new Google spreadsheet, name it whatever you want and save it.

 

Add interday.gs script to Google Spreadsheet

Then go to ‘Tools’, select ‘Script Editor’ option:

Paste the contents of the interday.gs file into the script editor, overwriting whatever default function definition is already in there.

Then name, it should look something like this once you’re done:

Save the document:

Now, return to your blank spreadsheet (you can close the script window) and press F5 to refresh the page. It’ll take about 5 seconds after the refresh for the ‘Fitbit’ menu to show up at the top of the Google spreadsheet menu. This is where you’ll find it:

 

Install OAuth2 library for Google Apps Script

Now go to ‘Tools,’ select ‘Script Editor’ again. We’ll need to add the Oauth2 Google Script library to our spreadsheet. To do so, click Resources in the top menu, then select Libraries:

Then add the OAuth2.0 library by typing in the project key “MswhXl8fVhTFUH_Q3UOJbXvxhMjh3Sh48” and hitting the Add button:

Once done, select the newest version and development mode and press save. It should look like this:

 

Get the ‘Script ID’

The past procedures required to get a project’s key by clicking on the ‘Fitbit’ menu inside the spreadsheet and clicking ‘Setup.’

However, the new process no longer uses Project Key, which is deprecated now.

Instead, we’ll need to get a ‘Script ID’. To find it, go to ‘Tools/Script Editor’ again and then select ‘File/Project Properties’:

Copy the text adjacent to ‘Script ID’ in the popup. We will need that in subsequent steps.

 

Authorize Fitbit Sync

Close the Script Editor and go back to your blank Google spreadsheet and select the FitBit menu and click on ‘Setup’:

A popup window will ask for Authorization:

Click on the ‘Continue’ button and follow the Google singing process under which you want to authorize the Fitbit syncing application.

Then it will show you the permissions the application is requesting, click ‘Allow’.

Then the ‘Setup Fitbit Download’ panel should appear. Just close it for now.

 

 

Setup a new Fitbit app at Dev.Fitbit.com

Now, go to https://dev.fitbit.com/ and either register a new account or login to your existing account.

Click on ‘Manage My Apps’ in the top menu and click on the ‘Register a New App’ option:

The next step is to fill up the application. You can use whatever data you wish, see the example of what I did.

These are the important parts:

  • For the OAuth 2.0 application type option, select: ‘Personal’ option.
  • The default access type should be set to: ‘Read-Only’.
  • The other important piece of data on the form is the Callback URL. Type in the following URL: https://script.google.com/macros/d/YOUR_SCRIPT_ID/usercallback. Then replace YOUR_SCRIPT_ID in the URL with the Script ID we have gathered in the previous step.

This is what the filled up application should look like. Once done, just press the ‘Save’ button.

On the next page, Fitbit will ask you to agree to the terms, and Register.

Do so, and you will be redirected to a page that contains your credentials.

Markdown your ‘OAuth 2.0 Client ID’ and your ‘Client Secret’ keys. The following two pieces of information:

 

Setup a Fitbit sync

Go back to your blank Google Spreadsheet and select ‘Setup’ from the ‘Fitbit’ menu.

Copy in the ‘OAuth 2.0 Client ID’ and the ‘Client Secret’ from the previous step. and your project key from step 3.

Select all the activities that you’d like to sync to your spreadsheet. You can select all of them or individual ones.

Then select the range for which to download the data. Just note, that you shouldn’t try to download more than a couple of months worth of data, Fitbit’s API will not like you.

Click ‘Save Setup’ and the panel will disappear.

 

 

Authorize Fitbit

In your spreadsheet, click ‘Authorize’ from the Fitbit menu and a sidebar will show up on the right.

Click the word ‘Authorize’ in the sidebar.

A page will open up with the Fitbit login page. Log in to the Fitbit account you would like to download data from in the new window, authorize the application by clicking ‘Allow’, and then close the tab when it says “Success you can close this tab”

 

 

First Sync

Inside the Google Spreadsheet, click on the Fitbit menu and select the ‘Sync’ option:

 

The process of synchronization will start and once finished, it’ll look something like this:

 

We’re done, if everything worked, don’t forget to leave me a comment.

Enjoy!

Facebook Comments