How to design advanced Google App Maker apps with BigQuery API, Google Groups, Google Data Studio and Cloud SQL Jérémy Dessalines Follow Oct 2, 2019 · 4 min read

My last Medium post raised questions on how to achieve an app like this.

I’d like to take a bit more time here to explain how to design this kind of advanced App Maker apps, and how to make it a success.

In our company :

we use BigQuery as our datalake. Basically if you need fresh and reliable data you need to query or fetch data from there.

we use Google Groups. We have Google Groups for our Business Units for example.

we use Google Data Studio. We use it as our prefered BI tool — read the post of my colleague Remy David for example — . It can be easily used by both our IT or business teams.

What does it mean for most of my Google App Maker architecture ?

0 — Should I deploy this app: as User or as Developer ?

I always recommend to publish apps as s Developer, as It will not require your end-user any access to their Google account.

What does it mean ? The account that is used for running code is often referred to as the execution identity. App Maker can use the app user’s account (the default value) or the account of the developer who publishes the app.

On the other side, it also means that you will not have access to your end-user Drive, Mail or Calendar.

For more information have a look at this page

Usually, I deploy my app is run as developer. So, I will use the developer account credentials to perform all the script. It is the same for the Google BigQuery API.

Fortunately Google App Maker, as Google Apps Script natively integrate this API.

1 — Go to “App Settings” > “Advanced Services” sections > “Add service” > add “BigQuery API”

2 — Query Big Query

In order to query a table you can use the sample code available here.

I have slightly modified it in order to format the JSON and return in on the client side.

It will be easier to do an advanced search with this formatted JSON:

Google Apps Script function to perform a BigQuery API query which return a formatted JSON

That way you will have a formatted JSON easier to manipulate on client side. You can store it if you need to use it on multiple functions to perform filters or queries.

3 — Architecture wrap up

Use Google BigQuery as the data warehouse for your company data.

Use Google CloudSQL as the relational database where your app data will be used.

Use Google App Maker:

As the UI for your end-user and for your specific use case. Make your app data as light as possible. Store only what you really need. Google App Maker is made to create simple apps. Plus, It will ensure the performance of your apps.

To secure efficiently (from page level, to field level or with script, …) access to your app data hosted in CloudSQL

Below, the architecture of the app I present in my previous post:

Example of App Maker appsarchitecture

Use Google Groups to define the end-users who will use your apps.

Tips: Avoid huge nested Groups ! It won’t work properly for now.

Go to the App Maker Guides sections for more information.

Use Google Data Studio for your dashboards, it can be embedded in your apps. It is easy to create and to customize. It can be interconnected to multiple source of data.

Cleo and Chris made a presentation at Next on how to integrate DataStudio with App Maker

Use BigQuery API each time you need to retrieve other data for your apps. You do not need to store it in your App Maker app.

Thank you for reading,

Feel free to comment, ask questions/feedbacks and thumbs up if you enjoy reading.