In my app made in Pages, in the sheet that feeds the app, the first 10 columns or so are fed from an external source. A script updates the sheet every day, replacing the data in the first 10 columns with updated data. The first column of this data is a unique number which stays constant (a unique item number) and then the other columns have updated info about the item. Items are never deleted, but the challenge I’m having is that items are sometimes added essentially in the middle of the sheet. So on day one, you might have:
and the next day this:
Meanwhile, in columns to the right of this data I have, among other things, a user-specific column, the function of which, is to allow the user to mark or “favorite” an item. This was a boolean that when a button in clicked in the list, sets the value to true or when the button is clicked again, clears the value. This is a custom action that says “if the value is empty, set it to true; if the value is not empty, clear the value”
Back to the data, as those more knowledgable than me can anticipate, when new items are added in the middle of the data on the left that is being fed to the sheet, the columns to the right lose their association with the data on the left. The data on the right does not shift down, essentially. In the addition it seems in the example above, A-003 now has the Glide Row ID that had been assigned to B-001, so now if the user looks at their favorites it shows A-003 rather than the item they had favorited.
Is there a simple solution to maintaining an accurate list for which items have been favorited in the sheet? I’ve explored the technique described in other posts of using an action to set the column value in a different sheet, but that seems to have a limitation for this use, namely that, unless I’m missing something, clearing that value from a button in the main sheet is not as straightforward as setting the value. If I create a “user favorite” sheet, I can set the column value in the second sheet to record the item number and that maintains the integrity of the user list, but I can’t seemingly just “clear” the value in the second sheet if the user wants to un-favorite it.
So, (A) is there a simpler way to accomplish my goal of an accurate user favorite list using a user-specific column that is stored in the main sheet or (B), is there a simple way to clear the value in the second sheet? Or (C ) Some other approach?