Articles in this section

Setup Guide: ActivTrak for Google Sheets Integration

Unlock the power of ActivConnect and access your ActivTrak data without the need for advanced SQL or BI knowledge. ActivConnect (Powered by BigQuery) leverages the native integration between Google BigQuery and Google Sheets to simplify access to your data. You’ll be able to access, analyze and visualize millions of rows of data in a simple way, allowing you to leverage the tools and techniques you are already familiar with, such as formulas, charts and pivot tables, while maintaining the data current through scheduled refresh options. 

ActivConnect is available as an add-on to customers on a paid subscription plan. Learn more about ActivConnect here.

Getting Started

Note: To protect your data, ActivConnect restricts data sharing outside the activtrak.us domain. The additional steps outlined here will guide you through setting up the permissions so that you can grant data access to anyone within your organization.

  1. Navigate to sheets.google.com and ensure you are logged in under your company’s Google account.
  2. Create a new Google Sheet document 
  3. Click the Share button in the upper right corner and provide edit permissions to your ActivConnect user account. This will be in the format of account_id@activtrak.us. Your permissions will look like the image below.

Sheets-1.png

Connect Your Data

  1. In a separate tab or incognito window, ensure you are logged in with your ActivConnect user account. This will be in the format of account_id@activtrak.us
  2. Navigate to sheets.google.com to access the shared document created in the Getting Started steps above. 

Navigate to Data > Data Connectors > Connect to BigQuery to set up the BigQuery connection.

sheets-2.png

 

The navigation is structured in the following way: 

Project: “us-activtrak-ac-prod”
Dataset: “xxxxxx” - Your ActivTrak account number
Table or View: This is the list of available tables in your ActivConnect account. For more information about the content of these tables, please refer to our data glossary

sheets-3.png

 

Here you have two options:

  1. Select the desired view and get all the content available in it.
  2. Write a custom SQL query to only populate the necessary columns. 

In this exercise, we will use the Write custom query option to create a custom Working Hours Report based on the daily_user_summary view

  1. Select Write custom query and copy and paste the query below in the big query editor window.
  2. Replace “xxxxxx” with your ActivTrak Account ID. The Connect button will be enabled once the query is properly edited. 

sheets-4.png

 

  SELECT

   local_date,

   day_type,

   user_name,
  
   utilization_level,
  
   first_activity_datetime,

   last_activity_datetime,

   SUM(productive_active_duration_seconds+productive_passive_duration_seconds)/3600
  AS productive_hours,

   SUM(unproductive_active_duration_seconds+ unproductive_passive_duration_seconds)/3600
  AS unproductive_hours

  FROM

   `us-activtrak-ac-prod.xxxxxx.daily_user_summary`

  GROUP
  BY

   1,2,3,4,5,6

 

Upon success, your data will show as a new sheet in the document.

sheets-5.png

 

You can now revert to the original Google account for continued access and analysis of the data. If you need to make changes to the data source or add additional views or queries, repeat the steps outlined in the Connect Your Data section of this document.

Note: Although the spreadsheet shows a preview of only 500 rows, any pivot tables, formulas, and charts will use the entire set of data. You can also extract the data to a sheet. For more information, see the Connected Sheets tutorial.

For additional assistance, please reach out to ActivTrak Support.

Was this article helpful?
1 out of 1 found this helpful