Android: Setting up in-app billing API v3 for purchases

In-app billing was a major source of frustration for me. All I wanted was to enable purchases on my Moustachify app, but no. Instead they provided a semi-working project which involved lots of guess-work and poking around with code to see what was essential for my project. For this tutorial, I'll be working on my other app Diablo 2 Runewords.

In terms of a development, the documentation by Google is abysmal. The official documentation has pages and pages of text, much of which isn't of great help when it comes to implementation.

As with the helper class (latest revision is 5 at time of writing) IabHelper. They've provided sample code which is broken for devices which don't have Google Play Services. These uncaught crashes caused me to waste many hours in finding and implementing the most appropriate fix.

I have supplied my patched up code which fixes the "Play is on everything" assumption made in Google's helper class.

Note: I'll say upfront now that this tutorial covers managed/consumable product purchases and consumption of products. However, it does not cover subscription based billing but I don't think it'd be much different.

Note #2: Please excuse the length of this post. It took a long over a week to write up as it's nearly Christmas, meaning lots of time with family/friends and late nights. State of mind isn't exactly the best for proof reading :)

Preparing the online side of things

First of all, add this permission to your app.

<uses-permission android:name="" />

Bump the "versionCode" up by one and compile a new signed APK. Upload this to your app's APK settings under "Alpha". As long as Google knows about this APK's billing permissions, it can be saved as draft or published to alpha - doesn't really matter.

Note: While testing, you should only use this versionCode. Bumping it up during dev will give you this error "Application Error : This version of the application is not configured for Market Billing. Check the help center for more information."

The reason why we do this first is because Google takes it's sweet time time propagating the changes (about 30mins to 4hrs), so it's best to get it out of the way early on.

However, immediately after uploading you should be able to create new products.


If you haven't done this, you wouldn't be able to access the "In-app Products" tab.

Preparing the test account

An interesting point to note is that you cannot test product purchasing using your development account. This means if you're logged into your phone using your developer account, then you have to sign out and create a new account in order to test your code. Damn you Google!

In your developer dashboard, go to global Settings (not the app specific settings).

Under account details, look for "License Testing" where you can add email addresses for users who are testing your apps. Any users logged into an Android device with the given emails will not be charged for testing your billing code, although they have to enter in valid credit card details.

For "License Test Response", make sure it's responding in a way you expect.

Creating products

Once in the "In-app Products" tab, start adding products.

Product type:

This determines the way it's associated with your account.

Once the product is created, you can't change it!

  • Managed type can only be purchased once and is permanently associated with the user's account.
  • Unmanaged type is a legacy artefact from the days of API v2. In v3, it behaves just like a managed type. You have to explicitly consume a managed product before it is removed from the account.
  • Subscription type sets up automatic recurring billing (not covered by this post!)

For more information, click on the type you're interested in and read the descriptions.

Product ID:

This is the "product SKU" or model number. As with product type, once the product is created you can't change it.

Each product ID must:

  • be unique (per app)
  • compose of either lowercase letters (a-z), numbers (0-9), underline (_) and dot (.)
  • start with lowercase alphabets or numbers

The naming convention is completely up to you, but try to name it by type. This helps you keep track of the products. For example:

  • app_name.permanent.ad_removal
  • app_name.permanent.gun_type_a
  • app_name.permanent.gun_type_b
  • app_name.consumable.refill_bullets
  • app_name.consumable.extra_3_lives

Title, description, price:

The rest of it is fairly straight forward. It's completely up to you what to fill in here.

Reminder! Click save once you're done setting the price, and remember to activate it!

Setting up the API library

Make sure you've got the "Google Play Billing Library" via the "Android SDK Manager". At time of writing, this is at revision 5 (and it's buggy)

Getting the files


Once you've downloaded it, look for your Android SDK folder. It's shown at the top of the SDK manager (top left, where it says "SDK Path").

  • In there, you'll find a folder called "SDK_PATH\extras\google\play_billing".
  • Ignore "", that's for the old API v2 way of handling in-app billing.
  • Look for a file called "IInAppBillingService.aidl"
  • Copy "IInAppBillingService.aidl" into your project under the path "PROJECT\src\com\android\vending\billing" (and yes, it has to be exactly the same!)
  • Now go to "SDK_PATH\extras\google\play_billing\samples\TrivialDrive\src\com\example\android\trivialdrivesample\util" (yeah I'm not kidding, it's ridiculously long)
  • Copy all 9 java source files file into your project under "PROJECT\src\common\services\billing\". You don't have to put it there, but I thought it was appropriate.

Making sure the files are patched

Google really let the ball drop with this library. Their new billing system is awesome, but they didn't even bother to release a stable library API files to accompany it.

I hate this bit because it took a few good hours out of my development time just to go research and tweak shit to get this up and running. It's a shame that I even have to write this part up.

What I've fixed is the NullPointerException and service binding errors when there is no Play Store installed on the device. What it should do is notify the user, not crash randomly or raise NPE's. And just for completion sake, serializable class IabException is missing a serialVersionUID.

There are other fixes posted online, but I feel there's too much error checking in too many different places. A sure way to cultivate a great source of bugs. Personally I like to find problems early and stop them before the service tries to do anything else.

Without further adieu, here's the diff for If you're looking for a quick download with the patches included, grab the fixed from my github repo (or the diff).

--- ~/Android/sdk/extras/google/play_billing/samples/TrivialDrive/src/com/example/android/trivialdrivesample/util/ Fri Nov 29 18:55:07 2013
+++ ~/Android/project/src/twig/nguyen/common/services/billing/ Fri Dec 20 12:24:09 2013
@@ -22,6 +22,7 @@
import android.content.Intent;
import android.content.IntentSender.SendIntentException;
import android.content.ServiceConnection;
import android.os.Bundle;
import android.os.Handler;
import android.os.IBinder;
@@ -80,6 +81,8 @@
// Has this object been disposed of? (If so, we should ignore callbacks, etc)
boolean mDisposed = false;

+ boolean mIsBound = false;
// Are subscriptions supported?
boolean mSubscriptionsSupported = false;

@@ -264,9 +267,11 @@

Intent serviceIntent = new Intent("");
- if (!mContext.getPackageManager().queryIntentServices(serviceIntent, 0).isEmpty()) {
+ List ri = mContext.getPackageManager().queryIntentServices(serviceIntent, 0);
+ if (ri != null && !ri.isEmpty()) {
// service available to handle that Intent
- mContext.bindService(serviceIntent, mServiceConn, Context.BIND_AUTO_CREATE);
+ mIsBound = mContext.bindService(serviceIntent, mServiceConn, Context.BIND_AUTO_CREATE);
else {
// no service available to handle that Intent
@@ -289,7 +294,9 @@
mSetupDone = false;
if (mServiceConn != null) {
logDebug("Unbinding from service.");
- if (mContext != null) mContext.unbindService(mServiceConn);
+ if (mContext != null && mIsBound) {
+ mContext.unbindService(mServiceConn);
+ }
mDisposed = true;
mContext = null;

Querying user purchases from account

You'll have to learn to assume is that the user has already purchased a product and this is the first time they're running the app on a new phone. Either that or they're logged into multiple devices, purchased a product from you on a tablet and then use your app on a phone.

Query the billing service as soon as you can. I put mine into the onCreate() of the main activity. This makes it effortless for the user to switch between devices and keep their billing information in sync. Good news is that subsequent queries are cached locally by the billing service, so it's very quick.

Although you can put the following code in an Activity, I suggest putting it in a fragment in case of rotation config changes resetting your query.

Once the query finishes, we save the result to a variable within the app to simplify our checks.

You can download from github.

import android.os.Bundle;
import android.util.Log;


* Helper fragment helps keep the billing madness out of MainActivity.
* @author twig
public class BillingInventoryFragment extends SherlockFragment {
// Helper billing object
private IabHelper mHelper;

public void onCreate(Bundle savedInstanceState) {



private void initialiseBilling() {
if (mHelper != null) {

// Create the helper, passing it our context and the public key to verify signatures with
mHelper = new IabHelper(getActivity(), G.getApplicationKey());

// Enable debug logging (for a production application, you should set this to false).
// mHelper.enableDebugLogging(true);

// Start setup. This is asynchronous and the specified listener will be called once setup completes.
mHelper.startSetup(new IabHelper.OnIabSetupFinishedListener() {
public void onIabSetupFinished(IabResult result) {
// Have we been disposed of in the meantime? If so, quit.
if (mHelper == null) {

// Something went wrong
if (!result.isSuccess()) {
Log.e(getActivity().getApplicationInfo().name, "Problem setting up in-app billing: " + result.getMessage());

// IAB is fully set up. Now, let's get an inventory of stuff we own.

* Listener that's called when we finish querying the items and subscriptions we own
private IabHelper.QueryInventoryFinishedListener iabInventoryListener() {
return new IabHelper.QueryInventoryFinishedListener() {
public void onQueryInventoryFinished(IabResult result, Inventory inventory) {
// Have we been disposed of in the meantime? If so, quit.
if (mHelper == null) {

// Something went wrong
if (!result.isSuccess()) {

// Do your checks here...

// Do we have the premium upgrade?
Purchase purchasePro = inventory.getPurchase(G.SKU_PRO); // Where G.SKU_PRO is your product ID (eg. permanent.ad_removal)
G.settings.isPro = (purchasePro != null && G.verifyDeveloperPayload(purchasePro));

// After checking inventory, re-jig stuff which the user can access now
// that we've determined what they've purchased

* Very important!
public void onDestroy() {

if (mHelper != null) {
mHelper = null;

G is a class I use to store global variables or statics for quick and dirty access.

  • G.getApplicationKey() obfuscates the application key from prying eyes. You can find this from "All applications" > your app > "Services & APIs" > "Your license key for this application". The reason why this is a function (and not a final static string) is that we're meant to generate this during runtime to guard against basic decompilation. How you wish to implement this is up to you.
  • Another point of interest is G.verifyDeveloperPayload(). The sample code in TrivialDriveSample wasn't entirely helpful; it simply returns true. Somehow this has to be generated from the user's information, but at the same time must be verified on multiple devices (so don't use IMEI).

    I'm not sure how much this matters to you, but it's possible to verify the payload check by implementing some custom server code to keep track of a "receipt" on purchase and then use that receipt to verify on inventory check. Again, how you wish to implement this is entirely up to you.

Now that you're able to determine what inventory your user has, you can update the UI accordingly (ticks where item is purchased, disable buy now button, etc)

Purchasing products

The code below is written in an Activity, but it could just as easily be ported over to a fragment as all the magic pretty much happens in the OnIabPurchaseFinishedListener handler.

Building upon the stuff learned from querying the inventory, the code used to purchase products is fairly similar. You'll need to initialise the billing service helper and then make a purchase request, coupled with a handler.

This is a fairly chunky piece of code, so feel free to grab from github for easier viewing.

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import com.actionbarsherlock.view.Window;

public class UpgradeActivity extends SherlockFragmentActivity {
// Billing helper object
private IabHelper mHelper;
private boolean mBillingServiceReady;

protected void onCreate(Bundle savedInstance) {


// Initialise buy buttons
Button btn = (Button) findViewById(;
btn.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {


private void initialiseBilling() {
if (mHelper != null) {

// Create the helper, passing it our context and the public key to verify signatures with
mHelper = new IabHelper(this, G.getApplicationKey());

mHelper.startSetup(new IabHelper.OnIabSetupFinishedListener() {
public void onIabSetupFinished(IabResult result) {
// Have we been disposed of in the meantime? If so, quit.
if (mHelper == null) {

if (!result.isSuccess()) {
// Oh noes, there was a problem.
complain("Problem setting up in-app billing: " + result.getMessage());

// IAB is fully set up.
mBillingServiceReady = true;

// Custom function to update UI reflecting their inventory

// User clicked the "Upgrade to Premium" button.
public void onButtonUpgradeClicked() {
if (!mBillingServiceReady) {
Toast.makeText(UpgradeActivity.this, "Purchase requires Google Play Store (billing) on your Android.", Toast.LENGTH_LONG).show();

String payload = generatePayloadForSKU(G.SKU_PRO); // This is based off your own implementation.
mHelper.launchPurchaseFlow(UpgradeActivity.this, G.SKU_PRO, G.BILLING_REQUEST_CODE, mPurchaseFinishedListener, payload);

* When In-App billing is done, it'll return information via onActivityResult().
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
if (mHelper == null) {

// Pass on the activity result to the helper for handling
if (!mHelper.handleActivityResult(requestCode, resultCode, data)) {
// not handled, so handle it ourselves (here's where you'd
// perform any handling of activity results not related to in-app
// billing...
super.onActivityResult(requestCode, resultCode, data);

* Very important
public void onDestroy() {

if (mHelper != null) {
mHelper = null;

// Callback for when a purchase is finished
private IabHelper.OnIabPurchaseFinishedListener mPurchaseFinishedListener = new IabHelper.OnIabPurchaseFinishedListener() {
public void onIabPurchaseFinished(IabResult result, Purchase purchase) {
// if we were disposed of in the meantime, quit.
if (mHelper == null) {

// Don't complain if cancelling
if (result.getResponse() == IabHelper.IABHELPER_USER_CANCELLED) {

if (!result.isSuccess()) {
complain("Error purchasing: " + result.getMessage());

if (!G.verifyDeveloperPayload(purchase)) {
complain("Error purchasing. Authenticity verification failed.");

// Purchase was success! Update accordingly
if (purchase.getSku().equals(G.SKU_PRO)) {
Toast.makeText(UpgradeActivity.this, "Thank you for upgrading!", Toast.LENGTH_LONG).show();

G.settings.isPro = true;

// Update the UI to reflect their latest purchase
// Consume product immediately
else if (purchase.getSku().equals(G.SKU_CONSUMABLE_BULLETS)) {
// Snippet shown below ...

There are only a few things happening here, but it's been bloated by comments and inline objects. Some notes to keep in mind:

  • generatePayloadForSKU() is not necessary as it's used for verifyDeveloperPayload()
  • complain() is just a placeholder for your own "message to user about errors" function.
  • BILLING_REQUEST_CODE is just a request code, similar to for "request" in startActivityForResult().
  • There is no event trigger for SKU_CONSUMABLE_BULLETS as the handler is purely there for example.

The summary below explains what's happening in the code. Reading both from top-to-bottom explains the following logic flow:

  • onCreate(): Initialise the billing service and a buy button
  • initialiseBilling(): The billing service detects if the service is available, updates mBillingServiceReady and the UI accordingly. Optionally, you can delay the UI update until after an extra query for the latest inventory is done to ensure the most up to date inventory is used.
  • onButtonUpgradeClicked(): Hander for the upgrade button simply launches the in-app billing process.
  • onActivityResult(): This gives IabHelper.handleActivityResult() a chance to do it's thing.
  • onIabPurchaseFinished(): This is where the magic happens.More details below.
  • onDestroy(): Always important to do some house keeping.

Until now, there isn't really anything special. mPurchaseFinishedListener is where the brains of this exercise lies.

Breaking down onIabPurchaseFinished(), we'll see that:

  • We need a special case for detecting IabHelper.IABHELPER_USER_CANCELLED. This causes result.isSuccess() to be false, but we don't wanna display an error message if the user simply cancels it.
  • Subsequently, if the result really DID fail for reasons other than cancelling then we should notify the user.
  • Upgrading to PRO means that it's a permanent purchase. Because of that we need to do something about it locally. Straight forward stuff.

Consuming purchases

Just below that PRO purchase example is another use-case where we've just purchased a consumable (G.SKU_CONSUMABLE_BULLETS). Consumables are purchases we don't want to leave as "purchased" against a user account (like extra lives or bullets). We should consume those immediately after purchase where possible.

mHelper.consumeAsync(purchase, handler) makes it really easy to do just that.

mHelper.consumeAsync(purchase, new IabHelper.OnConsumeFinishedListener() {
public void onConsumeFinished(Purchase purchase, IabResult result) {
// if we were disposed of in the meantime, quit.
if (mHelper == null) {

if (result.isSuccess()) {
Toast.makeText(UpgradeActivity.this, "Bullet pack purchased", Toast.LENGTH_LONG).show();

// Example of what you need to do
playerBullets += 100;
else {
complain("Error while consuming: " + result);

// Update the UI to reflect their latest purchase
  • As always, check if mHelper is still valid.
  • And check for success state, apply consumable appropriately.
  • Otherwise complain loudly about what went wrong.
  • Update the UI accordingly.

Protecting the code

You should always use some sort of protection against prying eyes. Although there is no sure 100% way of keeping your app safe, you can always take simple precautions to prevent people from getting their hands on sensitive information such as your secret app keys.

One effective way is to use ProGuard on your app at compile time for signed apps. I won't be covering that in this post.

As mentioned before, you should also obfuscate your application key where possible.


In no particular order (or perhaps in the order which I ran into issues with?)

Eclipse: Hide pyc files from Open Resource Window

Something that's been bothering me for some time now is the fact pyc files appear in Eclipse's "Open Resource" popup when using PyDev.


Although they don't show in the project explorer, there's absolutely no reason for them to appear in the resource popup. So, in order to remove them you'll have to:

  • Right click on your project (it seems you only have to do this once)
  • Click Properties
  • Go to Resource > Resource Filters
  • Click "Add"
  • Filter type: Exclude all
  • Applies to: Files
  • Click "All children (recursive)"
  • File and folder attributes: Name matches *.pyc
  • OK to apply and save thoroughly


Your settings should look a little something like this.

Although I've only done this once, it seems that it applies to all my projects. Could be wrong, but I don't remember having to do it for all my projects.


Android: Using activity fragments (with or without ActionBarSherlock)

During the rewrite of my old app Moustachify Everything (made in the days of Android 1.6), I decided to switch it to use a new-old thing called Fragments. They help you persist information during screen orientation changes when activities are destroyed and recreated.

Depending on what the fragments are used for, we'll be needing different files. And of course, you'll probably want to include the v4 support library as well.

Hidden fragments

Hidden fragments allow AsyncTasks and AlertDialogs to persist across rotations. Anyone who has worked with this before will understand that it's tedious, horrible and generally error prone.

Since it's hidden, you won't need to create/edit as many files.

  • Create: A class to extend whichever fragment type you need (eg. DialogFragment, regular Fragment or SherlockFragment)
  • Modify: Your Activity to extend FragmentActivity or SherlockFragmentActivity
  • Modify: Implement the dialog response in your activity from the Interface
  • Modify: Use snippet to create fragment when needed, show it using a unique "tag" string

The following is an example which allows you to instantiate a fragment (with arguments), display a custom dialog of choice and then keep itself displayed during rotations.

All you really need to do is make sure the interface suits your needs and the dialog shows the right content. (click to view):

public class ExampleDialogFragment extends DialogFragment {
private static final String KEY_ARG_A = "argumentA";
private static final String KEY_ARG_B = "argumentB";

* Interface to link back to the activity.
public interface ExampleDialogResponses {
public void doPositiveClick(long timestamp);

* Pass in variables, store for later use.
public static ExampleDialogFragment newInstance(int argA, String argB) {
ExampleDialogFragment f = new ExampleDialogFragment();

Bundle args = new Bundle();
args.putInt(KEY_ARG_A, argA);
args.putString(KEY_ARG_B, argB);

return f;

* Override the creation of the dialog.
public Dialog onCreateDialog(Bundle savedInstanceState) {
// Fetch the information set earlier
int argA = getArguments().getInt(KEY_ARG_A);
String argB = getArguments().getString(KEY_ARG_B);

// Generate your dialog view or layout here
// view = ...
TextView view = new TextView(getActivity());
view.setText(argB + String.valueOf(argA));

// Display the dialog
return new AlertDialog.Builder(getActivity())
.setTitle("Choose OK or Cancel")

// Cancel
.setNegativeButton(android.R.string.cancel, null)

// Click "OK" button handler
new DialogInterface.OnClickListener() {
public void onClick(DialogInterface dialog, int whichButton) {
long timestamp = System.currentTimeMillis();
((ExampleDialogResponses) getActivity()).doPositiveClick(timestamp);

Changing the base class of the activity is easy. While we're at it, implement the fragment response ExampleDialogFragment.ExampleDialogResponses.


public class YourActivity extends Activity


public class YourActivity extends FragmentActivity implements ExampleDialogFragment.ExampleDialogResponses
// or if you're using ActionBarSherlock
public class YourActivity extends SherlockFragmentActivity implements ExampleDialogFragment.ExampleDialogResponses

Implementing the dialog response helps keep the code clean. This goes into your Activity class.

public void doPositiveClick(long timestamp) {
Log.d("doPositiveClick", String.valueOf(timestamp));

And finally, this is how you display the fragment. First we initialise the dialog with newInstance() and the given arguments, and then we display it with show().

DialogFragment newFragment = ExampleDialogFragment.newInstance(12345, "ABCDE");, "dialog");

For ActionBarSherlock user, replace getFragmentManager() with getSupportFragmentManager().

The "dialog" string is simply a unique name for this fragment within the context of the activity.

Layout fragments

Normal layout fragments contain a layout and are treated like a View, except it retains non-view information during rotations. This is something which has bothered many developers for a long time.

Disclaimer! Something to keep in mind is that view information is not retained as they are destroyed along with the activity.

Normal activity fragments which display a layout will require a little more work than hidden fragments.

  • Create: A new layout for the fragment
  • Create: A class for the new fragment extending Fragment or SherlockFragment
  • Modify: The Activity layout to include a fragment
  • Modify: Activity to extend FragmentActivity or SherlockFragmentActivity

There isn't anything special about creating a layout for your new fragment. Just like any other layout, you can put it in /res/layout. I won't bother pasting the code to this post. Check out the sample code for fragment_example.xml (click to view) if you're really that curious.


The main attraction is the fragment class

public class ExampleFragment extends SherlockFragment {
// These values are automatically retained
private int buy = 0;
private int sell = 0;

// Views are destroyed each time the activity is rotated.
// These are NOT automatically retained by the fragment.
private Button btnBuy;
private Button btnSell;
private TextView tvLabel;

public void onCreate(Bundle savedInstanceState) {



public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {
View view = inflater.inflate(R.layout.fragment_example, container, true);

// Store handles to the controls
btnBuy = (Button) view.findViewById(;
btnSell = (Button) view.findViewById(;
tvLabel = (TextView) view.findViewById(;

// Event handlers
btnBuy.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {

btnSell.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {

return view;

public void onViewCreated(View view, Bundle savedInstanceState) {
super.onViewCreated(view, savedInstanceState);


private void updateLabel() {
StringBuilder sb = new StringBuilder();
sb.append("Buy: ");
sb.append(", sell: ");


Now to include the fragment in your activity layout. You'll need to add the "tools" namespace and add the fragment element to where it should be created.

To add the tools namespace, open the layouts file and go to the top. Add in the tools line:

<?xml version="1.0" encoding="utf-8"?>
xmlns:tools="" <!-- This one! -->

The fragment itself should be treated like a normal view, bound by height and width rules of the parent. Most of this can be done via the WYSIWYG editor.

android:layout_height="match_parent" />

The important attributes are:

  • android:id: the fragment an ID so you can refer to it via code
  • android:name: maps it to the given fragment class
  • tools:layout: fill the fragment with elements from the given layout

That's all the setup done! By now you should have a fully working preview in the layout editor.


Accessing fragment via code

Lastly in order to make use of your new toys, you'll have to be able to retrieve them.

FragmentManager fm = getSupportFragmentManager();
// or getSupportFragmentManager() for ActionBarSherlock users
fragment  = (ExampleFragment) fm.findFragmentById(;

Some key points to remember:

  • having the fragment defined in the XML means initialisation is already done for you
  • onCreate() tells the fragment it'll be retained.
  • onCreateView() is when you inflate the layout and initialise stuff.
  • onViewCreated() is where you fill in the initialised views and the retained variables.

And that's it! Your fragment will handle the instantiation pains so you don't have to worry about them as much.


Excel: How to create related drop-down lists

It's something that would be useful to many, but the terminology used in Excel isn't as straight forward as many (non-accountants) would think.

Select any values in "Make" column and it'll restrict your choices in "Model" column to a certain range. In the famous words of Jeremy Clarkson, "how hard could it be?"


Actually, not terribly hard but time consuming the on the first try.

For this I've used Excel 2010, but the essential steps are the same in Excel 2003 except getting to the options will be a little different.

Setting up the category/subcategory list data

You'll have to map out all the possible drop-down values in the lists. It's best to put this on another spread sheet to avoid cluttering up your data.

So on the "Category" sheet, prepare your drop-down choices like so.


Primary category

Back on your data sheet, select the column for the primary list.

Then go to Data ribbon tab > "Data Validation".


Select "List" type for criteria and click on the data arrow to select your source.

Switch to the category sheet and select the cells you want for the primary list.


Now you're able to select the primary category on each row.


Naming cell groups

Next up is the preparing the sub-category data. For this, you'll need to group cells together and give them a name.

  • Select the cells you're currently grouping (blue)
  • The name for that group MUST match the corresponding label for the group in the primary list (red).
  • Type in the name of the group in the green box area.
  • Press enter to name them.
  • Repeat this for all other cell groups (Honda, Toyota, etc)

If you made a mistake, use the "Name Manager" under "Formulas" to edit or delete them.


Related subcategory list

Going back to the data sheet, highlight the first cell you want to use as the sub-category.

Set up "Data Validation" with "List" validation type again, except this time you use a formula.


Since we're typing this into cell B2, we match the primary cell value to cell A2 (highlighted in yellow)


Once that's done, drag the corner down to apply this to cells in this column.

If there's an easier way, please let me know :(


Test if it works

If done correctly, it'll automatically populate the list items based on the value of the primary list.


And there you have it, related drop-down lists!

I think that's enough pictures for one post so I'll refrain from my usual post-mortem random GIF attachment.


Nintendo 3DS: Setting up Gateway 2.0b1

If you own an Australian PAL 3DS console, then you'll know how painful it is to see 3DS games on sale going to waste during Black Friday in the US. Or not being able to play that Japan exclusive game on your own 3DS without having to buy another 3DS console from Japan.

Ahh Nintendo and their region locking... until Gateway 3DS came along! Buy game, download ROM, and play!

But alas, there are a few things magic required in order for this to work.


  • A Nintendo 3DS (of any variety) with the right firmware between 4.1 to 4.5 (inclusive), so make sure of this before you rush out to buy a Gateway card!
  • A Gateway card (both red and blue)
  • An SD card with at least 1gb of free space (the one that came with your 3DS is fine)
  • A micro-SD for the 3DS ROM you want to run.
  • An SD card and microSD reader for your PC


A quick thing to note is the difference between SysNAND and EmuNAND. Gateway 2.0 brings a new feature which allows you to upgrade a fake copy of the firmware without actually going past firmware v4.5.

Your console firmware (SysNAND) still remains on a version between 4.1 to 4.5, and whenever you're on Gateway Mode you're running EmuNAND (any firmware). This allows you to run roms built on firmwares newer than v4.5.

Some things to do before beginning the process.

  • Turn off WiFi on your 3DS.
  • Have the 3DS fully charged or plugged in

Upgrading 3DS firmware to specific version between 4.1 to 4.5

If your 3DS is on a firmware older than 4.1, then you're gonna have to upgrade it.

Don't do an, online upgrade. It'll take you straight to the latest version and then the Gateway card won't work.

To determine what firmware certain games need, use and filter by the firmware you want. Be careful, games may have different firmware versions depending on which region it's for.

To help you decide, some great titles to choose from are:

  • 4.1 Bravely Default (JP)
  • 4.1 New Super Mario Bros 2 (US, PAL)
  • 4.1 Project X Zone (JP)
  • 4.1 Scribblenauts Unlimited (US)
  • 4.2 The Legend of Zelda - Ocarina of Time 3D (China)
  • 4.3 Professor Layton and the Miracle Mask (US)
  • 4.3 Paper Mario Sticker Star (US) (I'm half hearted about this one, it feels like shovelware)
  • 4.4 New Super Mario Brothers 2 (Taiwan)
  • 4.4 Castlevania Lords of Shadow - Mirror of Fate (US)
  • 4.4 Monster Hunter 3 Ultimate (US)
  • 4.5 Donkey Kong Country Returns 3D (US)
  • 4.5 Fire Emblem - Awakening
  • 4.5 Luigis Mansion 2 (US, JP, PAL)
  • 4.5 Pokemon Mystery Dungeon - Gates to Infinity (US, PAL) (Again, feels like shovelware)

Remember, although there is lots of shovelware games, buy the games that are actually good!

If you need any help, I've written a more detailed post about it here.

Get the Gateway firmware

The Gateway doesn't come with a micro SD card so you have to find you own.

Grab the firmware from the official site.

At time of writing, I downloaded v2.0beta1.

Installing the Gateway exploit

You'll need both SD and microSD cards for this part.

From the firmware package:

  • extract "GW_BETA_RELEASE_2.0b1\Launcher.dat" to your SD card
  • Put the SD card into your SD card slot.
  • extract all the contents from "Blue Card (R4i)" to your microSD card
  • Put the microSD into your Blue Gateway (installer) card.
  • Put the Blue Gateway card into your 3DS
  • Run it like any normal DS game

You should see the Gateway logo on the top screen and on the bottom a blue screen with some writing.

Select "GW_INSTALLER.NDS" and follow the steps to install the exploit.

Once done, turn off the 3DS and remove the Blue Gateway card.

Backing up your SysNAND

This requires a minimum of 1GB free space on the Nintendo 3DS SD card, and will take approximately 10 minutes.

Every console has a unique NAND image and if you accidentally update the onboard system NAND then there is currently no known way to restore back to 4.5 through software means.

The only recovery option is to restore via hardware using the backed up NAND image as a last resort.

So please make sure you physically copy the file created by our tool from the SD card to your computers hard drive.

To boot your 3DS into the Gateway menu, go to Settings > Profile > hold "L" and click on "Nintendo DS Profile". Don't let go of L until you see the menu!

It should work within 5 seconds. If it doesn't, just restart it and try again.

Once in, select "Backup System NAND" and press A to confirm. This will take approximately 10mins to complete. At first it'll show "Reading" for some time, but then the progress bar will appear.

Here is my pink 3DS in all it's glory. And yes, you'll have plenty of time to get acquainted with this screen.

Making sure you've backed up the unique NAND for your 3DS, copy all the files from the SD card onto your computer.

In the meantime, turn off the 3DS.

Creating your EmuNAND

Once the copying is done, put the SD card back into your 3DS and turn it on and get back into the Gateway menu.

This time, select the last icon "Format EmuNAND". It'll warn you about wiping all the data off the SD card, but it's fine because you should've backed it up. This will take about 5mins to complete.

For me, I didn't lose any save game process (on New Super Mario Bros 2) when switching between SysNAND and EmuNAND because (I'm guessing here) it doesn't seem to be associated with the SD Card.

Upgrading your EmuNAND firmware

Finally, go to the left-most icon in the Gateway Menu and select "Boot Gateway Mode".

The screen will blank out for about 5 seconds before loading up the main menu. Click on "Settings" and make sure that the firmware version says "GW3D" on it!

Otherwise, you're looking at the original firmware (SysNAND)
and should stop immediately!!!

If you don't see GW3D and still decide to upgrade, prepare yourself for tears.

Set up the wireless access point on your EmuNAND and upgrade the firmware THROUGH THE INTERNET. It is unconfirmed what will happen if you upgrade through a ROM.

At time of writing the latest firmware is v7.0. It's only a few days old but it causes some trouble with Gateway and (for now). On emuNAND v7.0, there is; no access to the eShop. random crashes, unable to play roms outside of console region.

Loading a ROM

Once you have your "whatever.3ds" rom, put it onto your microSD card by using Win32 Disk Imager.

Select the file and select your MicroSD drive, hit "Write". Takes about 5mins to write 1gb on USB2.


Slip the microSD card into the Red Gateway card and throw it into your 3DS.

Boot into Gateway mode via the Nintendo DS Profile exploit and you should see the game appear in the main menu.

There's nothing special about loading the game. Just tap or press A to load it.

Note that each time you shut down the 3DS, you will have to boot into Gateway Mode before you can load the rom.


  • If you're after a 3DS with firmware 4.5 or lower, then check the box for "(C) 2012 Nintendo" or earlier. It's right there on the same side as the WiFi certification, barcode and "Original Nintendo Seal of Quality".


  • The colour of the 3DS doesn't matter. I keep telling myself this because mine is pink... :\
  • The beauty of the EmuNAND is that if your firmware upgrade process goes horrifically wrong, your 3DS won't get bricked! Just restore the image from your backup on the PC and try again.
  • To help you keep track of which mode you're in, I strongly advise you to arrange the icons in SysNAND and EmuNAND differently. For example, in SysNAND put the Settings icon first whilst in the EmuNAND put the game icon first.
  • To prevent accidental firmware upgrades, enable parental controls!
  • In Gateway mode/EmuNAND, exiting the Settings app will revert you back to normal mode (non-Gateway mode). I am not sure if this is still in the SysNAND or not, but it's definitely something to keep in mind.
  • At the moment, only one rom can be loaded per microSD card at a time. To load another game, you have to use Win32 Disk Imager again.


Python & Google Analytics v3: Using google-api-python-client to access Analytics via the API

First up, I don't know what the fuck Google was doing with the docs when this was rolled out. Anything involving OAuth2 is horribly documented, requiring you to trawl through hundreds of pages for a few key lines of information.

Python's gData library is very nice, don't get me wrong. However it seems to be using Analytics v2 and the main issue I had with v2 was the rate limit which was constantly being reached. With v3, the limit was dramatically increased but you're constantly being told to go through the unecessary way of implementing OAuth2.

Well when I finally sledged my way through Google's crazy maze of doc pages, a mixture of obsolete StackOverflow posts and people giving advice for the "user authenticated" way of doing things, I finally came up with a working example to share.

Yes Google, I really enjoyed reading all those pages of writing to get what it working...

API Setup - Google Cloud Console

Google has taken a great amount of effort to centralise all the configuration for their APIs into this one central hub, Google Cloud Console.

In order to get Analytics API working, you'll need a "project".

  • Create a project (with any name and Project ID) and click on it.
  • Click on "APIs & Auth"
  • Enable "Analytics API". Make sure the status is green and says "ON"
  • Click on "Analytics API"
  • "Quota" is where you set your rate limit (I set mine to 10 requests per second because of the regex 128 character limit when filtering).
  • "Reports" is where you can check your usage for the day. You can have up to 50,000 requests a day.

Now click on "Registered apps" under "APIs & auth".

  • Register a new app (or use existing one)
  • Name can be whatever you want, but it should be a "Web Application"
  • When you see a screen with 4 options, click on "Certificate"


  • Click "Generate certificate"
  • Download and save the private key file to a safe place. You'll need this to access the API from within your code.
  • Copy the generated "Email Address" to your code somewhere. It should end with "". This is the important bit.

The email address is your "Service Account". This is not made clear to you in many of the doc pages!

Now we should be done with the Google Cloud Console.

API Setup - Analytics & how to get a table ID

Log into your Analytics admin and edit your tracker. Add in your service account email into the list of users which can view & analyse your data. You need to do this for every tracker the account needs to access.

While you're still in Google Analytics, you will need to find the unique "table ID". This simply refers to each site you have in the account.

This is what the docs describes the table ID as...

The unique table ID of the form ga:XXXX, where XXXX is the Analytics view (profile) ID for which the query will retrieve the data.

The unique ID used to retrieve the Analytics data. This ID is the concatenation of the namespace ga: with the Analytics view (profile) ID. You can retrieve the view (profile) ID by using the method, which provides the id in the View (Profile) resource in the Google Analytics Management API.

Not very useful! Link is useless too.

How you get the table ID is relatively simple. There's no need to write code or put your data into a 3rd party site either.

Take for example my (now defunct) "DCX GoogleCode page" tracker.


The table ID is NOT the field starting with "UA-..."! Instead, right click on the last item in the list and copy the link location.

The current link has a format like this:

Your table ID sits in the position where "33333333" is after the character "p". Copy that to your code somewhere.

Setting up Python

You'll need to install these libraries. You can use pip or easy_install, or manually. Whichever you prefer, just make sure you install it right. I've specified the versions I used at time of writing in case anybody needs it.

  • google-api-python-client (v1.2)
  • pyOpenSSL (OAuth installs an older version v0.1, but I had to upgrade to v0.13.1)

Code Snippet

This code will help you initialise the connection to the Analytics API v3 service.

import httplib2

from apiclient.discovery import build
from oauth2client.client import SignedJwtAssertionCredentials

def connect_to_analytics(self):
f = file('googleanalytics/your-privatekey.p12', 'rb')
key =
credentials = SignedJwtAssertionCredentials(

http = httplib2.Http()
http = credentials.authorize(http)

return build('analytics', 'v3', http=http)

As you can see, there's no:

  • secret client tokens
  • "flows"
  • Getting an authorisation URL and storing temporarily credentials
  • "Storage" methods
  • web authentication back and forth bullcrap to deal with

This example can be tweaked in any way you want, just read through to see the important bits for you. I've included an exponential backoff for your convenience in case you hit the rate limit.

def fetch_data():
# Exponential backoff
n = 1
service = connect_to_analytics()

while True: # Retry loop
# See this URL for a full list of possible dimensions and metrics
arguments = {
'ids': 'ga:123456', # Your Google Analytics table ID goes here

'dimensions': 'ga:pagePath',
'metrics': 'ga:pageviews',
'sort': '-ga:pageviews',

'filters': 'ga:pagePath=~%s' % path_pattern, # Regex filter
'start_date': start_date.strftime("%Y-%m-%d"),
'end_date': end_date.strftime("%Y-%m-%d"),
'max_results' : 1000, # Max of 10,000

data_query =**arguments)
feed = data_query.execute()

# Reset retry counter
if n > 0:
n = 0

break # Break free of while True

except HttpError, error:
print error.__class__, unicode(error)

if error.resp.reason in ['userRateLimitExceeded', 'quotaExceeded']:
sec = (2 ** n) + random.random()
print "Rate limit exceeded, retrying in %ss" % sec
n += 1

if 'rows' not in feed:
print "No results found"

data = {}

for row in feed['rows']:
pagePath, pageviews = row

# TODO: Do your stuff here
# example: data[pagePath] = data.get(pagePath, 0) + int(pageViews)

return data

The important bits are setting the right arguments and these two lines:

data_query =**arguments)
feed = data_query.execute()

Just in case

If at any time you see this error, you'll need to upgrade pyOpenSSL:

  File "/usr/local/lib/python2.7/dist-packages/google_api_python_client-1.2.egg/oauth2client/", line 106, in sign
    return crypto.sign(self._key, message, 'sha256')
AttributeError: 'module' object has no attribute 'sign'

A little post-coding activity

Please take the time to give Google a big kick in it's ass. Not everybody is drinking the Google-aid so tell them which part of the docs need more explaining. There's a lot of assumed knowledge in there which makes it difficult for people to pick things up.

Take THIS shitty Google documentation!


Not very useful

Useful once you're connected to the API.

This pointed me to the right search term, "Service Accounts"

Finally, the holy grail.

Copyright © Twig's Tech Tips
Theme by BloggerThemes & TopWPThemes Sponsored by iBlogtoBlog