Knowledge Base

How to Connect Appfigures to Google Sheets

At the moment there isn't a native connector for Google Sheets (yet), but that doesn't mean you can't connect the two! You'll be able to do that with Google's Script Engine and a custom script that we wrote for you.

Here's how you do that:

  1. Open your Google Sheet. A new one is best but not necessary.
  2. Open the script editor by navigating to ToolsScript Editor

  1. Give the script a name in the top left corner of the editor. AF API is a good default.
  2. Copy the following script, as-is, into the editor
var key = '<YOUR PERSONAL ACCESS TOKEN>';

// The number of days to pull data for
var days = 7;

// The way data will be grouped. Separate multiple options with a comma.
var grouping = 'date';

// Product IDs of apps to include, separated by a comma. Leave blank for all apps.
var products = '';

// ---------- //

function createMenu() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Appfigures')
    .addItem('Get Sales','getSales')
    .addItem('Get Revenue','getRevenue')
    .addItem('Get Ratings','getRatings')
    .addItem('Get Subscriptions','getSubscriptions')
    .addToUi();
}

function getSales() {
getAppfiguresData('sales');
}

function getRevenue() {
getAppfiguresData('revenue');
}

function getRatings() {
getAppfiguresData('ratings');
}

function getSubscriptions() {
getAppfiguresData('subscriptions');
}

function getAppfiguresData(dataset) {
if(!dataset) dataset = 'sales';

var json = UrlFetchApp.fetch('http://api.appfigures.com/v2/reports/' + dataset + '?start_date=-' + days + '&group_by=' + grouping + '&format=flat&access_token=' + key);
var data = JSON.parse(json.getContentText());

// figure out all the datasets we have in th eresponse
var headers = Object.keys(data[0]);

var gs = SpreadsheetApp.getActiveSpreadsheet();
var sheet = gs.getActiveSheet();

headers.forEach(function(header, h) {
  var name = titleCase(header.replace("_", " "));
  sheet.getRange(1, h + 1).setValue(name).setFontWeight("bold");
});

data.forEach(function(item, i) {
  headers.forEach(function(header, h) {
    sheet.getRange(i + 2, h + 1).setValue(item[header]);
  });
});
}

function titleCase(s) {
s = s.toLowerCase().split(' ');
for (var i = 0; i < s.length; i++) {
  s[i] = s[i].charAt(0).toUpperCase() + s[i].slice(1); 
}

return s.join(' ');
}
  1. Set your access token. Click here to learn how to generate it

  1. Customize (optional):
  • Days - The number of days you want pulled every time you load data. The default is 7.
  • Grouping - How the data will be broken down. Options include date, country, storefront, and product. Multiple options can be specified, separated by a comma. By default, data will be grouped by date.
  • Products - The Product Ids of the apps you want to pull data for. By default (blank) all apps will be included.
  1. Save the project.

  1. Run the createMenu function to set things up. This one's important.

  1. Head back to your Google Sheet and you'll see a new Appfigures option in your menu. All you have to do now is select the data you want to bring into your Google Sheet and vóila!

Tinkering further

This is a pretty simple and generic way to pull data from the Appfigures API into Google Sheets. It can be further customized and other data sets can be added to it as well beyond the ones available in this script.

Check out the API docs to see what the API can do.