Google Analytics 4 streams in Google Sheets with the Admin API

In the past months we have all been working hard to implement our Google Analytics 4 (GA4) environment. Something that we are less accustomed to, is using data streams. A data stream represents a source of incoming data, being web, iOS or Android. Web streams have a measurement ID which is implemented in the Google Analytics 4 tracker to collect data. iOS and Android apps are identified with a Bundle ID or Package Name, respectively. When these apps are used as streams for a property, these identifiers are displayed in the stream configuration.

Many of us manage a GA4 jungle with multiple accounts, multiple properties and lots of streams. Unfortunately, there is no possibility to filter on the previously mentioned stream identifiers in the GA4 UI. Thus, it becomes hard to see what property a certain stream identifier is connected to.

How are we solving this?
Fortunately, Google recently published the Alpha and Beta channels of the Google Analytics Admin API. This API lets you export, create or update GA4 accounts, properties, streams and contents such as custom dimensions and conversions. It is still an early preview version of the API, so it is subject to change. However, a custom setup still gives us a nice overview of all the accounts, properties and streams where we have at least ‘read’ access.

The custom setup in Google Sheets is built using JavaScript in the Apps Script add-on. In Apps Script, we have connected both the Universal Analytics (UA) Management API and the GA4 Admin API. Somehow the GA4 API could not retrieve all our GA4 accounts, even though we have read access to them. As a solution we first retrieve the account IDs with a UA API request. When all the account IDs are returned by the UA API request, we insert these into the  GA4 API request. In this way we were able to retrieve all of the account data. Once the account data is retrieved by the initial GA4 API request, we can do any GA4 API request we desire. It may sound complex, but luckily you do not need to think about this since it is all configured in the created functions.

To create a hands-on setup where no technical knowledge is required, we built a couple of functions that can be activated with a single click from a drop-down menu in the sheet. Whereas it takes quite some loading time to load accounts, properties and streams at once, you also have the possibility to either load just the accounts, properties, streams. Additionally, there is a function to clean up the sheet or recreate the tabs in case you mess up. Soon the code will be expanded with functions for retrieving GA4 custom dimensions and conversions.

Please note that over time the UA API that is used in this script might be depreciated. By the time the GA4 Admin API is no longer in Beta version, we will update the script to retrieve the account data with the GA4 Admin API so that it remains functional.

Click this link if you are interested in the code

Leave a Reply