I have created an app that has a sources table in googlesheet and everytime that I update the table is overwritten in the existing data, which leads to a loss of historical data, while I update the first table I want all the changes record as a new row in a second table
Hmm… Have you thought about adding a column to your sources table that can act as a counter?
Create an action that you press each time you update that increases that counter, and then create a workflow that looks for updates in the counter, and moves that data to another table.
Or… you could create a new table that you select whatever item, and it updates with the initial value referenced by your source table. The user can then just over ride that initial value if they don’t like it.
EDITED: Scratch my idea. I had interpreted your need incorrectly.
Keep the first table as it is. This is the table users interact with.
The second table, for the historical record, should contain the same columns as the first table, or at least those columns you care to track–it doesn’t have to be all of them. You must include the first table’s key column, though.
The second table needs a different key column, as the first table’s keys won’t be unique in the second.
Create a workflow on the first table for adds and updates that creates a new row in the second table from values in the added or updated row of the first. This ensures the second table always has the latest values from the first.
Yeah! What @Steve said.
But I want to be able to keep the history of each change, if I do that it will update the information just like the source table
If you do what Steve suggested above, you will have a workflow add a NEW row every time you make a change to the original source table. If you add a new row to the source table and then change the row 10 separate times, you would have 11 rows in the second table.
Is that not what you want?
that is what I want but that does not happen with what steve said, it is written over everytime I make a change in one specific row
Where is this happening? In the first table or the second table?
Just to be clear, to my understanding, it is expected to work like this:
- Add new row to source/first table.
- Workflow adds new row to second table with same info from first table row.
- Change the row in 1).
- First table row is updated with change upon save.
- Workflow adds new row to second table with the same info, including changes, from first table row.
- Repeat 3) though 5) for each change made to row in first table.