My5 – Link to Google Sheets
Linking to Google Sheets to import KPI values
Importing KPI values from Google Sheets is a fast and efficient way to pull data into My5.
Sample Spreadsheet:
Once you have linked a Google Sheet, the application will automatically update all KPI values from Google Sheets every night.
You can keep a master data set in Google Sheets and have full control over your data using this option.
This update is a full overwrite operation. Make sure you keep a copy of your data.
Linking to Google Sheets assumes that Google Sheets contains the master version of your KPI values.
A Google Sheet has to be prepared before linking and uploading. We suggest the preparation takes place on a Mac or PC rather than your phone.
Google Sheets example
The KPI Values Google Sheet format has to follow that of the sample. The download link for the sample is above.
Copy the contents of the spreadsheet into your Google sheet to use the template.
The details are as follows:
1. Dashboard & KPI names – The content of the Dashboard Name and KPI Name columns must be the same as the Structure you have created
2. Dates – the date header must be in the same format as the sample you have just downloaded (obviously, the dates can be different)
From the example, you can see that the dates in the column header to the left contain Months, the dates toward the middle of the spreadsheet contain Weeks, and the dates to the right of the spreadsheet contain days.
The above date formation is not essential, but it can be helpful when arranging your data. Don’t forget that the app will only display the last six periods of data in the details view.
This means you will see the last six months in the monthly view, the last six weeks in the weekly view, and the last six days in the daily view.
Import source – Google Sheets
Once you have completed your Google sheet, open the My5 app on your Mac or PC, and click on the setting cog (top-right). Click Connect Google Sheet.
Go to the next step.
Connect to Sheets
Go to your Google Sheet and copy the url at the top of the screen. Return to the My5 app and paste the url into the Google Sheet URL box.
Take note of the warning:
Warning: Syncing a Google sheet will remove your current KPI data. Only proceed if you wish to overwrite your data
Click the Check Google Sheet button.
Choose a sheet
If you have more than one tab on your Google Sheet, you can select which sheet you want to use.
Choose the sheet containing your KPI data.
Go to the next step.
Success message
If the Google Sheet imports correctly, you will receive the following message:
Your sheet has been connected successfully and is now syncing. Your Google Sheet will then be synced every evening. Return to the import settings to change the behaviour for the sync.
Click on Return to Import Settings and go to the next step.
Google Sheets connected
Once your Google Sheet has connected successfully, you will see a connection link showing the last time the data was uploaded.
The app assumes a connection means that Google Sheets is your Master Data source. Therefore the option to connect to regular spreadsheets has been disabled.
If you need to change your KPI values in Google Sheets and do not want to wait for the regular overnight update, you can force an update by clicking the Force Sync button.
The result
A successful upload might look something like this. If you look carefully, you will see that Deliveries on Time does not have a value. This is because the date at the time this Guide was created was 31st May 2022.
If you go back to the Google Sheet, you will see the last value entered for Deliveries on Time was 14th May 2022.
The app defaults to the current day.
To see the KPI values for Deliveries on Time, click on the KPI. It will take you to the Details Screen, See below.
Details screen
(date specific)
When you reach the Details screen, you will notice that there is still a lack of data!
Click on the date selector and select an appropriate date. In our case, it is May 24th 2022.
When the correct date is selected, you will see data for six periods, in our case, six days.
Details screen
(correct date)
When the correct date is selected, you will see data for six periods, in our case, six days.