[Preview] Rich reporting with Data Studio

[preethamm]

We are excited to announce the preview launch of AppSheet integration with Google Data Studio. Data Studio is a rich reporting and dashboarding tool that is available to AppSheet subscribers at no extra cost. Use Data Studio to generate fully customizable visualizations of your app data and combine data from multiple data sources to generate a rich dashboard to share with stakeholders. We are looking for users to build reports on Data Studio, provide us with feedback including bugs and ideas for enhancements.

Access requirements: You must be an app creator to visualize your app data using Data Studio. You need a Google account to use Data Studio. You can only access your AppSheet apps created with the same Google account. 

Why use this feature:
This feature lets you reuse your existing data model from the app in Data Studio. Without this feature, users will have to directly use their underlying data source(if supported) in Data Studio or other BI tools, and recreate the data model and then configure reports - a tedious process. AppSheet Connector for Data Studio avoids this step by making app data and its model directly available in Data Studio.

Preview link: To start creating the dashboard for your apps, you can access the Data Studio Connector via this link. This link must be visited at least once to activate the feature.

Updates as of 6/15/2022

- Configure data freshness down to 15 mins, with default being 1 hour.
- Data Sources are named based on the App name and Table name instead of "AppSheet".
- Data source allows changing the App and the table in a pre-configured data source.

Get Started:
To help understand the Data Studio features, we created a quick tutorial here. This tutorial will help you learn how to:

• Create a Data Studio data source linked to a table in an AppSheet app.
• Create a Data Studio report using this data source.
• How to Use your new report and  drill-down across dimensions.
• Schedule an email delivery of the report as a PDF.

Learning resources:

• Welcome to Data Studio
• Introduction to Data Studio
• Connect to AppSheet

How do I give feedback?
Please add your feedback directly to this post!

[Marc_Dillon]

Awesome!

I was able to follow the connector preview link, connect to my Appsheet account, select an app, select a table, and create a report on the source. Including virtual columns!

[Joseph_Seddik]

GRREEEATTT !! I've been waiting for this integration 😁 Thank you!

[Hamlet]

Finally! 👍 👏

Thanks!

[Koichi_Tsuji]

I assumed this connetors will automatically establish "blending" tables, based on the REF column which is set in AppSheet side, using GDS blending multiple table feature (introduced earlier this year in Feb/2022. However, it appears to me we have to re-create such relationship between tables after reading multiple tables from AppSheet app.  Currently REF type column in AppSheet tables are not supported?  This part of process (establishing relatinship in GDS) is tedius, and hoped new connector will makes our life easier to start buiding dashboard, retaininng the relations between table which are defined in AppSheet, more simply speaking the connector respect REF type column in AppSheet. 

Or am I missing something?

[preethamm]

Thanks for trying the feature! You are correct. We do not have the new "blend" feature based on the Refs. This is mainly because Data Studio can only import one table a time and blending is only available at report level(it is not a real data source). We are working with Data Studio team to see if we can do something here but we do not have any concrete solution yet.

If there are enough community requests for this, it would help us prioritize this with the Data Studio team.

[Koichi_Tsuji]

@preethamm  Thanks you for your clarity, understood.

Yes, data blending in GDS (GDP?) is only happening on report level, which is a problem to me, making our works less productive.  We make appsheet app and set up schema (connect tables), but we need to do those process again with GDP, which I have to admit cumbersome.  Lack in auto-generating ref (blending), the benefit using with this new connector seems to be "GDS would read VC values to draw the chart" For the rest to build dashboard, the process would be exactly same as we connect to other data sources such as spreadsheet.

I just conducted a quick test (just a single shot), and earlier feedbacks are : -

1. Only able to add one table at one time from appsheet app.
2. Probably I m missing the right way to do it, but it seems we have to disconect a connector which was connected to a certain app in order to add connect with another appsheet app. We need to disconnect the connector (Which will break up the report) and then re-select another app to establish the connection.

3. As highighted here in this post, the data blending process is problematic, especially to connect app with large number of tables where each table has connection with others.

4. This could be native and inherite problems with GDP, so new connectors should not be blamed for, but the data sets we establish connection with blending is not re-useable in other place. We have to repeat the blending repeatedly all the time when we create new report file.

5. Wish to have capability to add multiple tables from app via single action rather than adding new tables from report and mix.

Will test further, but hope this will help.

@takuya_miyai 

[preethamm]

I was added these as our action items to investigate further. 

Re [2] You do not need to disconnect the data source. You could create one data source per table. Then you can use them in multiple reports.

Blending not being reusable definitely increases manual work. A potential workaround is to create the "blends" in AppSheet itself by adding columns or creating derivative tables. Then you can use it in Data Studio without manually creating blends. However, this depends on how data is structured and is not a universal solution.

[Joseph_Seddik]

@preethamm 

Data freshness is fixed at 12 hours for AppSheet and cannot be changed, while with Google Sheets you can choose 4 hours, 1 hour or even leave it at the default of 15 mins. 😑

So I should tell the customer you have this great opportunity to have your reports displaying every 12 hours instead of 15 mins?!! 😖 That's a deal breaker.

[preethamm]

Good point! 12 hours is the default refresh rate and it is too big. I will discuss with the Data Studio team on lowering this and get back to you.

[Koichi_Tsuji]

@preethamm 

We currenlty dont have option for "iframe" globally in AppSheet. But for this data studio report alone, we wish to have option to add report through Iframe inside AppSheet app. Lets say, new view type is added to view settings and we simply past embed code into it to display dashboard as it they are built in dashboard inside AppSheet. Clicking url to open dashboard externally wiht browser is current option, but embeding report inside appsheet is more rubust. Thank you.

[Suvrutt_Gurjar]

Thank you very much @preethamm and AppSheet team for providing GDS integration capability.

I also very much second the request by @Koichi_Tsuji . GDS capability is definitely great and  will add great value and help get a bird's eye view analysis of aggregated data of the entire app involving all app users/ all zones/ all stores / all time frames/ all orders  / all projects' progress that the app data provides.

But I think an app is equally used extensively by each user of the app. In that sense either iframe  for GDS or strengthening charting capability to display natively in the app itself will go a long way. Each user will want to see graphical representation of my orders/ my sales/my attendance/ my tasks in my project, preferably in the app itself.

Request you to include iframing GDS / native display of charts in the app in your roadmap.

Incidentally,  this recent post by another community member , just asks the same thing showing GDS in AppSheet app:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Show-Data-Studio-in-AppSheet/m-p/408681#M160685

[preethamm]

We will definitely look into this! I think this would be a great add to the desktop functionality. However for mobile, this will not fit well and we might have to do something custom.

[Koichi_Tsuji]

Currently, interval to refresh data souce is 15min 1 hours etc.  Only the Report owner and co-author can manually and explicitly refresh the cached data to reflect the most latest backend data onto the charts. I know this should be thrown to Google Data Studio (or workspace) community instead of here, but GDS chart could work with Appsheet where any user who access to the dashboard/report could trigger such REFRESH option. App user edit the data, but they have no optoin to immidiately to refresh the report, then it will lead to the claim for sure.   The realtime refresh could be difficult in view of the GDS current architectures, but at least we should present option to manually refresh the data, like we SYNC the AppSheet app to pull the most latest data over the app. 

Once we present the GDS report to the user concurrenty present the appsheet app, user update the data from the app, but they surely claim the chat / reprort is not updated immidiately (min 15 min to wait to refresh. They dont understand why they need to wait.) Hence to solve this possible problem,  heavy re-load to refresh GDS chart should be presented to all the user who are allowed to access to chart rather than giving this optio to owner/co-author of the report.

Thank you.

[Joseph_Seddik]

@Koichi_Tsuji Where do you see these 15 mins please? I can only see 12 hours that cannot be changed. With Google Sheets as a data source, yes. But with AppSheet, no, it is 12 hours fixed at my side. Thank you!

[Koichi_Tsuji]

Hi @Joseph_Seddik  Sorry, my comment should have been unclear.  As you pointed earlier in this thread, I also saw only 12 hours, did not find any other options.   15 min should be shorted interval when we connect other sources such as google sheet.

[Joseph_Seddik]

@Koichi_Tsuji Sorry for my misunderstanding and thanks much for the clarification!

[takuya_miyai]

Hi @preethamm 

Thank you for releasing the functionality to further utilize AppSheet.😃
I have two pieces of feedback.

The name of the data source is always "AppSheet" by default.
It would be preferable to add it in the format "Application Name: Table Name".

I think the message at the point of selecting a table should be "Click "Add" to continue".

[preethamm]

Thanks for trying the feature and pointing these out!
- Renaming the data source automatically is a good idea. I will bubble it up in our action items.
- "Connect" -> "Add" : The button shows "Connect" text for me. This might be some translation issue and I will look into this further.

[Koichi_Tsuji]

Good point, @takuya_miyai 

In terms of the default data source name after reading table from AppSheet, it might be better to give a name, by concate the name of app + name of table.  Once we go on the journey with GDS + AppSheet, we keep adding the tables from various apps. Some table may have the same name. It is posssible we have the table with the same names (contents different). To get an idea which table and from which app at a glance of the list of data source, naming the data sources by concate app name + table name probably make us easier to find out who they are.

[takuya_miyai]

Hi @preethamm 
When adding a Table from a report, it is an Add button.

 

When adding a Data Source, it is Connect, so maybe the terminology is not unified in Data studio to begin with.

[CorettaJackson]

Excited to set this up! 

Thanks!!

[PS_Camunit]

AppSheet connector doesn't show up when i search under data studio. Am i missing something?

[Koichi_Tsuji]

You need to access to connector from this link.

https://datastudio.google.com/u/0/datasources/create?connectorId=AKfycbxy0_bVIUsKVRWtvA0fJfEq_F_wdP2...

[PS_Camunit]

Ohh, thanks a lot!

[preethamm]

Thanks for pointing it out. I have updated the post to highlight that the link must be visited atleast once to activate the connector. This is required during the preview only. 

[takuya_miyai]

Hi @PS_Camunit 

Something about Authentication was required.

 

[preethamm]

Thanks for snapshot. I have updated the post to highlight that the link must be visited atleast once to activate the connector. This is required during the preview only. 

[Koichi_Tsuji]

If I'm not on my own conclustion, wondering if or it is helpful or not that we could read AppSheet SLICE to add table to GDS?

[PS_Camunit]

Thanks @takuya_miyai, managed to get it working!

[takuya_miyai]

@preethamm 

Please point out if I am wrong, but it seems that co-author cannot be added as a new data source.
I can select the app, but the table does not show up.
If that is correct, then I would like to have the co-author apps excluded from the app selection list.

Also, once an app is selected, I cannot select another app again.

@Koichi_Tsuji  

[preethamm]

Thanks for pointing this out. This is a bug. I am working on a fix. 

[takuya_miyai]

Hi @preethamm 

I see that the Security Filter settings are reflected.
This was unexpected for me, but a pleasant result.🤗

Currently, only Owner can be specified as a data source, but if Security Filter is available, it would create an interesting use case if each app user can be specified as a GDS data source with their own authority.

Administrators will be able to create reports based on all records, and general app users will be able to create reports using only their own records.

@Koichi_Tsuji 

[takuya_miyai]

Hi @preethamm 

The numbers numbered in Data Studio by default, but this can be confusing with AppSheet _RowNnumber.
I think this is unnecessary if AppSheet is the data source.

 

@Koichi_Tsuji 

[preethamm]

I will check with Data Studio team and see if there is a way to hide this.

[TeeSee1]

In the Style menu of the Table graph, there is a setting to control the display of row number.

[Asheetbogo]

connection to google datasudio not possible - no app shown for connection ...

when i try to connect a table of my appsheet apps no app is shown for connection ....

1. Select the app to access the data source in the Application field.
2. Click Next.
3. Select a table to use as the data source in the Table field.
4. Click Connect.

AppSheetHelp:

a) You must be an app creator to visualize your app data using Data Studio.

b)  You can only access your AppSheet apps created with the same Google account.

>>> a) ... my apps are all "prototype" apps at the moment - is this a concern?

>>> b) ... i created my apps with "dropbox"-login - is this a concern?

many thanks in advance for your help!

[preethamm]

>>> b) ... i created my apps with "dropbox"-login - is this a concern?

You will need to create that apps with the same google login you use for Data Studio. Dropbox login will unfortunately not work.

A workaround would be to add your google account as the co-author to access the app. Unfortunately this is not working yet. I will add a response here once I fix that access.

[MrLeeVn]

"Data partitions" doesn't work.
Read only main table data?.
Is there a way to read all the data?
Thanks!

[preethamm]

Good question. Unfortunately we do not have a good workaround to get all the data partitions. I will need to look into this a bit further to see what we can do.