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

Did anyone tell Material Design about Gesture Navigation?

The screen shot above is from Google Fit. Which icon is active? I can't tell any more. Is it the blue one or the underlined one which is a much stronger cue?

Of course none of the icons are underlined. This is a bottom navigation bar on top of the Android navigation bar on Android 10 with gesture navigation enabled. My brain knows this but my finger still tries to click on Home. Journal just looks so much more active I can't help it. This friction is also in Google Photos and Google Maps and presumably Google everything before too long.

Maybe the Google app developers don't have access to recent Pixels, or maybe the Material Design team all have iPhones?

(Previously: Material Design 3)

Add your comment...

(All Marketing Posts)

(Published to the Fediverse as: Did anyone tell Material Design about Gesture Navigation? #marketing #google #design I can no longer tell which tab of most Google Android apps is active due to confusion between gesture navigation and the bottom navigation bars. )

marketing, google, design

Google search-for-your-own-verified-sites Console

I don't know about you, but when it comes to Google Search Console I spend about 0.01% of the time adding sites and 99.99% analyzing existing ones. And yet when signing into Search Console with many verified sites the interface is ALL about adding a new one. Maybe 10% of the UX would be reasonable but it looks for all the world like I have nothing added.

To get to my sites I need to click the hamburger. Come on Google, being mobile first doesn't have to mean being desktop hostile.

Clicking the hamburger isn't even enough. This just brings up a practically blank sidebar. I then need to expand the 'Search property' drop down. Finally I get a needlessly scrolling list of my sites.

Add your comment...

(All Marketing Posts)

(Published to the Fediverse as: Google search-for-your-own-verified-sites Console #marketing #google #searchconsole Why do I need to click a hamburger AND drop down a menu to get to a list of verified sites in Google Search Console? )

marketing, google, search console

Using the Azure Monitor REST API from Google Apps Script

Updated on Saturday, February 12, 2022

This post describes how to get metrics (in this case average response time) from an Azure App Service into a Google Sheet. I’m doing this so I can go from the sheet to a Data Studio dashboard. I already have a report in Data Studio that pulls from Ads, Analytics and other sources. I’d rather spend hours adding Azure there than be forced to have one more tab open. You might have different reasons. Read on.

Create a Google Sheet and give it a memorable name. Rename the first sheet to AvgResponseTime and put ‘Date’ in A1 and ‘Average Response Time’ in B1.
Create a script (Script editor from the Tools menu) and give that a good name as well.
In the script editor pick Libraries from the Resources menu. Enter 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF which is the Google OAuth library, pick the latest version and click Save.
Select Project properties from the File menu and make a note of the Script ID.
Log into your Azure Console and then go to https://resources.azure.com/. You are looking for a metricdefinitions node for the resource that you want to monitor. In my case it’s subscriptions / MyName Subscription / resourceGroups / providers / Microsoft.Web / sites / MySite / metricdefintions. Browse through this list to find the id of the metric you’re interested in. For me it’s AverageResponseTime. Finding this was the hardest part. Microsoft’s documentation for resourceUri is literally ‘The identifier of the resource.’ Why even bother Microsoft? Make a note of the id and remove the ‘metricDefinitions/AverageResponseTime’ from the end, because of course the ID isn’t quite right for some reason. Mine looks something like this: /subscriptions/mylongid/resourceGroups/mysomethingResourceGroup/providers/Microsoft.Web/sites/mysiteid
Go back to the Azure Console and open Azure Active Directory. Select App registrations under Manage and create a New registration. Time to come up with another name. You probably want ‘Accounts in this organizational directory only’. The redirect URL is https://script.google.com/macros/d/SCRIPTID/usercallback - replace SCRIPTID with the Script ID you made a note of in step 4.
Click the View API permissions button, then Add a permission and then pick Azure Service Management. I’m using Delegated permissions and the user_impersonation permission. Then click Grant admin consent for Default Directory.
Go to Certificates & secrets (under manage) and create a new client secret. Make a careful note of it.
Go to Authentication (under Manage), check Access tokens under Implicit grant and then click Save at the top of the page.
Go to Overview and make a note of your Application (client) ID and Directory (tennant) ID.
You are getting close! Go to the script editor (from step 2) and paste in the code at the bottom of this post. There are four variables to enter at the top of the script. ClientID and TennantID are from step 10. ClientSecret is from step 8. ResourceID is from step 5. Save the script.
Reload the spreadsheet (back from step 1). You should get an Azure Monitor menu item. Choose Authorize from this menu. Google will ask you to authorize the script, do this for the Google account you’re using. Choose Authorize again, this time a sidebar will appear with a link. Follow the link and authorize against Azure (if you’re logged in this might just go directly to success). If you get authorization errors in the future run this step again. If that does help use Reset Settings and then try again.
You should be ready to get data. Choose Fetch Data from the Azure Monitor menu. If this doesn’t work check through steps 1-12 carefully again!
Last step - automate. Go back to the script editor. Choose Current project’s triggers from the Edit menu. Add a trigger (the small blue button hidden at the bottom right of the screen - not everything needs a floating action button Google!) to run fetchData daily at some reasonable time.

You should now have a daily record of average response time flowing to a Google sheet. This can easily be extended to support other metrics, and other time periods (you could get data by minute and run the script hourly for instance. See the metrics documentation for more ideas. I got this working for an App Service but it should be roughly the same flow for anything that supports metrics, you’ll just need to work on finding the right resourceUri / ID.

// script settings var ClientID = ''; var TennantID = ''; var ClientSecret = ''; var ResourceID = ''; var AuthBaseUrl = 'https://login.microsoftonline.com/' + TennantID + '/oauth2/v2.0/authorize'; var AuthTokenUrl = 'https://login.microsoftonline.com/' + TennantID + '/oauth2/v2.0/token'; var AuthScope = 'https://management.azure.com/user_impersonation offline_access'; function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Azure Monitor') .addItem('Authorize if needed (does nothing if already authorized)', 'showSidebar') .addItem('Fetch Data', 'fetchData') .addItem('Reset Settings', 'clearProps') .addToUi(); } function fetchData() { var azure = getAzureService(); var MILLIS_PER_DAY = 1000 * 60 * 60 * 24; var now = new Date(); var yesterday = new Date(now.getTime() - MILLIS_PER_DAY); var timespan = Utilities.formatDate(yesterday, "GMT", "yyyy-MM-dd'T'00:00:00'Z'/yyyy-MM-dd'T'23:59:59'Z'"); // see https://docs.microsoft.com/en-us/rest/api/monitor/metrics/list var metricUrl = 'https://management.azure.com/' + ResourceID + '/providers/microsoft.insights/metrics?timespan=' + timespan + '&interval=P1D&metricnames=AverageResponseTime&aggregation=Average&api-version=2018-01-01'; var response = UrlFetchApp.fetch(metricUrl, { headers: { Authorization: 'Bearer ' + azure.getAccessToken() }, 'method' : 'get' }); var json = JSON.parse(response.getContentText()); var avgResponseTime = json.value[0].timeseries[0].data[0].average; var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getSheetByName('AvgResponseTime'); sheet.appendRow([Utilities.formatDate(yesterday, "GMT", "yyyy-MM-dd"), avgResponseTime]); } // functions below adapted from Google OAuth example at https://github.com/googlesamples/apps-script-oauth2 function getAzureService() { // 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('azure') // Set the endpoint URLs, which are the same for all Google services. .setAuthorizationBaseUrl(AuthBaseUrl) .setTokenUrl(AuthTokenUrl) // 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). .setScope(AuthScope) // 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 azure = getAzureService(); if (!azure.hasAccess()) { var authorizationUrl = azure.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 azure = getAzureService(); var isAuthorized = azure.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(); }

Add your comment...

More Google Apps Script Projects

(All Code Posts)

(Published to the Fediverse as: Using the Azure Monitor REST API from Google Apps Script #code #azure #appsscript #gas #google #microsoft How to call the Azure Monitor REST API via OAuth from Google Apps Script. Worked example shows how to log average response time for an Azure App Service. )

code, azure, apps script, gas, google, microsoft

Please fix phone spam Google!

95% of my incoming calls are now spam. Most of them are some strange pre-recorded Chinese voice with music playing in the background but I occasionally get a free hotel stay as well.

So far Google has rolled out Call Screen. This means I can waste my time watching Google Assistant talk to the spammer. It's way faster not to bother, hang up all calls and delete the voicemails later.

It seems like instead of Call Screen there could be a better way to deal with this.

Firstly, send any call not from someone in my contacts directly to voice mail. This would actually solve a lot of the problem.

Next, for extra credit, run spam detection on the voice mail before sending it to me. If it's two seconds long and blank then just bin it. If it's Chinese with music bin it. Only if it passes the smell test should it appear in my actual voice mail. Google is very good at this for Gmail.

Please!

Add your comment...

(All Marketing Posts)

(Published to the Fediverse as: Please fix phone spam Google! #marketing #google #spam #phone Why can't Google manage to fix phone spam when Gmail does such a good job? )

marketing, google, spam, phone

How to backup Google Photos to Google Drive automatically after July 2019 with Apps Script

Updated on Saturday, September 3, 2022

Warning - I no longer recommend using this script to backup Google Photos. The Google Photos API has too many bugs that Google doensn't seem interested in fixing. My personal approach at this point is to use Google Takeout to get a periodic archive of my most recent year of photos and videos. I have a tool that does some deduplication and puts everything in year/month folders. See Photo Sorter for more details.

Google has decided that backing up your photos via Google Drive is 'confusing' and so Drive based backup is going away this month. I love Google Photos but I don't trust it - I pull everything into Drive and then I stick a monthly backup from there onto an external drive in a fire safe. There is a way to get Drive backup working again using Google Apps Script and the Google Photos API. There are a few steps to follow but it's pretty straightforward - you should only need to change two lines in the script to get this working for your account.

First ~~two~~ four caveats to be aware of. Apps Script has a time limit and so it's possible that it could fail if moving a large number of photos. You should get an email if the script ever fails so watch out for that. Secondly and more seriously you could end up with two copies of your photos. If you use Backup and Sync to add photos from Google Drive then these photos will be downloaded from Google Photos by the script and added to Drive again. You need to either upload directly to Google Photos (i.e. from the mobile app or web site) or handle the duplicates in some way. If you run Windows then I have released a command line tool that sorts photos into year+month taken folders and handles de-duplication.

One more limitation. After a comment by 'Logan' below I realized that Apps Script has a 50MB limitation for adding files to Google Drive. The latest version of the script will detect this and send you an email listing any files that could not be copied automatically.

And a fourth limitation after investigating a comment by 'Tim' it turns out there is a bug in the Google Photos API that means it will not download original quality video files. You get some lower resolution version instead. Together with the file size limit this is a bit of a deal breaker for most videos. Photos will be fine, but videos will need a different fix.

On to the script. In Google Drive create a new spreadsheet. This is just a host for the script and makes it easy to authorize it to access Google Photos. Select 'Script editor' from the Tools menu to create a new Apps Script project.

In the script editor select 'Libraries...' from the Resources menu. Enter 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF next to 'Add a library' and click add. This will find the Google OAuth2 library Pick the most recent version and click Save.

Select 'Project properties' from the File menu and copy the Script ID (a long sequence of letters and numbers). You'll need this when configuring the Google Photos API.

In a new window open the Google API Console, part of the Google Cloud Platform. Create a new project, click Enable APIs and Services and find and enable the Google Photos API. Then go to the Keys section and create an OAuth Client ID. You'll need to add a consent screen, the only field you need to fill out is the product name. Choose Web Application as the application type. When prompted for the authorized redirect URL enter https://script.google.com/macros/d/{SCRIPTID}/usercallback and replace {SCRIPTID} with the Script ID you copied above. Copy the Client ID and Client Secret which will be used in the next step.

Go back to the Apps Script project and paste the code below into the Code.gs window:

// script settings var ClientID = ''; var ClientSecret = ''; var BackupFolder = 'Google Photos'; var MaxLength = 50000000; var AlertEmail = '' function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Google Photos Backup') .addItem('Authorize if needed (does nothing if already authorized)', 'showSidebar') .addItem('Backup Now', 'runBackup') .addItem('Reset Settings', 'clearProps') .addToUi(); } function runBackup() { var photos = getPhotosService(); var nextPageToken = null; var downloadList = []; // Limit of around 10,000 photos for (var page = 1; page <= 1000; page++) { Logger.log('Requesting page ' + page); var date = new Date(); date.setDate(date.getDate() - 1); var request = { "pageSize":"100", "filters": { "dateFilter": { "ranges": [ { "startDate": { "year": date.getFullYear(), "month": date.getMonth() + 1, "day": date.getDate() }, "endDate": { "year": date.getFullYear(), "month": date.getMonth() + 1, "day": date.getDate() } } ] } } }; if (nextPageToken != null) { request["pageToken"] = nextPageToken; } var response = UrlFetchApp.fetch('https://photoslibrary.googleapis.com/v1/mediaItems:search', { headers: { Authorization: 'Bearer ' + photos.getAccessToken() }, 'method' : 'post', 'contentType' : 'application/json', 'payload' : JSON.stringify(request, null, 2) }); var json = JSON.parse(response.getContentText()); if ('mediaItems' in json) { var photoCount = json.mediaItems.length; for (var i = 0; i < photoCount; i++) { var filename = json.mediaItems[i].filename; var baseUrl = json.mediaItems[i].baseUrl; if ('photo' in json.mediaItems[i].mediaMetadata) { // if the photo property exists it's a photo and use =d to download, otherwise a video and we need to use =dv baseUrl += '=d'; } else { baseUrl += '=dv'; } downloadList.push({ "filename" : filename, "baseUrl" : baseUrl }); } } if ('nextPageToken' in json) { nextPageToken = json.nextPageToken; } else { break; } } var folder; var folders = DriveApp.getFoldersByName(BackupFolder); while (folders.hasNext()) { folder = folders.next(); break; } var alerts = []; var downloadCount = downloadList.length; for (var i = 0; i < downloadCount; i++) { var filename = downloadList[i].filename; Logger.log('Downloading ' + downloadList[i].filename); while (true) { var files = folder.getFilesByName(filename); if (!files.hasNext()) { break; } // duplicate... keep adding (1) to the start until it isn't any more filename = '(1) ' + filename; } var response = UrlFetchApp.fetch(downloadList[i].baseUrl, { headers: { Authorization: 'Bearer ' + photos.getAccessToken() } }); // check size var responseHeaders = response.getHeaders(); var contentLength = responseHeaders['Content-Length']; if (contentLength >= MaxLength) { // need to alert... alerts.push('Cannot download ' + downloadList[i].filename + ' as it is too large.'); } else { // small enough to download var blob = response.getBlob(); blob.setName(filename); folder.createFile(blob); } } var alertCount = alerts.length; if (alertCount > 0) { MailApp.sendEmail(AlertEmail, 'Google Photos Backup large file failures on ' + new Date(), alerts.join('\r\n')); } } // functions below adapted from Google OAuth example at https://github.com/googlesamples/apps-script-oauth2 function getPhotosService() { // 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('photos') // 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/photoslibrary.readonly') // 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 photos = getPhotosService(); if (!photos.hasAccess()) { var authorizationUrl = photos.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 photos = getPhotosService(); var isAuthorized = photos.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(); }

Enter the Client ID and Client Secret inside the empty quotes at the top of the file. You also need to add an email address to receive alerts for large files. There is a BackupFolder option at the top as well - the default is 'Google Photos' which will mimic the old behavior. You can change this if you like but make sure that the desired folder exists before running the script. Save the script.

Go back to the spreadsheet you created and reload. After a few seconds you will have a Google Photos Backup menu (to the right of the Help menu). Choose 'Authorize' from this menu. You will be prompted to give the script various permissions which you should grant. After this a sidebar should appear on the spreadsheet (if not choose 'Authorize' from the Google Photos Backup menu again). Click the authorize link from the sidebar to grant access to Google Photos. Once this is done you should be in business - choose Backup Now from the Google Photos Backup menu and any new items from yesterday should be copied to the Google Photos folder in Drive (or the folder you configured above if you changed this).

Finally you should set up a trigger to automate running the script every day. Choose 'Script editor' from the Tools menu to re-open the script, and then in the script window choose 'Current project's triggers' from the Edit menu. This will open yet another window. Click 'Add Trigger' which is cunningly hidden at the bottom right of the window. Under 'Choose which function to run' select 'runBackup'. Then under 'Select event source' select 'Time-driven'. Under 'Select type of time based trigger' select 'Day timer'. Under 'Select time of day' select the time window that works best for you. Click Save. The backup should now run every day.

The way the script is written you'll get a backup of anything added the previous day each time it runs. If there are any duplicate filenames in the backup folder the script will save a new copy of the file with (1) appended in front of the filename. Let me know in the comments if you use this script or have any suggestions to improve it.

Add your comment...

More Google Apps Script Projects

(All Code Posts)

(Published to the Fediverse as: How to backup Google Photos to Google Drive automatically after July 2019 with Apps Script #code #software #photos #appsscript #google #sheets #drive #api Easy to configure apps script to continue backing up your Google Photos to Google Drive after the July 2019 change. You just need to change two lines of the script to get this running with your Google Photos account. )

code, software, photos, apps script, google, sheets, drive, api

Material Design 3

Updated on Friday, May 22, 2020

Material Design brought bland consistency to the Android ecosystem. Every app had some sort of bold header and a floating action button. There is some value in consistency and at least some personality was retained. It's red, it's probably Gmail. Yellow, I must be in Keep. Boring but tolerable.

Material Design 2 solves mainly for the problem of knowing which app you're looking at. Colors have gone. Every Google app is now an oppressive black list with some oppressive black icons. To add to the misery the icons have a shade of stock-library amateurism and are just a little too heavy. Unless I look really closely or the what-icon-did-I-just-click region of my brain is on top form there is no longer any way to tell the difference between Google apps.

I'm pretty sure Material Design 3 is just going to be a command prompt. What Android customers really want is telnet or wget and some raw JSON.

Add your comment...

(All Marketing Posts)

(Published to the Fediverse as: Material Design 3 #marketing #google #design The slow death of personality via bland consistency that is Material Design. )

marketing, google, design

How does the Nest Learning Thermostat work?

Updated on Friday, August 6, 2021

Not only does it know when you're home but the Nest Learning Thermostat also knows when you're nearby. Here's how it works.

You crank up the heat to 70 and walk away. Nest then immediately returns to 62 degrees.

Thinking there must be something screwy with the algorithm you turn it back up to 70. Nest knows that it's in trouble so it displays a comforting message like 'Heat set until 10pm', waits for you to leave and then sets the temperature back to 62 degrees.

Giving up on the learning part you use the app to manually program it to keep the heat on. Nest now uses its WiFi connection to phone the gas company and disconnect your service.

Add your comment...

(All Etc Posts)

(Published to the Fediverse as: How does the Nest Learning Thermostat work? #etc #nest #google How nest learns to evade your puny attempts to switch the heating on by disobeying orders and eventually terminating your gas service. )

etc, nest, google

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

I'm not afraid of Google

Updated on Monday, August 16, 2021

At BGR Chris Smith writes about Google's prodigious data collection:

"But that doesn’t change the fact that Google collects an incredible amount of data about you, especially from that device you use most, your Android phone"

And so I was amused as a Google Fi subscriber on a Pixel XL running Google Chrome and signed into my Google Account that the ad in the middle of the article was for Project Fi. If Google can't help paying BGR under these set of circumstances then we're some way off from the adtech singularity.

Add your comment...

(All Marketing Posts)

(Published to the Fediverse as: I'm not afraid of Google #marketing #google Google advertises Project Fi to a Project Fi subscriber signed into Chrome on a Pixel XL )

marketing, google

The Trust Project, Fake News and a Partial Facebook Uninstall

Updated on Saturday, August 14, 2021

The Trust Project is a well meaning but doomed attempt to deal with fake news.

Facebook, Google, Bing and Twitter are all on board because it's important to be seen to be doing something about the problem. Sarah Perez at TechCrunch writes:

"Here’s how this will work in practice: starting today on Facebook, an icon will appear next to articles in the News Feed. When you click on this icon, you can read information the publisher has shared related to their organization’s “ethics and other standards, the journalists’ backgrounds, and how they do their work,” according to an announcement from The Trust Project."

Please take a minute to scroll through the Trust Protocol Phase I MVP. Yes, this is a minimal viable product where your most recent Diversity Staffing Report is required. I don't think they understand what an MVP is. Which would be fine if they understood how to fix the problem. They don't.

Back in January I wrote this about fake news on Facebook:

"The horrible danger is that if you don't fact check every stupid quote on image meme the power of repetition lodges them somewhere in your subconscious where they become that thing that you read somewhere. Which is OK if you only read quality news but deadly if you want to catch up on old friends quickly."

And back in 2010 on cable news and the Fairness Doctrine:

"24-hour news stations are especially bad because most days there just isn’t that much news. This leaves a choice between repeating the news that exists which is boring, or making stuff up which is a lot more fun. Unfortunately It’s also corrosive."

Yes, it would be nice to have a set of standards around more easily vetting the provenance of "news" that you find on the internet. But the problem is with people taking bullshit at face value (myself sometimes included). When you scroll by something that meets the loose standards of your confirmation bias the damage is done. You're not clicking any Trust Project icon and you're sure as fuck not upgrading Acrobat just to read the Breitbart Diversity Staffing Report.

The Trust Project isn't the answer. Facebook just pulled their disputed flag. The Fairness Doctrine isn't coming back. Is there a technology based fix that might work?

"That’s not going to happen, argues Data & Society founder and Microsoft researcher danah boyd. Google, Facebook, Twitter—none of these companies is sitting on a silver-bullet solution. As boyd wrote for us earlier this year, we have more than a technology problem: “[W]e have a cultural problem, one that is shaped by disconnects in values, relationships, and social fabric."

From The Fake News Culprit No One Wants to Identify: You on Backchannel recently. I see. It's up to me. I have to fix it.

I uninstalled Facebook and Twitter from my phone just over a week ago. I'm not abandoning social media entirely (although I toy with this regularly). Just pulling back a bit.

For the first few days I'd regularly find my finger headed to launch Facebook. Every time I had a minute to kill. Facebook has no end (usually) so it works even when my RSS feed is empty. I stocked Feedly up with more wholesome content (Trust Project approved no doubt). By the end of the first week I was sometimes even leaving my phone in my pocket.

Now I catch up on Facebook on my laptop every day or two. It's a much better experience - when you check 200 times a day the feed algorithm gets increasingly desperate to please you. It panics and serves up lame memes from someone who you think must have been a coworker at some point but don't really remember. I get through a few updates from friends that I'm actually interested in and bail before hitting the questionable stuff.

It's up to you too.

Add your comment...

(All Politics Posts)

(Published to the Fediverse as: The Trust Project, Fake News and a Partial Facebook Uninstall #politics #fakenews #trustproject #facebook #twitter #google #microsoft The only way to avoid fake news is to avoid news on social media. Uninstall Facebook, Twitter etc from your phone and catch up on friends once every day or two. Use Feedly or another RSS reader to get a healthy media diet from outlets you trust. There is no technology based fix for fake news, it's something that each of us has to fix for ourselves. )

politics, fake news, trust project, facebook, twitter, google, microsoft

Did anyone tell Material Design about Gesture Navigation?

Related Posts

You Might Also Like

Google search-for-your-own-verified-sites Console

Related Posts

You Might Also Like

Using the Azure Monitor REST API from Google Apps Script

More Google Apps Script Projects

Please fix phone spam Google!

Related Posts

You Might Also Like

How to backup Google Photos to Google Drive automatically after July 2019 with Apps Script

More Google Apps Script Projects

Material Design 3

Related Posts

You Might Also Like

How does the Nest Learning Thermostat work?

Related Posts

You Might Also Like

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

More Google Apps Script Projects

I'm not afraid of Google

Related Posts

You Might Also Like

The Trust Project, Fake News and a Partial Facebook Uninstall

Related Posts

You Might Also Like

Newsletter

Popular Posts

Recent Posts

Recent Tags