ActivTrak for Google Sheets Setup Guide
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 sharing data outside the activtrak.us domain. The additional steps outlined here will guide you through setting up the permissions in a way that allows you to grant data access to anyone within your organization.
- Navigate to sheets.google.com and ensure you are logged in under your company’s Google account.
- Create a new Google Sheet document
- 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.
Connect Your Data
- 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.
- 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.
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. Please refer to our data glossary here for more information about the content in these tables.
Here you have two options:
- Select the desired view and get all the content available in it.
- 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
- Select Write custom query and copy and paste the query below in the big query editor window.
- Replace “xxxxxx” with your ActivTrak Account ID. The Connect button will be enabled once the query is properly edited.
----------------------------------------- Custom Query -----------------------------
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
----------------------------------------- Custom Query -----------------------------
Upon success, your data will show as a new sheet in the document.
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
Comments
No comments