AppSheet Office Hours Recap: Data Source Connections Featuring Google Drive Folders

[Lauren_vdv]

This month, the Community came together for our March AppSheet Office Hours on data source connections. We heard from several members of the AppSheet team, including Product Manager, Arthur Rallu, Engineering Lead, Philip Stephens, and Product Manager, Peter Dykstra, who took us through an overview of AppSheet data connector options, data scaling and performance best practices, and Google Drive folders as app data.

If you didn’t have a chance to join Office Hours live, no worries! In this post, we share the session recording, written questions and answers, as well as supporting documentation and resources, so you can refer back to them at any time. 

There were many questions that we didn’t have time to get to on the call, but we did our best to add those to this post as well. If you have any further questions or if you don’t see yours below, please add a comment to this post and someone from the AppSheet team or the Community will be happy to help!

Session recording

Questions and answers

1. What is the status of the change coming to Google Drive, with the potential impact on photos? Is there an official step-by-step remedy? 

For those of you who may not be aware, the upcoming Google Drive migration from multi-parent files to shortcuts might cause apps to stop displaying images.

The current status of this is still ongoing. The rollout depends on many factors, including if your Google Workspace Admin chose to get releases on a fast or slow track.

If you’re affected by this change, @kamila_klimek has published instructions that let you know what to do, and how to prevent the issue in the first place.

2. What is the recommended way to organize app-related data in Drive folders?

When apps are created, a folder structure for app data is automatically generated in your primary cloud data source (which is usually Google Drive). When a file or image is uploaded within an app, this is where it’s being saved. 

But connected data tables are frequently located in other locations - usually hosted in the app creator’s personal Drive folders. It depends on your use case, but it’s generally recommended to use Shared Drives to organize your data. 

More information on utilizing data from a Google Team Drive can be seen here, along with tips for data organization here.

3. When making a copy of an app, is it possible to copy some tables completely and some just with headers (no data)?

Anyone can create and share app templates for others to view and copy a version into their own account. When copied, templates will copy the table and anything in it to the new copied version of the app - it’s not possible to pick and choose which tables or which records get copied. A good practice when making and sharing templates is to remove all records (leaving only tables/columns) or ensure the records contain no sensitive information.

This is important to keep in mind - make sure you’re not sharing data or records that are sensitive when you’re creating app templates or sharing copies of apps.

4. After I have an app ready, is it possible to split the Google Sheets data source? I’d like to split my current data source sheet into two or even more separate sheets.

Yes, this is essentially data partitioning. You can start with a single Google Sheet and then split it after your app has been deployed. Learn more about how to use data partitions in this article here. 

5. Is there any plan to read ‘subfolders’ once we select a primary folder as the table for AppSheet? 

It would be beneficial if we could read the files in subfolders and the AppSheet table returns the name of the folders as additional metadata.

This is something on our long-term roadmap. There are scaling and performance challenges to address with this, and right now, we’re of the mindset that it’s better to keep it more controlled, (so your apps can continue to function correctly) until we have a better way to manage the scale. 

6. Can you rename a Google Sheet once its data is connected to the app?

Yes - the app will continue to run because it connects to the Sheet using its ID, not its name.

However, keep in mind that AppSheet is not re-reading the name of the file. So in the editor, there will be a mismatch, which is less than ideal. If you rename a tab, you will need to update your app definition (worksheet name/qualifier field) or it will break the app.

7. When you copy an app to create a new one, the related sheet is copied nicely inside the app’s folder structure. 

But if you start from scratch with a new sheet, what is the recommended way to change the sheet’s location into another folder and update the app accordingly?

Changing the location from one folder to another does not impact the app, as long as the app still has the right permissions to access the data in the sheet. 

The app identifies the sheet by its ID, not its location.

8. Is synchronization with AppSheet faster with Google Drive or SQL? 

Usually SQL is faster when there’s a large amount of data and when you start using security filters. The general rule of thumb is that SQL is faster with the right configuration and provisioning for the load. 

With that said, it wouldn’t be surprising if you find cases where Google Drive is faster. 

9. I’m creating a to-do list app with a repetitive task function.

I know how to automatically set the deadline to be after a few days, but is there a way to automatically set the deadline to be after one month, six months or one year? 

Yes. Regardless if it’s a few days, one month, six months, or year, it depends on what the initial value of the due date column is and the unit of time being used. So in the due date column, you’ll want the initial value to include an expression with the creation date and a certain amount of time (length of time until the due date). 

In this task management example, it’s set so the default deadline delay is based on hours from right now - technically you could just make it 720 hours to push the deadline out a week, a month, etc.

10. Currently, when we specify a file as a Google folder data source, only the metadata information of the file will be recorded.

Are there any plans to add user-modifiable columns in the future? 

We’ve investigated some approaches to this feature. There’s a way to attach user-modifiable information to the Drive files. You could also create other tables that are linked through a shared key with a file in Drive. 

So there are a few options. It’s something we have planned, but we’re thinking through the best way to do it.

11. Is it possible to update or delete Google Calendar events through AppSheet?

I get the error: ‘Unable to add/edit/delete row in table 'GCal'. → The given key was not present in the dictionary.’

Yes. You can connect to a Google Calendar and create, modify, or delete events from that calendar. We’ll need to follow up to get more specific details about your app to help troubleshoot this error, but this is sometimes related to a connected table, a calendar being renamed, or permissions changing.

12. Is it possible to pull data from a user’s Google Workspace profile into my AppSheet app (i.e. mobile phone, profile photo, etc.)?”

There’s not a direct integration with Workspace User Directory today, but there are options for using the Admin SDK Directory Service and Apps Script to automatically pull your user directory into a Google Sheet, which you can then connect to one or multiple apps. 

Additional instructions can be seen here.  

13. Is it possible for AppSheet to create an app that’s used for thousands of users and they could sync or write update data to SQL server by this app? 

For example, a retail chain that has thousands of stores - everyday these stores sync or write all of their sales data to SQL server by this app. 

Yes. There are many teams that have thousands of users using a single AppSheet app or a collection of apps syncing back to a single or multiple data sources. There would be considerations unique to your situation in regards to what the user experience should be for the employees, and whether they are having to load large amounts of data frequently to do their job, versus simply collecting data and ensuring it gets centralized throughout the day. 

There are many retail use cases that are ideal for AppSheet, like daily task management, shift scheduling, health and safety inspections, hiring and HR operations. You can explore app templates by industry, feature, and function here. 

14. Does AppSheet offer specific support for apps in the health industry that frequently involve private information? 

If your app processes personal or sensitive data, you can mark it as containing “Personally Identifiable Information” or “PII,” which will prevent it from being retained in the system logs. You can do this either on a per-column or per-app basis. More information on how to do this can be seen here. 

For customers who are subject to the requirements of the Health Insurance Portability and Accountability Act (HIPAA), AppSheet can also support HIPAA compliance as a business associate (as the term is defined in HIPAA). This HIPAA Implementation Guide is intended to help you configure the data you send to AppSheet in light of the way our services work and the sensitivity of protected health information.

15. Can you use a mixture of a PostgreSQL database and a Google Sheet/folder? 

My specific question pertains to ways to create an onboarding view that can grab the titles and descriptions from the PostgreSQL database and the images from a Google Drive folder. 

Yes, this is possible. The way to go about it is to create a table view and make each of the records editable. You will have your different fields, including the image. If the image isn’t linked yet, you can upload the image there and it will place that image into your Google Drive. 

After you’re done with that operation, you can delete this view and just keep the onboarding view and your information and images will appear there. 

16. With Google Maps, can we configure our own routing directions? 

Routing within the app itself isn’t possible in a simple way today, but there are several built-in options, linking out to either Google Maps or other map services. 

Information on how to display location data, drop pins, get directions in your app, and more with Google Maps can be seen here. 

17.  Is there a way (or plan) to integrate Google Chat or Spaces Chat directly into an app?

See this post from @Koichi_Tsuji!

18. How can you connect a response from a form on an app to a shared company Google Calendar?

New events can be added to a shared Google Calendar via an AppSheet app form. See this documentation on using data from Google Calendar for details. 

19. With Ref_Rows, is there an easy way to do "save and add another"?

Yes, by enabling the “Is Part Of” feature for ref columns, you can add a series of child records directly from the parent form. See this example and test the “Inline child records form.”