Flag record in read only table using data from import range

[Paras_Sood]

Hi community,

I have created an app whose database is on google sheet and values get updated based on an import range function, pulling data from another spreadsheet.

Ideally, I only want to flag a record, by highlighting it as red by clicking on a button if I see something wrong with it, I cannot update the values directly through the app because this is pulling data from another spreadsheet. I tried to use a virtual column, but I cannot update a virtual column using behaviour actions. Is it possible to achieve this?

[dbaum]

The following might be an approach:

• Add a Flag column in the spreadsheet and regenerate the columns in AppSheet
• Change the table in AppSheet from read-only to instead allow updates
• Ensure that the columns that shouldn't be editable have the Editable? property deselected and that the Flag column's Editable? property is enabled

Then, you can create your action and format rule that set and reference the value of the Flag column.

[Paras_Sood]

Thanks for your reply dbaum, however my concern is that I am using an import range statement to pull data from another spreadsheet as the backend for this appsheet. Therefore, I am not sure how the "flag" column will be bound to the record that I want to flag, which can get updated at anytime as soon as a new record is added. My import range statement pulls in data only for the last 90 days, therefore the import range statement keeps dynamically updating the data

[dbaum · Accepted solution]

I see. I didn't realize your source data changes frequently. I assumed the scenario was more like it updates daily and you needed to be able to flag a row only for immediate reference.

Consider whether there's a way within Sheets to keep the Flag column's value in the row with the data where it was intended to be applied. Maybe sort the import range so that new records are always at the end? Maybe there's an applicable Apps Script technique? As part of your approach, perhaps you could move the 90-day filter from the sheet to the app--e.g., the table's security filter?

Alternatively, maybe you need a shadow table with 2 columns: an ID column that can be used to reference a row from the main table, and the Flag column. You could probably create an Apps Script to add the IDs to the shadow table periodically or maybe even every time the main table gets a new record. If helpful, a script could also probably remove IDs from the shadow table when they disappear from the main table after 90 days.

[Paras_Sood]

I like the first alternative, however, I have around 6000 records in the table that I am importing from, I don't think that should be a problem. Thanks for your suggestion!