Follow the trend of Apps develop in your org with a Dashboard of the default Cloud SQL App Maker Instance

Jérémy Dessalines
Aug 9, 2019 · 5 min read

Monitoring Google App Maker adoption in our Organisation

To improve our monitoring of the use of the Google App Maker product within our organization, we have implemented dashboards to track:

Number of apps active today, number of users on these apps.

As the summer was a good time to take a step back, I wanted to automate the use of the Cloud SQL App Maker instance of our organization.

This instance is used for projects in development before the production of our internal applications by our community of about 200 Citizen Developers at the group level.

The idea is simple, I want to know the trends of App Maker projects on this instance.

A project corresponding to the creation of a new database in this instance.

WIthout data your’re just another person with an opinion.

For this I realized an Apps Script project calling the Cloud SQL Admin API, a REST API for administering your instances programmatically in order to Lists instances under a given project in alphabetical order by instance name.

Data will be displayed on a DataStudio dashboard.

Configure the Apps Script

Create a new one.

For this script, you will need to activate OAuth2.

To add it to your script, do the following in the Apps Script code editor:

  1. Click on the menu item “Resources > Libraries…”
  2. In the “Find a Library” text box, enter the script ID 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDFand click the “Select” button.
  3. Choose a version in the dropdown box (usually best to pick the latest version).
  4. Click the “Save” button.

For this library (and the Apps Script functionality in general) the URL will always be in the following format:

https://script.google.com/macros/d/{SCRIPT ID}/usercallback

Where {SCRIPT ID} is the ID of the script that is using this library. You can find your script’s ID in the Apps Script code editor by clicking on the menu item “File > Project properties”.

Save the URL. We will need this URL to configure our Access to the GCP Instance.

Once done click on that, go to Resources > Cloud Platform project enter the Project Number of your Cloud SQL default instance. It will link your script to the project.

Access the related GCP Project. From there, click on the menu in the top left and navigate to “APIs and Services → Dashboard

Click on Enable APIs and Services and enable the following API:

Enable Google Cloud SQL Admin API

You will also need to create a Big Query Dataset (if one doesn’t exist).

OAuth Credentials

Then we need to create the OAuth credentials. First create the OAuth consent screen. Fill in the Application Name only.

Then create the OAuth ID and Secret by clicking on Create Credential -> OAuth client ID. Choose Web Application, and give it a name (again, use App Maker app/GCP Project name). We then need to specify the “Authorized redirect URI”.

We will need the {SCRIPT ID} we previously created:

Copy and add the previously generated URL:

https://script.google.com/macros/d/{SCRIPT ID}/usercallback

It is now setup for Cloud SQL API access.

We need to configure the scope in our script to access this API. According to the documentation:

.setScope(‘https://www.googleapis.com/auth/sqlservice.admin').setScope(‘https://www.googleapis.com/auth/cloud-platform')

Below the script with this scope:

Access to Big Query

As BigQuery is the data warehouse of Google Cloud. We will use it to store the data retrieved by the Cloud SQL API: The list of Cloud SQL instances.

To be able to use Big Query in Apps Script you have to activate this service in Apps Script. For this in your script go to Resources> Advanced Google Services and activate BigQuery API.

  • Create the Big Query table

In Big Query and create a table in one of your dataset

You will need to create an empty table with 2 fields:

“date” and “name”

Apps Script Function

Create the function who will use the service to access the API and insert the result into BigQuery.

Modify the params in the JSON with

CLIENT_ID and CLIENT_SECRET we have created before in GCP.PROJECT name and INSTANCE name of your Cloud SQL instance.BigQuery PROJECT Id, Dataset Id and Table Id.

Test it

PManagement() function.

The result in BQ

You will find the list of instances and the dates in the BigQuery table:

See the number of apps in your org by running

SELECT date, COUNT(NAME) FROM [YOURTABLE] GROUP BY date ORDER BY date desc

Schedule it

You can schedule the function to run hourly or daily depending on your needs.

Dashboard

Insert the result in a DataStudio to present it nicely

That’s it !

Thanks you for reading my first Medium post.

You can find the full code on GitHub

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade