A simple way to do this
In a Google sheet where this is enabled (see below for getting it set up), add the following formula to a cell, with the URL to a Mode query (note that you must include the query; you can get this URL by going to a report, clicking "View Details," and clicking on the query on the left).
=importModeResult("https://app.mode.com/organization/reports/abcdabcdabcd/queries/012301230123")
The cells below and to the right of this cell will be populated with the CSV results from this query. It will automatically use the last successful run of the query.
- From a Google Sheet, click “Extensions -> Apps Script” from the top menu bar.
- Paste the
importModeResults
function below into the code editor and save it. - In the code editor, click the “gear icon -> Project Settings” in the left-side menu, scroll down to the bottom and click the “Add Script Property” tab.
- Add a
username
andpassword
property. The value of the username property should be your Mode username, the email address associated with your Mode account, or a Mode API token. The value of the password property should be your Mode password or a Mode API secret. We strongly recommend using API tokens and secrets; you can create a token and secret in theAPI Tokens
tab of your Mode settings page. - Once you save these properties, you should be able to use the
importModeResult
function in your Google Sheet.
function importModeResult(url) {
var USERNAME = PropertiesService.getScriptProperties().getProperty('username');
var PASSWORD = PropertiesService.getScriptProperties().getProperty('password');
var token = getTokenFromUrl(url);
var account = getAccountFromUrl(url);
var query = getQueryFromUrl(url);
var options = {
headers: { 'Authorization': 'Basic ' + Utilities.base64Encode(USERNAME + ':' + PASSWORD, Utilities.Charset.UTF_8) }
};
var reportUrl = 'https://app.mode.com/api/' + account + '/reports/' + token;
var reportResponse = UrlFetchApp.fetch(reportUrl, options);
var reportJSON = JSON.parse(reportResponse);
var lastRunPath = reportJSON['_links']['last_successful_run']['href'];
var queryRunUrl = 'https://app.mode.com' + lastRunPath + '/query_runs'
var queryRunResponse = UrlFetchApp.fetch(queryRunUrl, options);
var queryRunJSON = JSON.parse(queryRunResponse);
var queryRuns = queryRunJSON['_embedded']['query_runs']
queryRuns.forEach(function(q) {
if (q['query_token'] == query) {
resultPath = q['_links']['result']['href']
}
})
if (resultPath) {
var csvPath = 'https://app.mode.com' + resultPath + '/content.csv'
var csvResponse = UrlFetchApp.fetch(csvPath, options);
var csvTextRaw = csvResponse.getContentText();
var csvText = csvTextRaw.replace(/(["'])(?:(?=(\\?))\2[\s\S])*?\1/g, function(e){return e.replace(/\r?|\r/g, '') });
return Utilities.parseCsv(csvText)
} else {
return 'Invalid URL!'
}
function getTokenFromUrl(url) {
return url.split('/reports/')[1].slice(0,12)
}
function getAccountFromUrl(url) {
return url.split('.com/')[1].split('/')[0]
}
function getQueryFromUrl(url) {
return url.split('/queries/')[1].slice(0,12)
}
}
This works, but it doesn't Refresh after report updates. Any tips/tricks for getting the formula to update after report refresh?