How can we help?

Integrating with Google Sheets

Ninjacat has a very strong integration with Google sheets. Any data that you can get into a Google Sheet can be included as a table in your NinjaCat report. There are many clever and automated ways to get data into a Google Sheet using apps such as Zapier and Supermetrics, opening the possibility to easily incorporate data from sources such as Pinterest, SalesForce, Zillo, etc.

Get started by attaching a Google sheets account in the Networks tab

Connecting a Google Sheets Network

1. Click on your name in the top right corner, then under "Data Sources" click Add Network  and select "Connect" listed under Google Sheets.



2. Open another tab in the same browser and log in to the Google Drive/Sheets account you wish to connect. 


3. Go back to your NinjaCat tab and type a unique description to name this Google Sheets connection, then click "Add Network"

4. You will automatically be directed to a Google permissions page to authorize NinjaCat and allow access. Click "Allow":

After you click "Allow", you will be automatically redirected to NinjaCat to continue adding your Google Sheet to your client accounts.  

5. Click on the Accounts tab and search and click into the account you wish to report on using your google sheet.  Once in the account, click on the "Add Data Sources" button, and as before, select "Connect" to connect Google Sheets to the account:



6. Select the Google Sheet network from the top of the drop-down list and configure your google sheet.  The right-hand side of this screen has help text that can guide you as to what each option in this configuration represents:


7. You can add multiple google sheets data sources to an account by giving each sheet a unique Sheet ID.  Currently you are able to add up to 10 unique google sheets to each account. 


8. Once you have connected your Google Sheet to your accounts, you are ready to add Google Sheets to your template.  For more information, see:

  1. Adding Google Sheets widgets to shinobi reports (under the tab Reporting - Templates (Beta))
  2. Adding Google Sheets widgets to standard reports (under the tab Reporting - Templates)


Google Sheet Requirements and Best Practices

Your Google sheet should adhere to the following requirements:

  • Column headings must be in row 1.
  • Recommended limit of 10 columns total. This is not a hard/fast limitation, but if you have more than 10 columns the resulting table in your NinjaCat report will start to look very crowded.
  • No blank or skipped rows. Your NinjaCat report will include consecutive rows in a Google sheet up until the first row with no content in the first cell/column.
  • All "Date" metrics/dimensions must be formatted as a Google Sheet Date.  Any other numerical data where calculations are to be performed must also be formatted as a number, not as a text string.
  • Formatting (e.g. color, bold, text alignment) in your Google sheet will not carry through to your NinjaCat report. All cells will be center aligned (standard) or left-aligned (shinobi) unless otherwise specified (see below).
  • Cell alignment in tables (standard):
    • You can explicitly control the text alignment of a field in a google sheets table by adding a modifier to the header row:
      • Header|left
      • Header|right
      • Header|center


Getting Data into a Google Sheet

There are many different ways of getting data into Google Sheets, in this example "Pinterest Pins by User Data" was brought in using a Google sheet add-on called Supermetrics. Data can also be imported to sheets using hundreds of Zapier Zaps.

Once you have your data in sheets it will look something like this example below.  

Google Sheet with Pinterest Data: Figure 1

Adding the Google Sheets Widget to Your Shinobi Template
(under the Reporting - Templates (beta) tab) 

  1. Start a new template or edit an existing template under the tab Reporting - Templates (Beta).  
  2. Add a data widget to your template or click on an existing data widget to begin configuring your Sheets data.
  3. Under "Data Sources", select Google Sheets as a data source. Once added, click on the pencil icon to configure the dimensions and metrics for your Google Sheet:

  4. To report on multiple google sheets within a single account, use the Sheet Mapping and select the unique Sheet ID (number from 1 thru 10) as configured when adding the data source to your account:


  5. Begin adding all of the dimensions to your data widget by clicking on the "Edit Dimensions" button, then click "New" to add a new dimension. Some example of Dimensions that you may wish to add are: Date, Campaign Name, Keyword, Device, Country, etc:

  6. Edit the dimension and click OK to confirm your changes.  Edit the dimensions for your google sheet as follows:
      - Name:  This is the name of the dimension as it will appear in your table and in the template editor
      - Header Mapping: This should be an exact match of the header row of the matching column in your Google Sheet
      - Aggregation Mapping: If you wish to aggregate your Google Sheet data with other data sources such as Google Adwords, Bing or other networks, this is where you would select the common dimension to use for data aggregation in your widgets. 12.png
  7. After you have added all of your dimensions, click on the "Edit Metrics" button to add the desired columns from your google sheet.  As before, you can enter in the Name, Header Mapping, and Aggregation mapping for all of your metrics.  Under the "Display As" and "Calculate totals as..." drop-down menus, you can also specify the type of metric you are adding, how it should be displayed in the editor, and what operation should be used for the total rows of this metric:13.png
  8. Once all of your dimensions and metrics have been added in your Google Sheets configuration, you are ready to start adding this data to your widgets by selecting any dimensions, metrics, filters and sorting options in the same way as all other widgets:
  9. Account Grouping (Optional):To change the grouping of your widget and report on the account group instead of a single account, select "Account Group" from the grouping section.  (See How to report on multiple accounts at once for more information.


Adding the Google Sheets Widget to Your Standard Template
(under the Reporting - Templates tab) 

Once your Google Sheets network is established, you can work with the Google Sheets template widget.

  1. From the Templates tab the Google Sheets widget can be found under Miscellaneous Widgets.
  2. Under the Table section of the widget, select the Network, the Google Sheet, and specify the worksheet tab. You may also optionally specify the data columns to include in the table. If no data columns are specified, all data columns found in your sheet will be included in the table:

Google Sheet Report Template: Figure 2

Once the template is saved and the report is run, the Google sheets data from figure 1 above will look like this in figure 3:

Report Table Containing Pinterest Data from Sheets Integration: Figure 3

If the sheet is updated and the report is re-run, the sheets data will update in the report. 




Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.