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

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

Updated on Friday, December 29, 2023

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 Enabled APIs and services and find and select the Fitness API. Then go to Credentials 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:

// add your Google API Project OAuth client ID and client secret here var ClientID = ''; var ClientSecret = ''; function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Google Fit') .addItem('Authorize if needed (does nothing if already authorized)', 'showSidebar') .addItem('Get Metrics for Yesterday', 'getMetrics') .addItem('Get Metrics for past 60 days', 'getHistory') .addItem('Reset Settings', 'clearProps') .addToUi(); } function getMetrics() { getMetricsForDays(1, 1, 'Metrics'); } function getHistory() { getMetricsForDays(1, 60, 'History'); } // see step count example at https://developers.google.com/fit/scenarios/read-daily-step-total // adapted below to handle multiple metrics (steps, weight, distance), only logged if present for day function getMetricsForDays(fromDaysAgo, toDaysAgo, tabName) { var start = new Date(); start.setHours(0,0,0,0); start.setDate(start.getDate() - toDaysAgo); var end = new Date(); end.setHours(23,59,59,999); end.setDate(end.getDate() - fromDaysAgo); var fitService = getFitService(); 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.distance.delta", "dataSourceId": "derived:com.google.distance.delta:com.google.android.gms:merge_distance_delta" } ], "bucketByTime": { "durationMillis": 86400000 }, "startTimeMillis": start.getTime(), "endTimeMillis": end.getTime() }; var response = UrlFetchApp.fetch('https://www.googleapis.com/fitness/v1/users/me/dataset:aggregate', { 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++) { // each bucket in our response should be a day var bucketDate = new Date(parseInt(json.bucket[b].startTimeMillis, 10)); var steps = -1; var weight = -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[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; } sheet.appendRow([bucketDate, steps == -1 ? ' ' : steps, weight == -1 ? ' ' : weight, distance == -1 ? ' ' : distance]); } } // functions below adapted from Google OAuth example at https://github.com/googlesamples/apps-script-oauth2 function getFitService() { // 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. return OAuth2.createService('fit') // Set the endpoint URLs, which are the same for all Google services. .setAuthorizationBaseUrl('https://accounts.google.com/o/oauth2/auth') .setTokenUrl('https://accounts.google.com/o/oauth2/token') // Set the client ID and secret, from the Google Developers Console. .setClientId(ClientID) .setClientSecret(ClientSecret) // 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()) // Set the scopes to request (space-separated for Google services). // see https://developers.google.com/fit/rest/v1/authorization for a list of Google Fit scopes .setScope('https://www.googleapis.com/auth/fitness.activity.read https://www.googleapis.com/auth/fitness.body.read https://www.googleapis.com/auth/fitness.location.read') // Below are Google-specific OAuth2 parameters. // Sets the login hint, which will prevent the account chooser screen // from being shown to users logged in with multiple accounts. .setParam('login_hint', Session.getActiveUser().getEmail()) // Requests offline access. .setParam('access_type', 'offline') // Forces the approval prompt every time. This is useful for testing, // but not desirable in a production application. //.setParam('approval_prompt', 'force'); } function showSidebar() { var fitService = getFitService(); if (!fitService.hasAccess()) { var authorizationUrl = fitService.getAuthorizationUrl(); var template = HtmlService.createTemplate( '<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' + 'Close this after you have finished.'); template.authorizationUrl = authorizationUrl; var page = template.evaluate(); SpreadsheetApp.getUi().showSidebar(page); } else { // ... } } function authCallback(request) { var fitService = getFitService(); var isAuthorized = fitService.handleCallback(request); if (isAuthorized) { return HtmlService.createHtmlOutput('Success! You can close this tab.'); } else { return HtmlService.createHtmlOutput('Denied. You can close this tab'); } } function clearProps() { PropertiesService.getUserProperties().deleteAllProperties(); }

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

Add your comment...

More Google Apps Script Projects

(All Code Posts)

(Published to the Fediverse as: Export Google Fit Daily Steps, Weight and Distance to a Google Sheet #code #software #fit #appsscript #google #sheets #drive Detailed instructions for setting up an automatic daily export of your step count from Google Fit to a Google Sheet. Uses Google Sheets, Apps Script and the Google Fitness API. )

code, software, fit, apps script, google, sheets, drive

Winter Solstice 2017

It's the start of Winter in the Northern Hemisphere. Rendered in Catfood Earth (Windows, Android).

(Previously, Previously, Previously, Previously)

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Winter Solstice 2017 #code #winter #solstice #earth #summer #southern #hibernal Winter Solstice 2017 rendered in Catfood Earth for Windows and Android. It's the start of Winter in the Northern Hemisphere. )

code, winter, solstice, earth, summer, southern, hibernal

Autumnal Equinox 2017

Updated on Saturday, August 21, 2021

Autumn/Fall starts now in the Northern Hemisphere. Springtime for the hemispherically challenged. Rendered in Catfood Earth (Windows, Android live wallpaper).

(Previously, Previously, Previously, Previously)

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Autumnal Equinox 2017 #code #earth #equinox #autumnal Autumnal (fall) equinox 2017. Autumn begins in the Northern Hemisphere, Spring in the Southern Hemisphere. Image rendered in Catfood Earth. )

code, earth, equinox, autumnal

Email Alerts for new Referers in Google Analytics using Apps Script

Updated on Monday, February 13, 2023

It's useful to know when you have a new website referrer. Google Analytics is plagued with spam referral and you want to filter this out of reporting as quickly as possible to stop it from skewing your data. It's also helpful to be able to respond quickly to new referral traffic - maybe leave a comment or promote the new link on social media.

The script below will send you a daily email with links to any new referrers (this is GA3, there is a GA4 version later in this post).

Start a new apps script project in Google Drive and paste in the code. At the top enter the view ID that you want to monitor and the email address that should receive reports.

Choose Advanced Google Services from the Resources menu and switch on the Google Analytics API. Then click the Google API Console link and enable the Google Analytics API there as well.

Finally pick Current project's triggers from the Edit menu and trigger the main function daily at a convenient time.

This script saves known referrers in script properties. For a site with lots of traffic this may run out of space in which case you might need to switch this out and write known referrers to a sheet instead.

For Google Analytics 4 properties use the version of the script below. The setup process is the same, but you need the Google Analytics Data API instead of the Google Analytics API.

Add your comment...

More Google Apps Script Projects

(All Code Posts)

(Published to the Fediverse as: Email Alerts for new Referers in Google Analytics using Apps Script #code #googleanalytics #appsscript #gas #ga4 Apps script that will email you any new referral traffic from Google Analytics. Useful for responding to new links and referrer spam. GA3 and GA4 versions. )

code, google analytics, apps script, gas, ga4

Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API

Updated on Sunday, September 18, 2022

I needed a console app that reads some inputs from an online Excel workbook, does some processing and then writes back the results to a different worksheet. Because I enjoy pain I decided to use the thinly documented new Microsoft.Graph client library. The sample code below assumes that you have a work or education Office 365 subscription.

using System; using System.Collections.Generic; using System.Globalization; using System.IO; using System.Linq; using System.Net.Http.Headers; using System.Threading.Tasks; using Microsoft.Graph; using Microsoft.IdentityModel.Clients.ActiveDirectory; using Newtonsoft.Json.Linq; namespace Excel365Test { /// <summary> /// 1) Install Microsoft.Graph NuGet Package /// 2) Install Microsoft.IdentityModel.Clients.ActiveDirectory NuGet Package /// 3) Register app at https://portal.azure.com/ - need app ID and redirct URL below /// </summary> class Program { static void Main(string[] args) { TokenCache tokenCache = new TokenCache(); // load tokens from file string tokenPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Excel365Test"); if (!Directory.Exists(tokenPath)) { Directory.CreateDirectory(tokenPath); } tokenPath = Path.Combine(tokenPath, "tokens.dat"); if (System.IO.File.Exists(tokenPath)) { tokenCache.Deserialize(System.IO.File.ReadAllBytes(tokenPath)); } // this is the OAUTH 2.0 TOKEN ENDPOINT from https://portal.azure.com/ -> Azure Active Directory -> App Registratuons -> End Points var authenticationContext = new AuthenticationContext("https://login.windows.net/your-url-here/", tokenCache); // only prompt when needed, you'll get a UI the first time you run var platformParametes = new PlatformParameters(PromptBehavior.Auto); var authenticationResult = authenticationContext.AcquireTokenAsync("https://graph.microsoft.com/", "your-app-id", // Application ID from https://portal.azure.com/ new Uri("http://some.redirect.thing/"), // Made up redirect URL, also from https://portal.azure.com/ platformParametes).Result; string token = authenticationResult.AccessToken; // save token so we don't need to re-authorize System.IO.File.WriteAllBytes(tokenPath, tokenCache.Serialize()); // use the token with Microsoft.Graph calls GraphServiceClient client = new GraphServiceClient(new DelegateAuthenticationProvider( (requestMessage) => { requestMessage.Headers.Authorization = new AuthenticationHeaderValue("bearer", token); return Task.FromResult(0); })); // test reading from a sheet - in this case I have a test worksheet with a two column table for name/value pairs var readSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["test"]; var readTables = readSheet.Tables.Request().GetAsync().Result; string readTableId = readTables[0].Name; var table = readSheet.Tables[readTableId].Rows.Request().GetAsync().Result; // convert page to a dictionary... this doesn't handle pagination Dictionary<string, decimal> tableValues = table.CurrentPage.ToDictionary(r => r.Values.First.First.ToString(), r => Convert.ToDecimal(r.Values.First.Last, CultureInfo.InvariantCulture)); // test adding a row to a table with four columns // sadly it seems you need this exact format, a regular JArray or JObject fails WorkbookTableRow newRow = new WorkbookTableRow { Values = JArray.Parse("[[\"1\",\"2\",\"3\",\"4\"]]") }; var outputSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["data"]; var outputTables = outputSheet.Tables.Request().GetAsync().Result; string outputTableId = outputTables[0].Name; var outputResult = outputSheet.Tables[outputTableId].Rows.Request().AddAsync(newRow).Result; // the excel unit tests seem to be the most useful documentation right now: // https://github.com/microsoftgraph/msgraph-sdk-dotnet/blob/dev/tests/Microsoft.Graph.Test/Requests/Functional/ExcelTests.cs } } }

Paste the code into a new console project and then follow the instructions at the top to add the necessary NuGet packages. You'll also need to register an application at https://portal.azure.com/. You want a Native application and you'll need the Application ID and the redirect URL (just make up some non-routable URL for this). Under Required Permissions for the app you should add read and write files delegated permissions for the Microsoft Graph API.

Hope this saves you a few hours. Comment below if you need a more detailed explanation for any of the above.

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API #code #microsoft #excel #graph #office365 How to read and write data to an Office 365 Excel workbook using the Microsoft.Graph client library from a C# console application. )

code, microsoft, excel, graph, office 365

Summer Solstice 2017

Summer starts now (or Winter for the Southern Hemisphere). Rendered in Catfood Earth.

(Previously, Previously, Previously, Previously)

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Summer Solstice 2017 #code #solstice #summer #winter #earth #northern #estival The exact moment of Summer Solstice 2017 as rendered in Catfood Earth )

code, solstice, summer, winter, earth, northern, estival

CodePlex Shutdown: Shapefile, Orb, StackHash and Blogger2BlogML Migration

Updated on Wednesday, August 4, 2021

CodePlex Shutdown: Shapefile, Orb, StackHash and Blogger2BlogML Migration

Microsoft is finally pulling the plug on CodePlex. I've hosted various projects there over the years and have just finished migrating everything over to GitHub which looks like it just might have a future:

blogger2blogml - Migrate blogger format blogs to BlogML.
orb - Controller library for Ambient Orb devices.
shapefile - parse the ESRI Shapefile format.
stackhash - full client for downloading and debugging Windows Error Reporting (WER) crash dumps.

Add your comment...

(All Code Posts)

(Published to the Fediverse as: CodePlex Shutdown: Shapefile, Orb, StackHash and Blogger2BlogML Migration #code #stackhash #codeplex #github CodePlex is gone. Shapefile, Orb, StackHash and Blogger2BlogML have been migrated to GitHib. )

code, stackhash, codeplex, github

Vernal Equinox 2017

Updated on Saturday, October 1, 2022

Vernal Equinox 2017

Vernal Equinox 2017 happens right now, as rendered in Catfood Earth.

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Vernal Equinox 2017 #code #earth #equinox #spring #autumn #vernal The exact moment of Vernal Equinox 2017 as rendered in Catfood Earth. )

code, earth, equinox, spring, autumn, vernal

Catfood Weather 2.30

Updated on Wednesday, February 22, 2017

Catfood Weather 2.30

Catfood Weather is a free taskbar based weather forecast app for Windows that includes a seven day forecast and severe weather alerts. It uses National Weather Service data and works for any US location (by ZIP code or latitude and longitude).

This update fixes a problem calling the National Weather Service API, so please install 2.30 if you're using a previous version of Catfood Weather. To get alerts for new releases follow the RSS Feed or sign up to the ITHCWY Newsletter.

Download Catfood Weather 2.30.

(Previously, Previously)

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Catfood Weather 2.30 #code #weather Free taskbar based weather forecasts for Windows. Hover for current conditions, click for a seven day outlook and you get automatic severe weather alerts. US locations by ZIP or latitude/longitude only. )

code, weather

Get an email if your site stops being mobile friendly (no longer available)

Updated on Tuesday, December 5, 2023

Google axed this tool today, so the script won't work any more. If you're looking for a replacement check out my Core Web Vitals script.

Get an email if your site stops being mobile friendly

Google just released an API for the mobile friendly test and so I've whipped up a script to send an alert if a web page violates their guidelines. This will run the test as often as you like and send you an email if it detects a problem. Alternatively if you're not mobile friendly it will keep emailing you until you fix any problems which might be a good motivational tool.

First start a new apps script project in drive and paste in the code below:

There are three variables you need to set, urlToMonitor is the full URL of the page to test, alertEmail is your email address (or whoever needs to be pestered) and runTestKey is the API key for the service. To get this go to the Google API Console, click Enable API, search for 'Google Search Console URL Testing Tools API' and click enable. Then click the Credentials option and generate a browser key.

Once you've configured the script choose 'Current project's triggers' from the Resources menu in apps script and set up a schedule for the mobileFriendlyMonitor() function.

Add your comment...

More Google Apps Script Projects

(All Code Posts)

(Published to the Fediverse as: Get an email if your site stops being mobile friendly (no longer available) #code #mobile #appsscript #gas Use Google Apps Script and the Mobile Friendly Test API to constantly monitor your site for any violations. )

code, mobile, apps script, gas

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

More Google Apps Script Projects

Winter Solstice 2017

Related Posts

You Might Also Like

Autumnal Equinox 2017

Related Posts

You Might Also Like

Email Alerts for new Referers in Google Analytics using Apps Script

More Google Apps Script Projects

Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API

Related Posts

You Might Also Like

Summer Solstice 2017

Related Posts

You Might Also Like

CodePlex Shutdown: Shapefile, Orb, StackHash and Blogger2BlogML Migration

Related Posts

You Might Also Like

Vernal Equinox 2017

Related Posts

You Might Also Like

Catfood Weather 2.30

Related Posts

You Might Also Like

Get an email if your site stops being mobile friendly (no longer available)

More Google Apps Script Projects

Newsletter

Popular Posts

Recent Posts

Recent Tags