How can I manually edit empty cells that are within an array returned by an ArrayFormula function?

Question

We have a Google Form whose responses are stored in Google Sheets, and that data is used by formulas in other sheets.

For example, one sheet uses the following formula to return form response data from columns A to K:

=Arrayformula('Key Sign In'!A:K)

The formula above works however the problem is that we also need to manually enter key check-in times in the empty cells in column J of the formula's results. Whenever any value is added to an empty cell in column J, the formula results completely disappear. Undoing the change Ctrl+Z restores the formula results.

We could instead edit column J of the sheet that is linked to the form, but involving multiple sheets makes that approach too complex for some of the users.

Is there a way to add data to column J of the formula results while avoiding this issue?

Answer 1

You are using an array formula to copy data to columns A1:K. Formula results are always view-only and cannot be edited.

One solution is to mark the key return times in column L instead of column J.

Another solution is to use two array formulas, one to copy columns A1:I and the other to copy column K1:K, leaving column J editable.

Yet another solution is to use a script to copy form responses to the other sheet as they arrive. Values written by a script can be edited normally. The script can run on an installable "on form submit" trigger.