Google Sheets

This script allows you to import AT Internet data within Google Sheets.
Once you've installed the script, you are able to call any Reporting API feed.
Please note that you have to have an AT Internet account to query the API.

Benefits

If you were used to use AT Internet data within Excel but fed up with the .iqy files, you'll be thrilled to use this script to manipulate the AT Internet data within Google Sheets.

Getting started

You can download the script with the download button down below, if you want to propose improvements you can push new contents on the GitHub repository here.

Category: Analysis

Description

Import AT Internet data to Google Sheets

This script allows you to import AT Internet data to Google Sheets.

Installation

In order to install it, follow these steps:

Open your Google Sheet, and go to Tools > Script editor…
Create a new script (File > New > Script file) and paste the code from atinternet.gs to it, and save the script
Go back to your sheet (it should reload). You should see a new menu on top (next to Accessibility), called AT Internet
Click on AT Internet > Login, you’ll get permission alerts – accept it, and connect to your Google account (this is because the authentication data are linked to your Google account)
Enter your AT Internet email and password when prompted, and confirm

You are now connected to your AT Internet account on this Google sheet. In order to logout, just click on AT Internet > Logged in as …

Usage

In any cell of your sheet (this will be the top-left cell of the imported data table), use this formula:

=ImportATInternetBasicAuth("YOUR API URL")

Example:

=ImportATInternetBasicAuth("https://apirest.atinternet-solutions.com/data/v2/json/getData?&columns={d_source,m_visits}&sort={-m_visits}&space={s:591420}&period={R:{D:'-1'}}&max-results=50&page-num=1")

8 reviews for Google Sheets

Alexander Grießer – February 23, 2018

This sounds like a great idea. However, the login does not work for me.

All I get is: “Error
SyntaxError: Expected : found , (line 126).”
- bdiolez – February 23, 2018
  
  Hello, I just pushed a new version. This should fix your issue!
  Thanks for reporting it.
  BD
Yann Guégan – February 23, 2018

I have the same error : https://www.dropbox.com/s/ow0zbzo78wsgs00/Capture%20d%E2%80%99%C3%A9cran%202018-02-23%20%C3%A0%2013.00.10.png?dl=0
- bdiolez – February 23, 2018
  
  Hello, I just pushed a new version. This should fix your issue!
  Thanks for reporting it.
  BD
Merino – February 26, 2018

It seems perfect seems I had enough of iqy in excel. However there is no reload of the sheet and no new menu on top (what’s missing ?) thanks
- rbalangue – March 2, 2018
  
  Hi Pilar,
  Have you managed to reload the Sheets to get access to the new menu?
Chen – February 28, 2018

Is it possible to read input values from the google sheet? e.g. https://apirest.atinternet-solutions.com/v2/html/getData?&columns={d_time_year,d_time_month,cl_377912,m_page_loads}&sort={d_time_year}&space={s:512345}&period={D:{start:'[“Start”,”Please enter a start date in the format YYYY-MM-DD”]’,end:'[“End”,”Please enter an end date in the format YYYY-MM-DD”]’}}&max-results=1000&page-num=1&sep=dot
- rbalangue – March 2, 2018
  
  Hi Chen,
  I guess it is, we will add this to our to-do list.
  thanks.
Prescillia Dethelot – March 2, 2018

I put my API url but when i want executed the code this message appear “Paramètre formel manquant. (ligne 165, fichier “Code”)Ignorer”
- rbalangue – March 2, 2018
  
  Hello Priscillia, would you be able to share your document with support@atinternet.com? Thanks.
Christiane – March 12, 2018

Hello,
Thank you for this evolution . But I met some problems with it :
The script worked very well yesterday, over a fixed period of time:
period={D: {start:’ 2018-02-19′, end:’ 2018-03-10′ }}

But this morning, several queries were displaying an #ERREUR message as
“an internal error prevented the execution of the function”.

I tried to reload the google sheet, to reload each call, to log out, to relog in.
It wasn’t working.
Then I changed the period : period={D: {start:’ 2018-02-19′, end:’ 2018-03-11′ }}.
This made it possible to load some requests but not all of them.

Then i cut each API call from each cell, without changing anything in the query.
And i re-pasted each one. it worked.
I guess there is an issue of “cache” refresh ?
Any idea ?

This script is not far from being completed for a first data sharing.
thank you for your return.
Ana – August 2, 2018

Hello,

I always receive the same error message while trying to execute a request in Google spreadsheet :

Request failed for https://apirest.atinternet-solutions.com/data/v2/json/getData?columns=%7Bd_time_date%2Cm_visits%2CCM_47249%7D&sort=%7B-m_visits%7D&space=%7Bs%3A562498%7D&period=%7BD%3A%7Bstart%3A'2017-09-18'%2Cend%3A'2018-07-02'%7D%7D&max-results=50&page-num=1 returned code 401. Truncated server response: {“ErrorCode”:1001,”ErrorMessage”:”Incorrect login or password”} (use muteHttpExceptions option to examine full response). (ligne 159).

Even password & login are ok.
Pilar – September 28, 2018

me too ! besides it doesn’t work every time we opne the file. Even password & login are ok.