Revise the Google Sheets Drop Sheet tool

[joeschafer]

The behavior of an "Overwrite Sheet (Drop)" configuration is such that it breaks formulas (#REF) that point to the overwritten sheet and named ranges that reference the overwritten sheet.  This is a bummer because the only way I've found to overcome the issue is to write a script that re-applies the named range.  This works, but it greatly raises the barrier to using this tool and in some corporate environments it won't even be possible.

What would probably be a good alternative behavior is to delete the contents of the sheet, rather than the rows/columns/cells of the sheet.  I think both probably have valid use cases but my proposed functionality is going to cause fewer issues and be the more popular behavior for most users.  I believe there is a google sheets API call for just this kind of behavior...

[DanielH]

Hi,

I have been having the same issue for awhile now. I want the data on the sheet to be replaced but without deleting the sheet. One way I did solve this issue is using an indirect formula. Create a new sheet (Datasheet or name it whatever you want) and only place the following code in row 1:

=INDIRECT("'Output Alteryx'!A:A")

Use the double quotes only when there is no space in the sheet name. If there is a space (as in the example) use single quotes for the name.

Do this for every column (B:B - C:C) etc. and that way you can reference your new sheet which will be an exact copy of your output. Its a small workaround but currently works great for me.

[joeschafer]

Thanks DanielH.  Indirect works!  Kinda Janky but least-janky solution I've found so far.  I still think that formatting should be retained as part of the default functionality of the tool. 

[ARich]

Hi All,

Thanks for the feedback- please continue to upvote if you're interested in this functionality!

Best,

Alex

[Sbinkley]

@DanielH - thank you for this solution!  It's the least complex of anything I can try right now to get my stakeholder what they need.  Appreciate it!

[JessieC]

@DanielH +1

[Billfurst]

Hi, is there any update on this review?  Perhaps an early christmas present so I don't have to build 20 formula sheets to sit on top of 20 data sheets?  It's been under review for almost 2 years.

[mbraswell]

We need a truncate, added as an option.  The delete is really adding layers to an otherwise simplified process.  Any word on this issue?

[robertyu]

Great solution, @DanielH!

I had to resort to appending data but this workaround is super helpful!