Play Store Review to spreadsheet automation

How to analyse Play Store Reviews in Google Sheets
Our guide to analysing Play Store Reviews in Google Sheets

Knowledge is Power

Getting the most out of store reviews for apps is paramount to their success. Here’s how we can powerfully analyse Play Store reviews.

After releasing your app keeping track of user’s sentiment is the most important task you can do, as this allows you to plan for future iterations and see any potential discrepancies in your userbase. Once a month, our designer, Jotham, collates the latest reviews on each of our clients’ apps in a spreadsheet. He uses this to analyse the data and triage review to see the latest experiences and thoughts from users on each app.

As you can imagine this process is time-consuming but super valuable. So we decided to use a recent R&D day to automate the process. This blog outlines the first step of that automation; collecting the reviews in a spreadsheet automatically.

The first step

To do this you’ll need a service account. You can create one following this guide. Once you’ve created your service account, download the JSON file and place it in your drive.

You will need to note down the file id. You can find this in the URL once you select the ‘open with’ option. This file has everything you need to access the Google Play Developer API.

Next create a new Google sheet, set up your column names according to the information you want to pull from the review. We use:

  • Version Number
  • Date Of Review
  • Review Rating
  • Comment
  • Device
  • Android OS
  • Review ID

To begin scripting; select Tools then Script Editor. This will open an online IDE where you can write in Apps Script, derived from JavaScript.

Once here, you need to generate a token to access the Google Play Developer API. To do this we’ll use a handy library called cGOA. Add the library by clicking Resources then Libraries.

Search for MZx5DzNPsYjVyZaR67xXJQai_d-phDA33 and add it. Once this is done, you should add the following code and run to generate the token (this will only need to be done once):

To retrieve the token, add the following code:

Then you can call Goth.getToken(‘PlayConsole’) to get the token. You will now have access to the android publisher scope where we can retrieve the reviews.

Making it usable

You can retrieve a paged list of reviews that users have left over the past week by calling

UrlFetchApp.fetch("https://www.googleapis.com/androidpublisher/v3/applications/your_package_name/reviews?access_token=your_auth_token" );

You are now able to start populating the spreadsheet with this data. First, convert the result into a useable object and open the spreadsheet:

Next, detect the bottom of the spreadsheet and iterate over the data populating the relevant columns. The function looks like this:

This uses a helper function:

As you can see we perform some other functions on the reviews, like reversing the order and calculating the cumulative average.

We also check that we haven’t added the review already. Run your code and you should see the spreadsheet begin to populate.

The really cool thing about Apps Script is that you can run it in multiple ways. Either in the editor like you just did or you can add a button to the spreadsheet, or set up a custom trigger.

To add the button to the spreadsheet run the following code:

To add a trigger head to https://script.google.com/home, select your script and triggers from the three dots menu. There are several types of triggers which can run at a scheduled time or from a spreadsheet event. For example, we run the custom button menu code onOpen.

With the reviews readily available in the spreadsheet we could now look into automatically classifying them or performing sentiment analysis. We will look into this in the coming months.

Written with help from http://ramblings.mcpher.com/Home/excelquirks/goa/walkthroughs/serviceaccount


Looking for something else?

Search over 400 blog posts from our team

Want to hear more?

Subscribe to our monthly digest of blogs to stay in the loop and come with us on our journey to make things better!