Dynamic table connections

We are developing an application that will implement CRUD operations on a high number of sheets in Smartsheet that all have exactly the same column structure, so we want to use the same dasboard view in Appsheet to connect to all the Sheets (one at the time) and perform the CRUD operations on the dynamically connected sheet.

It is not possible to create one sheet containing all the data because of Smartsheet 5.000 row limitation and data seggregation requirements.

Is this possible in Appsheet without creating hundreds of identical applications?

Thank you for your input.

Kindly, Ole



Hi Steve,

Thank you for your swift reply Steve.

However, you sent med instructions on how to handle a few large tables.

What I am asking is how to handle many small tables with the same column structure and the ability to dynamically connect to the different tables (sheets in Smartsheet) so we can use the same view to manage the (hundreds of) sheets in Smartsheet.

A nice solution would be ability to use a REST web service as data provider. I can see that others have requested this and actually I would think this to be a key feature in Appsheet supporting a lot of important use cases.

Thank you in advance.

Kindly

Ole

How is that different from partitioned tables?

Hi Steve

  1. Partitions represent ONE table. We need separate tables with different access right for the different Appsheet users.

  2. The partitions are statically defined in the Appsheet model. We need to support a varying number of tables (sheets).

  3. We can provide the Smartsheet sheet ID when opening the connection.

Thank you.

Kindly Ole

As Steve highlighted, all the data table need to be connected to app through the appsheet editor. Unless the connection to the target data table is established, Apppsheet won’t have direct access to do any operation in terms of CRUD.

But now I understood your data source is Smartsheet and need to store the data to much of sheet in Smartsheet through Appsheet. Again to achieve that, in general and per Appsheet standard procedure, we need to add every single Smartsheet sheet to Appsheet and probably set the show/hide table/view based on the user log in IDs to show the Smarsheet sheet which is prepared for the log in user.

In case you have hundreds or thousands of sheet, yes, it could be just time consuming, moreover, it probably will not be an option for you.

I just though how we solve your problem, but I got only one solutions, using workflow, webhook on the ground that Smartsheet do provide the full set of API to undergo CRUD operation against their sheet. (I googled and found Smarsheet API docs, and found it is pretty much comprehensive and well organized. I never used Smartsheet before, but looks like we can do CURD using their APIs.)

Assuming Smartsheet do have APIs for full CRUD, then what about this solution? I know it could not be most ideal, but I just providing my thought how technically we can achieve your goal.

Firstly, we do create new table storing the Appsheet user data, such as Email which is used for authentication to access to Appsheet App. I m not sure the detail of your case, but assuming you do have independent Smarsheet sheet for each individuals. So I guess you do have Smartsheet sheet “ID” for each users. Then on this new table, just add column beside the user email to store such ID. Now we have Use email (ID) and Smartsheet IDs as pair on the new table, and connect this table to the Appsheet. I recommend you add the security filter to retrieve only one row out of this table for the log in users. (I don t mention to the details about Appsheet security filter, as they are loads of good docs on site)

Secondly, we do create the data source, ideally SQL or any other data source which will ensure the scale. If you don’t want to deal with SQL, then Google sheet should be option, as they could store more cells rather than Smartsheet, i.e 5 mil cells per sheet. I suppose this should be fairly enough, but depending on your case. If not, only SQL as data source is option.

On this data source, create new table, which contains exactly same data schema, i.e. data type and set of the column which is from your current Smartsheet.
However, add few columns which store the user email and Smartsheet IDs.

Add this table to Appsheet editor.

Now as you can see, this table is kind of UNIONED single table, which can be achieved by employing Appsheet partition functions, and storing data across all the users.
Similarly to the first step, add the security filter so that Appsheet read rows associated with the log in user.

Now we have two new table and then make the relationship.

The second table, user email column is set as REF type and refer to 1st table which store user data. And for this second table, the user email column, either App formula or initial value is set to USEREMAIL() with expression. This will automate the fill in with log in user 's email to this table when new row is added to this table. At the same time, for the SmartSheet IDs, use dereference expression, something like [Use Email].[SmartSheet Sheet ID] as app formula or initial value.

New table set up ends up here. This turns to be kinda of intermediate or middleware to bridge Appsheet app and Smartsheet.

Appsheet user play with all crud against this table.

Then let me move to the further steps.

We generate the workflow for all CRUD operation, add new row, update, delete. Luckily Appsheet can detect all those event against the data table.

Once any change, add, update or delete happens to the table (second one) then call workflow.

On the workflow, just set up the WEBHOOK, i.e. the API call to Smatsheet API.

I hope now you found out the trick.

Whatever event happens second table, then trigger API to call Smartsheet API. By doing so, SQL table will be replicated with Smartsheet sheet, technically speaking.

I don’t mention again about how to set up Webhook in Appsheet workflow, but Phil prepared nice docs on the knowledge base.

To summarize.

Use access to the appsheet app and can access to data / rows only assigned for him/her.

Do add row, update and delete to this table, and trigger workflow to call Smartsheet APIs.

I never created app like this, but technically it should work.

One last thing which is important.

To find out the target row in Smartsheet, I guess we need to have IDs for each row.
This is chicken and / or egg story, but each table, one for SQL and Smartsheet for another, both table should be completely identical including IDs, which is important.
To call Update and / or Delete, we should pass ID of the rows as their params and header of API call, to tell to Smartsheet which row should be updated or deleted.

To add new row, then get the ID from appsheet and then pass that value to Smartsheet to ensure the consistency.

Again another important aspect.

I just quickly add a look at Smartsheet API docs.

For calls, we need to specify the Smartsheet sheet ID. Now we can get this value from the Appsheet/SQL data, as we added column to store this value. Pass this value out of the row which are triggering workflow, and you can access this value in Appsheet workflow.

I dont think i m not missing anything important, hope you manage to solve your own problem with Appsheet, which is urtra super powerful.

On this process, no coding involved, apart from constructing API.

Good luck.


Additional note.

I suppose probably on your case the new sheet could be added to Smartsheet? In that case, use the API on the opposite direction. Pass the value of sheet ID along with Useremail through the API (not sure if Smarsheet API do have such a functionality though) and call Appsheet API to add new row to the user details table. (First table i mentioned in my post)

Or you can create the first table within Smart sheet. In that case, without involving Appsheet API, I can see you can achieve that. I m not Smartsheet specialist, but as far as I see their doc, there should be away to do this trick.

1 Like

Hello Tsuji,

Thank you for your very elaborate answer. It is greatly appreciated.

I have been on holiday so I have not read it in full until now.

I think the solution you suggest is a bit too complex and I would be concerned that it might be difficult to make stable and secure.

Webhooks and workflows combined with “shadow tables” could be avoided if Appsheet would support one of the following:

  1. At design time point to a class table the normal way. At runtime raise an event to identify the instance table that follows the interface contract defined by class table.

  2. A CRUD web service data source in addition to google, Smartsheet, SQL etc. The service interface would be acting as a virtual table, a proxy between Appsheet and the external data source. Making this feature would open not only for dynamically connecting to Smartsheet sheets, but to any data source.

Kindly Ole Erecius

1 Like