How to analyse Play Store Reviews in Google Sheets
Written by Rhys Kentish
Oct 16, 2019

Play Store Review to spreadsheet automation

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):

https://gist.github.com/rhyskentish/1d2bee05fee789ee4fbabf9d5c9fc210

To retrieve the token, add the following code: 

https://gist.github.com/rhyskentish/4a2c94bd88eb814c28a80c00d67f189f

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...);

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

https://gist.github.com/rhyskentish/52f7e74af6d9076023aaeea298f1c886

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

https://gist.github.com/rhyskentish/17c6d30a7f0eb72d8991faf36e0ef387

This uses a helper function: 

 

https://gist.github.com/rhyskentish/6d4f1ac376e7f04a1e95884f5b0ab381

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: 

https://gist.github.com/rhyskentish/53d84372f8476d56eed991fee2644168

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/serviceacc...

Top