Google Sheets: connecting to your spreadsheets

If you're frequently organizing important company data using Google spreadsheets, our Google Sheet integration is a great way to dynamically represent this data within AppInsights.

You’ll just need to connect your Google account, select the data format and visualization type you’d like to use, and then select the appropriate spreadsheet cell(s) that you would like represented within AppInsights.  Any time you make a change to the underlying data in your Google Sheet, your connected AppInsights widgets will automatically update to reflect this change!

For users that are new to our Google Sheet integration, we recommend you read this tutorial in full.  If you're already an experienced user but simply need a refresher on using one of our available Google Sheet widgets, please use the links below:
 


SINGLE NUMBER

To get started, navigate to the Google Sheet tile within our Services panel (blue “+” button).

google sheet 1

You’ll immediately be presented with a selection of data formats: Single Number, Table, XY Graph, Leaderboard, Line Graph, and Multi-Bar.  Once you’ve selected the appropriate data format, you can then choose from a number of visualization options.

google sheet 2

For our example, let’s use the basic “Single Number” option and then select the standard number widget to represent our data.

google sheet 3

google sheet 4

Once you’ve deployed your new number widget to the dashboard, you’ll need to authenticate your Google Sheet account (if you haven’t already).  To do so, hit the gear icon at top right of your new widget and select the “Edit Widget Settings” menu.  From this menu, hit the blue “Connect” button and a pop-up window will invite you to enter your Google credentials and extend access permissions to AppInsights.  You will only need to do this once…

google_sheet_5_02

google sheet 6

Once you’ve connected your account, you’ll notice that the “Data Settings” tab has populated with a new set of options.  You can now choose from any of the spreadsheets within your connected Google account.  Once you select a spreadsheet, you can then choose an individual sheet (or tab) within that spreadsheet along with the specific cell that you would like represented within AppInsights.  (You can also add a prefix or suffix to accompany your data point.)

google sheet 7

As we’re using a basic number widget for our example, all we need to do is select a single spreadsheet cell that contains data and your widget will populate.  In our example, we'll use the "Cell" dropdown to select the second column, second row of our spreadsheet -- B2.

Google Sheet
google sheet 8

AppInsights
google_sheet_9_02

You’re done!  Whenever the data changes in your selected cell, your widget will update with the new value.  This is particularly useful if you’re looking to track the value of a data-point over time.  Just select the “Single Number” data format, choose our Line Graph or Sparkline widget, and AppInsights will automatically reflect any changes as your data point updates over time.

Please note: the “Single Number” data format is the only use of our Google Sheet integration that will require you to select a single spreadsheet cell.  For the rest of the tutorial, we’ll be providing instructions on widgets that connect to a data set (or range of cells) rather than a single cell value.

TABLE

Our table widget is a great way to represent columns of text and/or data that are stored within your Google Sheet.  Start by selecting the “Table” data format and selecting our standard table visualization.  If you’ve already connected your Google Sheet account, you’ll be asked to select your spreadsheet, sheet tab, and a range of cells to populate your table.  If your spreadsheet contains header data -- i.e. names for each column -- you’ll want to check the “First Row is Header” box so your top row is not treated like standard data.

google sheet 10

As our table widget will expect a range of spreadsheet cells, you’ll need to specify the top left cell and the bottom right cell of the data set.  This means that you’ll need to organize your spreadsheet data in adjoining columns, otherwise you’ll end up with empty cells within your data set, which AppInsights will be unable to recognize.

For our table example we’ll select from A1 to C19, which will populate our table widget with three columns, each with 18 rows of data (header row not included).

Google Sheet
google sheet 11

AppInsights
google_sheet_12_02

XY GRAPH

The XY graph empowers users to plot two columns of numerical data along an X and Y axis.  (Please note: our XY graph will not recognize dates -- if you’re looking to track a data point over time, please use our Line Graph or Sparkline widgets).

Similar to the table, our XY graph visualization will require you to select a range of cells.  Once again, you’ll want to specify your spreadsheet, sheet tab, and the top left / bottom right cell of your data range.

google sheet 13

When using the XY graph, do not include any column headers used to label your data as these will prevent your data range from being represented properly (i.e. we ignore “Users” and “Pings” pictured below).

Google Sheet
google sheet 14

AppInsights
google_sheet_15_02

LEADERBOARD

Our Leaderboard data format will allow you to choose from a number of visualizations -- Leaderboard, Pie Chart, Bar Graph -- each of which is intended to rank the comparative values of a data-set.  For our example, we’ll be using the standard leaderboard widget which will automatically rank the values of our data set from highest to lowest.

Start by selecting your spreadsheet, sheet tab, and cell range (top left / bottom right).  As with the XY graph, do not include any column headers used to label your data as these will prevent your data range from being represented properly.

google sheet 16

Google Sheet
google_sheet_17_02

AppInsights
google_sheet_18_02

LINE GRAPH

Selecting our line graph data format will allow you to populate either our Line Graph or Sparkline widgets with historical data, as opposed to selecting a single spreadsheet cell that is tracked over time. (Please note: the other widgets listed in this category -- i.e. number, horizontal / vertical bar, dial -- will function identically to their “Single Number” counterparts.

To get started, select the Line Graph or Sparkline widget, then choose your spreadsheet and sheet tab of choice, along with your range of cells.  (You may ignore any column headers.)

google sheet 19

Please note that for these widget types, the AppInsights app will expect you to pass through a date that corresponds to your data (see below screenshots).  With this in mind, you must be sure that your widget settings are consistent with the time range of data that you’ve selected.

In our example, we’ve provided the most recent 12 months of sales data.  To properly represent this in our widget, we need to navigate to the “Widget Settings” tab and select “Past Year” as our Time Period.

google sheet 20

Google Sheet
google_sheet_21_02

AppInsights
google_sheet_22_02

MULTI-BAR

Your final data format / visualization option is our multi-bar graph.  If you have a more complex data set and need to plot multiple values against the same x-axis categories, this is the visualization option you’re looking for.

For starters, you’ll want to make sure your data set is arranged to cooperate with our multi-bar visualization.  Start by arranging all of your x-axis value categories on the left side of your data range (you can think of these as unique titles for each of your rows).  Next, arrange your data sets as unique columns that correspond to each of your x-axis categories.

In our example, we’re tracking the number of unique drip emails that were sent over the course of three months.
google sheet 23

You’ll notice above that there is a placeholder cell that lists “Multi-bar” in the top left corner of our spreadsheet.  Please note: in order for your multi-bar visualization to function properly, this cell needs to be selected as the top left bound of your data set -- it cannot be empty.

Once you’ve properly formatted your data set, follow the standard steps of selecting your spreadsheet, your sheet tab, and the range of cells you would like represented.  (Please view the spreadsheet sample below for clarity on the appropriate top left / bottom right selections).

google sheet 24

Google Sheet
google sheet 25

AppInsights
google_sheet_26_02

***A final note on Google Sheet data updates: while AppInsights will automatically reflect any change to an individual data cell or range of cells that has been connected to your widgets, the app will not detect any NEW data that has been added to your sheet.  If, for example, you add a new row to a data set that is connected to a leaderboard or line graph, you will need to modify your widget settings to include this new data if you want it represented within your AppInsights widget(s).

Was this tutorial helpful?  Please take a moment to submit your “Yes” or “No” answer below!  You can also contact us or post a public question for further instruction.

Did you find this article helpful?