bstancil/import_google_sheet.md Secret

## import_google_sheet.md

      
    Raw
  

              import_google_sheet.md
            
          
    Importing Mode results into a Google Sheet

A simple way to do this
Usage

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.
Setup


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 and password 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 the API 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)
  }
}