Hey Olaf B.!
The COPY ... TO does actually work. One catch is that a gsheets community extension can't be loaded in the MotherDuck UI, since MotherDuck only supports the core DuckDB extensions server-side.
You can run it on the DuckDB CLI on your own machine as follows:
INSTALL gsheets FROM community;
LOAD gsheets;
CREATE SECRET (TYPE gsheet); -- opens a browser window to sign in
ATTACH 'md:';
COPY (SELECT * FROM my_db.main.my_table)
TO 'https://docs.google.com/spreadsheets/d/<sheet_id>/edit' (FORMAT gsheet);
The extension runs locally, pulls the rows from MotherDuck, and writes them to your sheet. If you want it to land on a specific tab, you can add sheet 'TabName', create_if_not_exists true to the options. Here are the docs for further reference.
If you want it configured in the UI, you can set up a Flight. We have a ready-made recipe. It does the same COPY with OVERWRITE_SHEET TRUE, so re-running replaces the tab cleanly. This one needs a Google service account key, and the sheet shared with that service account's email as Editor. One thing: on the free plan, Flights run on demand (30 min of compute a day), and scheduled runs need a card on file, so out of the box this gives you a one-click export rather than an automatic nightly refresh.
If it's an occasional export, I'd start with using the extension, since it's the fastest path. If you want it repeatable, the Flight is a more robust way to go.
We do have an open feature request to support the Google Sheets extension directly in MotherDuck. I've added your name to it so you'll be counted when it's prioritised.
.png){kind=logo}
