The BetterWorks integration with Google Sheets can be used to automatically track progress on any activity recorded in Google Sheets. Using Google authentication, a BetterWorks user has access to any of their existing Google Sheets and can select a single cell to be automatically updated on any of their milestones in BetterWorks. Check out our Blueprint for tips on how to get the most value from this integration:
Here's our video on how to set up this integration in sixty seconds but feel free to find more detailed set up instructions and troubleshooting information below.
Setting Up the Integration
Once an Admin in your organization has activated the Google Sheets integration, you can select the integration while creating a new goal or you can add it to an existing milestone.
If you're creating a new goal from the Goal Create page, you can add the integration when you define any of your milestones. You'll see Google Sheets icon on the right-hand panel:
If you'd like to add the Sheets integration to an existing milestone, navigate to the Goal Detail page and select the "Set Target Metric" or "Edit Target Metric" option from the Action drop-down on the milestone you want to integrate:
Both of these will open the integration interface which will prompt you select your Google account for your first log in. The Google account selected will determine which sheets you have access to based on the permissions of that account (if you can't find a sheet, make sure you have permission to access it in Google Sheets first).
Important: This BetterWorks integration is only available in our production environment and is not available in demo, trial, or preview environments and must be activated by an Admin initially before it will be visible for normal users.
Selecting Data to Integrate
The Google Sheets integration interface (accessed either from Goal Create/Edit page or from the "Set Target Metric" feature located in the action drop-down menu of an existing milestone) allows you to search for and select the sheet you want to work with, specify the tab within that sheet, and the cell you'd like to integrate:
Once your integration has been successfully configured, you'll see a Google Sheets icon appearing on the milestone and hovering over this icon will show the time until the next sync. Any issues with the integration will appear as an error alert on the milestone (see "Issues with your Integration" below).
Important: BetterWorks will track any value in the selected cell but if the spreadsheet is edited and selected value is moved to a different cell, this milestone will need to be updated manually.
Updating an Integrated Milestone
The Google Sheets integration will update automatically every hour - you can check when the next scheduled sync will occur by hovering over the green Sheets icon on your milestone:
If you've made an update on your Google Sheet that you need to see reflected in BetterWorks immediately, use the Action drop-down (the '...' icon) on your milestone and select "Sync":
Note: you will also have an alert that your milestone has not synced automatically when it is first created:
This is because the hourly sync has not yet occurred during it's lifetime - simply wait or use the manual sync option to clear this alert. If it persists more than an hour, then this may indicate a connectivity issue to your Google account.
Editing an Existing Integration
To make a change to an existing integrated milestone without making any changes to it's parent objective, use the Action drop-down on the milestone and select "Edit target metric" - this will enable you to either edit (using the pencil icon) or remove the integration:
Issues with your Integration
The four types of issues that can affect your integration are:
- A "selected value is not a number" means that the cell you've selected in your integration has an alphabetical character in it which is not supported (our progress calculations only take numbers as input). Please check your sheet and make the necessary repairs.
- A "selected value no longer available" means that the cell you selected no longer has any value in it - this may be due to a column being deleted in your Google Sheet and the cell you targeted has shifted left. Check your sheet to ensure that the intended value is selected.
- A "worksheet was not found" error suggests that the worksheet has been deleted from the Google Drive. Please confirm that the worksheet still exists in Google Sheets.
- A "worksheet access denied" error tells you that BetterWorks can no longer access the worksheet that you've selected using your account. To confirm, log into Google Sheets with the account selected in BetterWorks (during your initial Google Sheets set up) and confirm that you still have access to the sheet in question. Please confirm your access using the corresponding account in Google Sheets.
Once you've tried the above troubleshooting steps and the issue persists, feel free to reach out to firstname.lastname@example.org for additional assistance.
Some of the ways the platform can alert you of connectivity issues with your integration or that it may not be updating as expected include:
- One of the above error messages will appear on the milestone alerting you that it is out of sync.
- An error on your timeline at the point where the integration began failing:
- You (as the owner of the milestone) will receive an email alerting you that the impacted milestone requires attention - you can then log into the platform to get more information.
We hope that this integration is of value and, as always, welcome any and all feedback!