Admin GA4 with Google Spreadsheet

Yesterday I tweet that I saw that recently Google Spreadsheet added the Analytics Admin service for Google Analytics 4 (GA4). This built-in service help us to code easier with other Google Products(1).

What this means? That we can use the API of Google in our Google Spreadsheet. This bring us a lot of versatility to, for example, list all the GA4 Accounts, or all the GA4 properties... Or even create new ones. In this example I'm only going to show you how to list the GA4 Accounts and the GA4 Properties that you have read access, but you can do anything that the API allows you. And it can be coded in our Google App Script and output in our Google Spreadsheet. If you want to have a look about all the possibilities, here's the official documentation.

So, first we need to create a new Google Spreadsheet, and click in Extensions --> App Script

Once we are there we can rename the project with something that identifies it, in our case it can be ga4Management, for example.

Now we need to add the built-in service we have been talking about. For that, we click on the + button (Add a service).

And we click on Google Analytics Admin API, and press the add button.

Besides, we are also going to add another built-in service that will help us to create new sheets on the spreadsheet and other features related with the spreadsheet itself.

The Service is called Google Sheets Api, and for invoque at it, instead of using the object Sheet, I renamed by SpreadsheetApp

After that, the rest is coding, but don't worry readers, at the end of the post, I'll share with you a copy of the Spreadsheet and you can make a copy of it in your own Google Drive. I've tried to commented it and also include console.logs in case you want to debug it.

For last, just mention that in the beginning of the code there's an onOpen function that runs when we load the spreadsheet but as we are creating it now, we are going to force this method to arrange the proper permissions.

Et voila, first time you click on run, for the onOpen function.

And it will ask you for permissions:

So you will have to click in advance

And then select the option that ends with (unsafe). 

Next window will inform us about the permissions we are going to grant to this app using our gmail account, and click on Allow

We click on Run again with the difference that it will appear execution completed instead of asking us for permissions.

Piece of cake, once the execution is completed, a new menu on our spreadsheet called "GA Management" will appear

Using this menu, now we can list all our GA4 Accounts and all our GA4 properties.

And that's all folks !!!

This is the link of the Google Spreadsheet.