How to maintain a permanent record of rows from a source that changes over time?

AppSheet AppSheet Q&A
,
1

I have two tables, both with identical column headings.

Table 1 is a consistently formatted output from another application.
Every row has a unique “OSM ID” field to identify that row.
In each iteration of the table (the output): some rows will have been deleted, some rows will have been added, some rows will remain but are updated (never the OSM ID), and some rows will remain unchanged.

I want to have Table 2 as a “master table” in Appsheet that records every row in the source table, adding and updating rows as needed. No rows should be deleted from Table 2 but instead marked “deleted” somehow. Thus building a permanent record of OSM_ID rows.

I have a feeling this isn’t complex to achieve, but - despite much research and testing - the solution eludes me.

Suggestions and recommendations are requested.
Happy to clarify anything.

2

So I’d use a bot on say an hourly schedule to:

  • Copy any newly created rows and add them to table 2
  • Mark rows now missing from table 1 as deleted in Table 2
  • Update rows in Table 2 where they have been changed in Table 1

Appsheet can’t itself ‘see’ changes made to a spreadsheet. So you’re forced to either use a scheduled bot. Or create a Google Apps Script to trigger the Bot from within Appsheet.

Simon, 1minManager.com

1 Like
3

Thank you Simon - seems like a realistic approach.
I’ve not used Bots before, do you know of a Template or tutorial what could give me a starting point for developing this?

4

Checkout https://support.google.com/appsheet/answer/11432969?hl=en&sjid=7760132014665515006-EU

1 Like
5

Schaue folgendes kurze Erklärvideo: Bearbeiten, Löschen und Kopieren einer Zeile in eine andere Tabelle | Automatisierung

1 Like