App Script to copy the latest fetch to base data worksheet

How I automate fetching Chrome Web Store user counts & ratings

Ego booster (or deflator) charts
Ego booster (or deflator) charts

I have 7 Chrome extensions and apps and, as a chronic numbers addict, I like to keep track of their user numbers (WAU), and ratings.

I use a spreadsheet (Google Sheet) to collect the data, and analyze trends, and catch (to diagnose) outliers. The same spreadsheet also functions as a JSON-providing backend for data being funneled elsewhere (e.g. for user numbers on this page).

While the analysis part, and the JSON-feed worked well, the data collection part was painful. Google doesn’t provide an API to fetch extension data, so I’ve had to fill the data manually into the spreadsheet every day!

For a long time, I used to open my Chrome Web Store (CWS) developer dashboard every morning, and one-by-one fill in the numbers into the spreadsheet cells. While this was relatively easy, if menial, on the desktop, it’s quite painful on the phone – copying numbers between two apps on the small screen.

As the next step, I created a Google sheets form with the 7 fields, connected it to my CWS-data spreadsheet, and bookmarked the form on my phone homescreen. Now, instead of opening, and editing, a large spreadsheet, I had to fill in 7 textboxes on a webpage. Much faster on a small screen. Still, manual though.

Last week, I finally went another step, and automated the whole thing using Google sheets’ importXML function. It’s a 2-step operation:

  • Importing data: The `importXML` function takes 2 arguments.
    For the first argument, provide the URL of the extension for which I want the data, say `https://chrome.google.com/webstore/detail/pbghjbbdfkjffaalmcfgelakmeckapnd`.
    For the 2nd argument, it takes the `XPATH` for the exact element on the page I want to fetch, in this case `//span[@class=’e-f-ih’]`.
    I then clean the returned data (`10,716 users`) to get a clean number (10716).
    Fetch user numbers, ratings and rating count
    A simple copy-paste-modify exercise gets me data for all my (and anyone else’s) extensions, as well as rating count and value.
  • Copying data: I wrote a basic app script to copy the fetched data onto a new row in the main sheet(s) where the base data is kept:
    App Script to copy the latest fetch to base data worksheet
    I’ve scheduled this script to run every day around noon, so by the time I check the stats at lunch, it’s already updated with latest data.

The rest of the spreadsheet is just the same, full of color-coded differentials to highlight outliers, and growth charts to inflate (or deflate) my ego. Not gonna share that here ;)

Hope this helps someone.


P.S.: If anyone knows of a CWS/Chrome API that lets me fetch all this data without all this hassle, please (PLEASE!) do let me know.

P.P.S.: Yes, I could have done all that work in a single app-script by fetching the page and then parsing it for data. I didn’t want to parse the page when Google Sheets provide me a simple function to do just that. Stupidly, Google Sheets functions are not available in Google Apps Scripts :(

P.P.P.S.: There are several services out there, e.g. import.io, which will let you build your crawlers and import this data directly (if you don’t want to mingle with Google sheets and apps-scripts)

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.