Welcome. How can we help?

Google Spreadsheets

Tomonori Igari
  • Updated
Follow

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:

  1. Follow the steps for "Adding a Widget."
  2. When the widget window appears, select "Configure Widget."
  3. 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.
  4. 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. 

mceclip0.png

Add Additional Accounts

To add another account, click on the Google Account dropdown, and click on the white-space under your existing account:

AddGAAccount.png

Link Permissions

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:

  1. Open your Google Spreadsheet
  2. 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.
  3. Click "Advanced"
  4. Select "Change"
  5. Choose Option "On- Anyone with the Link"
  6. Save
  7. Navigate to your Cyfe Dashboard and Refresh Webpage
  8. 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 

mceclip1.png

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. Simple as that!)

Number Formatting

Avoid formatting in the numbers / data series themselves. Keep them Plain/Automatic using the "Format" menu.

 blobid0.png

Specify Type of Value ($, %) in Label (header)

Append Type of Value ($, %, :) in the series 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:

 

I Connected My Google Sheet and My Widget Doesn't Work

This is likely due to formatting issues between your Google Sheets document and the Cyfe Widget. Please see our formatting tips at www.cyfe.com/custom. See also the Tips and Tricks above.

How do I locate my Google Sheet in Google Drive

If you have trouble locating your Google Sheet to change its Link Permissions:

  1. Navigate to your Google Sheets Widget

  2. Click on Configure

  3. Copy the 25 Character Spreadsheet ID

  4. Create a New Google Sheet

  5. Overwrite the 25-character spreadsheet ID in the URL with the ID from your Widget

 

Related articles

Comments

0 comments

Article is closed for comments.