This article tells you how to connect and format your Google Spreadsheets widget. If you're familiar with the widget and looking for the template, you can access the template here.
In This Article:
Adding Account + Spreadsheets
After connecting your Google Account, use the drop-down to choose the Spreadsheet you want to visualize.
After clicking "Save Widget", a pop-up window will appear; select the sheet (tab) in the spreadsheet that you would like the data to be pulled from.
Where is my Spreadsheet?
Link Permissions:
The Google Spreadsheets Widget will fail to pull data into your dashboard if "Link Permissions" on your Google Sheets file is not correctly set.
- Change sharing permissions to "On - Anyone with the Link"
- Refresh your Cyfe Dashboard
Shared Drives:
We currently do not support spreadsheets in Shared Drives.
Tip:
If you have multiple spreadsheets, consider creating a 'Cyfe' folder in Google Drive and move your spreadsheet files into this folder. Setting the permission level of the entire folder will eliminate the need to change link permissions for each file.Formatting your Spreadsheet
★ Chart Type ★
The format of your spreadsheet will need to change according to the chart type you want to create. Access / download the template below for instructions on formatting your spreadsheet.Access the Google Spreadsheets Template
Formatting Rules
Here are three important rules to keep in mind when formatting your GoogleSpreadsheet.
- Date Formatting
- Number Formatting
- Value Type ($, %)
---------------------------------------------------------------------------------------------
Date Formatting
If you want the data in your spreadsheet to align with the Date range you choose on your Cyfe (Date Picker in top navigation bar in app):
✓ Cell A1 must have the word Date:
✓ Data in Column "A" must be in YYYYMMDD format
FYI:
"Date" data has to be daily data (one date per row, no duplicates) if you want to Group by Day in the date picker.
⨂ If you don't want to use the Cyfe Date Picker (you want static data pulled into Cyfe)
- Any labels on the X-axis is okay
- Use formats like "Day", "Month", "Period" etc in Cell A1. Column A will still be your X-axis.
If you use any other label than "Date" for the first column of your data then Cyfe will simply create a chart for you, put the first column's data on the x-axis and the other columns on the y-axis.
---------------------------------------------------------------------------------------------
Number Formatting
Avoid formatting in the numbers / data series themselves. Within your Google Spreadsheet, make sure the cells are set to Plain/Automatic.
---------------------------------------------------------------------------------------------
Specify Type of Value ($, %) in Label (header)
☒ Do NOT include the type of value in the cell. For example, do not put $100, or 20% within the cell itself.
☑ Instead, append the type of Value ($, %, :) in the series (column header) label, such as:
- Revenue ($)
- Percentage (%)
- Time (:)
Additional Data Rules (optional)
In the accordion below are a list of optional parameters you can append to your GoogleSheet
Data Format Parameters:
| Optional | Description | |
| Labels | No | Required. Specify what labels you would like to use. |
| Data | No | Required. Starting from the 2nd row, append list of values for your metrics starting with the date (YYYYMMDD) as the first value in each row |
| Colors | Yes |
You can use your own color scheme for your metrics by appending a row of hexadecimal color codes to the end of your list. The first value in this row must always be “Color” Colors,#ffc15a, #000000
|
| color | Yes | ^ if you want to use just one color, |
| Cumulative | Yes |
Use this row to indicate whether you would like to see cumulate values for particlar metrics in the widget header. The first value in this row must always be “Cumulative”. Use the values “0” or “1” to indicate which metric to cumulate values for: Cumulative,1,0,0,0
|
| Average | Yes |
Use this row to indicate whether to display averages for particular metrics in the widget header. The first value in this row must always be “Average”. Use the values “0” or “1” to indicate which metric to average Average,1,0,0,0
|
| Total | Yes |
If you use “Date” as the first column label in your data, by default the widget will automatically calculate the averages for percentage based metrics or the sums for all other metrics based on the current time period and display them in the widget header. This row enables you to overwrite these values in the widget header. The first value in this row must always be “Total” Total,542,23,42,49
|
| Comparison | Yes |
If you use “Date” as the first column label in your data, by default the widget will automatically calculate percent changes for each metric by comparing the current time period against the last based on the current time period and display it in the widget header. This row enables you to overwrite these values in the widget header. The first value in this row must always be “Comparison”: Comparison,-10,50,42,0
|
| Reverse | Yes |
Use this row to indicate whether high values are good or bad for particlar metrics (e.g. high “unlikes” count on Facebook is bad). This row is used to calculate percent differences (comparisons) in the widget header. The first value in this row must always be “Reverse”. Use the values “0” or “1” to indicate which metric to reverse Reverse,1,0,0,0
|
| ReverseGraph | Yes |
Use this row to indicate whether you want to display a graph upside down (e.g. when displaying ranking information). The first value in this row must always be “ReverseGraph”. Use the values “0” or “1” to indicate which metric to reverse the graph for: ReverseGraph,1,0,0,0
|
| YAxis | Yes |
By default each metric you add to the widget falls on its own y-axis. This row enables you to use a single y-axis across all your metrics in the widget thereby syncing the data. The first value in this row must always be “YAxis”. Use the value “0” for all metrics to sync them YAxis,0,0,0,0
|
| YAxisMin | Yes |
The minimum value of the y-axis. By default this value is “0” for each metric. The first value in this row must always be “YAxisMin” YAxisMin,0,20,100,0
|
| YAxisMax | Yes |
The maximum value of the y-axis. By default this value is automatically calculated for each metric. The first value in this row must always be “YAxisMax” YAxisMax,1000,2000,500,600
|
| YAxisShow | Yes |
By default all y-axes are hidden. This row enables you to turn on the y-axis for a particlar metric. The first value in this row must always be “YAxisShow”. Use the values “0” or “1” to indicate which metric’s y-axis to show YAxisShow,1,0,0,0
|
| LabelShow | Yes |
By default all data labels are hidden. This row enables you to turn on the data labels for a particlar metric. The first value in this row must always be “LabelShow”. Use the values “0” or “1” to indicate which metric’s data labels to show LabelShow,1,0,0,0
|
Common Questions:
My widget is not scaling correctly
How do I locate my Google Sheet in Google Drive?
-
Navigate to your Google Sheets Widget
-
Click on Configure
-
Copy the 25 Character Spreadsheet ID
-
Create a New Google Sheet
- Overwrite the 25-character spreadsheet ID in the URL with the ID from your Widget
- The spreadsheet ID comes after "/spreadsheets/d/" in the URL
- Here is an example: https://docs.google.com/spreadsheets/d/abcdefghijklmnopqrstuvwxy
Comments
0 comments
Article is closed for comments.