How to prevent 'Ref' error when sink to spreadsheet with overwriting mode.

Short answer: use INDIRECT function.

When you apply overwriting mode for spreadsheet sink, 'Ref' error occurs if you set function that referring to cells on overwritten sheet.

For example, some cell contains this function before the batch runs: =SomeSheet!B1 . And after executing the batch, the sheet is overwritten and function cell is going to be =Ref .

To prevent this, you can use INDIRECT function like this: =INDIRECT("SomeSheet!B1") .