I have an AppSheet app with multiple tables stored in a Google Sheet. These tables are logically related: for example, I have a Customer, Order, and OrderItems table. A customer can have many orders and an order can have many items.
I’m struggling with how to best join these tables to display a read-only table in AppSheet. For now, I’ve been using a Google Apps Script to programmatically join the tables to create a new table in Google Sheets that I bring into AppSheet as a read-only source. This seems to work, except I don’t know how to keep the data current. I’ve set up a trigger in Google Apps Script to run the script every 10 minutes, but I do have odd and intermittent failures like “We’re sorry, a server error occurred. Please wait a bit and try again.”
TLDR: I’m trying to do an inner join on tables in Google Sheets to display in AppSheet and I wonder what the best approach would be.
Yes, this person is essentially asking about implementing a one-to-many relationship in AppSheet. They have Customers, Orders, and OrderItems tables, with logical relationships:
A Customer can have many Orders (One-to-Many)
An Order can have many OrderItems (One-to-Many)
What They’re Struggling With
They are currently manually joining these tables using a Google Apps Script, which creates a new merged table in Google Sheets. The issue is keeping that data updated reliably, as their script runs on a timed trigger but sometimes fails.
Better Approach in AppSheet
Rather than using Google Apps Script to join the data, they should leverage references (REFs) and virtual columns in AppSheet to dynamically join the related records without needing an extra merged table.
Steps to Properly Structure the Data in AppSheet
Ensure Reference Columns Exist
In the Orders table, add a column [Customer_ID] as a Ref to the Customers table.
In the OrderItems table, add a column [Order_ID] as a Ref to the Orders table.
Enable Related Lists
AppSheet automatically creates a Related Orders virtual column in Customers.
AppSheet also creates a Related OrderItems virtual column in Orders.
Create a Slice for Displaying Data
If they want a read-only table showing combined data, create a slice that brings in relevant fields from Customers, Orders, and OrderItems.
Use a Virtual Column for Display
Instead of creating a merged table, they can use a virtual column in Orders:
CONCATENATE([Order_ID], " - ", [Customer_ID])
This makes it easy to show combined data dynamically in a read-only format.
Why This is Better than Google Apps Script
No need for periodic script execution – the data updates automatically.
Less maintenance – No API calls or external script dependencies.
I think it was making a single point about many; the core essence there is that if you’re wanting to display read-only data, while still having the data editable in the app, you would need to create slices for this. (Something I do all the time)
Pro Tip: The only way in which someone can get into the form (to edit the record) is through the Edit action. So if you hide that from someone that’s not supposed to be able to edit things, they can’t. This is a simple way to make your data read-only, without having to go through all the hassle of anything else.
Oh… you mean like… combine the customer and order and detail contents all into one. Yeah can’t do that, not sure why you’d want to. What’s the benefit? Or intended functionality?
In a nutshell, I’m trying to create tabular reports that display data from multiple tables simultaneously. For example, I want to join data from the Customers table with data from the Orders table to only show customers who have placed orders over the past 6 months and sort that by the the number of orders placed.
I could build a report using Looker Studio, but the functionality is rather limited, and I don’t want to force users to leave the AppSheet application to view and export data.
Maybe you are looking for something more complex but if you are looking for template reports, you may want to take a look at the following sample app
This app has exactly the same entities that you have described , “Customers” , “Orders” and “Order Details”. “Order Details” is equivalent to line items of an order.
In Salesforce, there’s an option in the settings that says “Join Table A and B.” Unfortunately, there’s no way to do this in Appsheet. I’m waiting to see if someone has managed to solve this issue.
On Table A create a VC. In the formula use SELECT(TableB[KeyField],[ForeignKey] = [_THISROW].[PrimaryKey]).
It is assumed that Table B will have some sort of identifier that relates it to Table A (foreign key).
Thank you, that’s close, but I’m trying to display a single table that will have all customers and all orders in the same table. That means there will be a row for each order and customers will be repeated.
The only native solution I could find to this was to add virtual columns to the Orders table and create a slice to show values based on specific conditions.
I didn’t need to use that formula. I set up a relationship between the Customer and Order table by setting the a reference column linking Orders to Customers and I was able to add VCs to the Order table by using formulas like [Customer].[CustomerName]
Are you are describing a standard one-to-many relationship?