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:
To get started, navigate to the Google Sheet tile within our Services panel (blue “+” button).
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.
For our example, let’s use the basic “Single Number” option and then select the standard number widget to represent our data.
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…
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.)
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.
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.
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.
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).
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.
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).
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.
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.)
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.
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.
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).
***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).
Did you find this article helpful?