Following the instructions below, you can send TrueClicks data to Google Sheets.
Prerequisites
You need a TrueClicks data connector key to access your data.
Log in to TrueClicks and go to the Reporting page.
Scroll down to the Data Connector section, enter a key of your choice, and click the Create New Key button.
This will generate a combination of Username and Token that will be used later in your new Google Sheets doc:
Google Sheets setup
Create a new Google Sheet
Go to Extensions > Apps Script
Copy & paste this ImportJSON script and click Save
Enter the following formula in the A1 cell of an empty sheet:
=ImportJSONBasicAuth("https://data.trueclicks.com/odata/v3/auditresultview", "{Username}", "{Token}")Replace {Username} and {Token} with your data connector key credentials from the above steps. Make sure you also remove the {} brackets in your formula:
Example:
=ImportJSONBasicAuth("https://data.trueclicks.com/odata/v3/auditresultview", "ppc_team_dashboard", "a4a7b2a0-e123-49ae-a1b9-7dcdabaacf01")
Now, your Google Sheet is populated with TrueClicks data.
Additional data sets
There are 3 different data sets you can import into Google Sheets - next to account-level data (used above), you can also import check-level data and user data.
Data set | URL | Description |
Account-level data | The latest aggregated account data, including account TrueClicks score, the number of monitoring issues per category, and the number of auditing results per category. The same data you see in the all accounts overview. | |
Account-level data (historical) | It is the same as "Account-level data" but includes weekly historical values up to 1 year back. | |
Check-level data | The latest calculated score or value for every monitoring and auditing check. The same data you see when you open a single account audit. | |
User-level data | Active users and pending invitations. | |
Task-level data | Tasks from Task management | |
Target monitoring (active) | Active target monitoring periods | |
Target monitoring (all) | All target monitoring periods (including past and future periods) |
To import a different data set into Google Sheets, create an empty sheet within the same Google Sheet doc and repeat step 4 of the Google Sheets setup section, using a different URL from the table above:
=ImportJSONBasicAuth("{URL}", "{Username}", "{Token}")