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

[gcor71]

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.

[1minManager · Accepted solution]

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

[gcor71]

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?

[Tommy62]

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

[1minManager]

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