Botify for Google Sheets

Botify for Google Sheets allows you to easily get Botify data in your spreadsheets.
Note that, currently only a small subset of the API is supported by this SDK. Feel free to customize our macros or contributing to the project.

Install

• Open the addon page.
• Click on “Add”
• In the main menu, click on “Add-ons” > “Manage add-ons”
• For the Botify add-on, click on the “Manage” button
• Click on “Use in this document”

Templates

Macros

Every macro is prefixed by Botify, thus typing =BOTIFY in a cell formula should show you the available macros thanks to the autocompletion.

• BOTIFY_USER_LIST_PROJECTS
• BOTIFY_PROJECT_LIST_ANALYSES
• BOTIFY_PROJECT_AGGREGATE_URLS
• BOTIFY_ANALYSIS_LIST_URLS
• BOTIFY_ANALYSIS_GET_URLS_DETAIL
• BOTIFY_ANALYSIS_AGGREGATE_URLS

Get all projects belonging to a user

/**
 * Return the projects of a user
 * @param {String} apiToken Botify API token
 * @param {String} username Username of the project owner
 * @param {Number} nbProjects [Optional] Number of projects to get (defaults to 30)
 * @return {Array} The list of projects.
 */
function BOTIFY_USER_LIST_PROJECTS(apiToken, username, nbProjects) {

Get latest project’s analyses

/**
 * Return the latest analyses of a given project.
 * @param {String} apiToken Botify API token
 * @param {String} username Username of the project owner
 * @param {String} projectSlug Project's slug
 * @param {Number} nbAnalyses [Optional] Number of analyses to get (default: 20)
 * @param {Boolean} onlySuccess [Optional] List only successfully finished analyses (default: true)
 * @return {Array} The list of analyses.
 */
function BOTIFY_PROJECT_LIST_ANALYSES(apiToken, username, projectSlug, nbAnalyses)

Aggregate project URLs data

/**
 * Return the result of an aggregation for latest project's analyses.
 * @param {String} apiToken Botify API token
 * @param {String} username Username of the project owner
 * @param {String} projectSlug Project's slug of the analysis
 * @param {BQLAggsQuery} urlsAggsQuery BQL Aggregation Query to perform
 * @param {Number} nbAnalyses [Optional] Number of analyses to get (default: 5)
 * @return {Array} The result of the aggregation.
 */
function BOTIFY_PROJECT_AGGREGATE_URLS(apiToken, username, projectSlug, urlsAggsQuery, nbAnalyses)

This macro allow you to compute metrics like average depth, pagerank distribution, and much more for lastest analyses of an analyses.
You can also group URLs in order to compute the amount of visits generated by each segments, for instance..
For more information about how to define aggregation queries, please refer to the Aggregate URLs documentation.

List URLs (URL explorer like)

/**
 * Return the requested fields of a given URL
 * @param {String} apiToken Botify API token
 * @param {String} username Username of the project owner
 * @param {String} projectSlug Project's slug of the analysis
 * @param {String} analysisSlug Analysis's slug
 * @param {Range} fields Range of fields to fetch (ex A1:A4)
 * @param {BQLFilter} filter [Optional] Filter of apply on urls
 * @param {BQLSort} sort [Optional] Sort of apply on urls
 * @param {Number} size [Optional] Number of urls to retrieve (max: 1000, default: 100)
 * @param {Number} page [Optional] Number of urls to retrieve (default: 1)
 * @param {Boolean} displayTotal [Optional] Display the number of urls matching the filter (default: false)
 * @return {Array} The value of the fields
 */
function BOTIFY_ANALYSIS_LIST_URLS(apiToken, username, projectSlug, analysisSlug, filter, fields, sort, size, page, displayTotal)

This macro works the same way the URL Explorer does, listing URLs matching a filter while allowing to sort and display a list of fields. It can at most list 1,000 URLs at a time.

Get detail for some URLs

/**
 * Return the requested fields of a given URL
 * @param {String} apiToken Botify API token
 * @param {String} username Username of the project owner
 * @param {String} projectSlug Project's slug of the analysis
 * @param {String} analysisSlug Analysis's slug
 * @param {Range} urls Urls to get detail on
 * @param {Range} fields Range of fields to fetch (ex A1:A4)
 * @param {Boolean} showHeaders [Optional] Show Groups and Metrics headers (default: true)
 * @return {Array} The value of the fields
 */
function BOTIFY_ANALYSIS_GET_URLS_DETAIL(apiToken, username, projectSlug, analysisSlug, urls, fields, showHeaders)

This macro allow you to retrieve fields data for a list of URLs.
To avoid rate limit (429) and get a faster response, we highly recommend to give a list of URLs to a single cell formula (as in the following example) instead of copying this macro for each URL.
Note a google script must respond within 30 seconds, thus if the list of URLs is too long, some URLs fields might not be computed, We recommend to give at most 10,000 URLs.

Aggregate analysis URLs data

/**
 * Return the result of the aggregation on URLs of a given analyses
 * @param {String} apiToken Botify API token
 * @param {String} username Username of the project owner
 * @param {String} projectSlug Project's slug of the analysis
 * @param {String} analysisSlug Analysis's slug
 * @param {BQLAggsQuery} urlsAggsQuery BQL Aggregation Query to perform
 * @param {Boolean} showHeaders [Optional] Show Groups and Metrics headers (default: true)
 * @return {Array} The result of the aggregation.
 */
function BOTIFY_ANALYSIS_AGGREGATE_URLS(apiToken, username, projectSlug, analysisSlug, urlsAggsQuery, showHeaders)

This macro allow you to compute metrics like average depth, pagerank distribution, and much more.
You can also group URLs in order to compute the amount of visits generated by each segments, for instance..
For more information about how to define aggregation queries, please refer to the Aggregate URLs documentation.

FAQ

How to get my API token?

You can get your API token in your user account page as explained there.

How to get my projectSlug and analysisSlug?

An easy way to get your projectSlug and analysisSlug is with the URL of your analysis report.
In the following example, adam_warlock is the username, demo-project is the projectSlug and 20160308 is the analysisSlug.

How to get the list of available fields?

A full list of available fields to display or compute metrics on can be found in the Analysis Datamodel.

How to define an aggregation query?

Please refer to the Aggregate URLs documentation.

How to get the source code of the macros?

The source code of the Botify Google Sheets addon is open source and available on Github. Feel free to customize our macros or contributing to the project.

Troubleshooting

I’m getting an #ERROR! with the code 429

The HTTP Status Code 429 means that you reached the Botify API rate limit.
Keep in mind that each cell using a Botify Macro is requesting the Botify API on background, thus an excessive usage of Botify Macros may reach the limit.
Please refer to Rate Limit documentation for more details.