This article tells you how to connect and build your first Google Spreadsheets widget. In addition, you'll learn how to format the spreadsheet.
In This Article:
How to Configure Widget
Perform the following steps to configure your widget:
- Follow the steps for "Adding a Widget."
- When the widget window appears, select "Configure Widget."
- Enter the following information:
- Widget Title: Title you would like displayed for the widget.
- Google Account: Provides access to the data being gathered by your account.
- Spreadsheet: Select the file you would like the data to be pulled from.
- Chart Type: Select how you want the data to display.
- Select "Save Widget."
After you select "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.
Add Additional Accounts
To add another account, click on the Google Account dropdown, and click on the white-space under your existing account:
The Google Spreadsheets Widget will fail to pull data onto your dashboard if "Link Permissions" on your Google Sheets file is not correctly set. To change "Link Sharing" permissions:
- Open your Google Spreadsheet
- Select File > Share (or select the "Share" button on the right hand side of the page.)
- You can also adjust your sharing permissions by right-clicking on the spreadsheet from your Google Drive.
- Click "Advanced"
- Select "Change"
- Choose Option "On- Anyone with the Link"
- Navigate to your Cyfe Dashboard and Refresh Webpage
- If the Google Sheet Widget still does not populate, delete the widget and re-add. Additionally, make sure your spreadsheet is in alignment with our Spreadsheet formating instructions.
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 permission for each file.
Spreadsheet Example Template
Here is a template with instructions on formatting your spreadsheet. Download for instructions:
Important Rules: Date Formatting
To utilize the Cyfe Date Picker (Date Picker in top navigation bar in app):
✓ Cell A1 must have the word Date:
✓ Data in Column "A" must be in YYYYMMDD format
"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. Simple as that!)
Avoid formatting in the numbers / data series themselves. Keep them Plain/Automatic using the "Format" menu.
Specify Type of Value ($, %) in Label (header)
Append Type of Value ($, %, :) in the series label, such as:
Additional Data Rules (optional)
In the accordion below are a list of optional parameters you can append to your GoogleSheet
|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|
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”
|color||Yes||^ if you want to use just one color,|
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:
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
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”
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”:
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
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:
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
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”
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”
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
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
I Connected My Google Sheet and My Widget Doesn't Work
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