Published google sheet with scripts not working

Question

From my previous post at Using one script for copying text from cells in a row to another sheet using an icon in a row I have made a great solution for moving a row by marking a checkbox thanks to @Tanaike.

I need this spreadsheet to be published online and anonymous users to mark the checkbox in column A. When they click the checkbox the text is moved to sheet 2.

The thing is. The script works fine for an anonymous user with a link but not when it is published online.

I have made a test sheet for you to look into. What am I doing wrong?

Sheet with a link for anonymous user that works fine: https://docs.google.com/spreadsheets/d/1z5TGWp3N002z0GNts4NRyspkjXcsN52rxPtx8Pu9D70/edit#gid=0

Sheet with a a published link for anonymous user that doesn't work https://docs.google.com/spreadsheets/d/e/2PACX-1vT7xrS6irhVTHQzsvM-RdBU6rTDyWFGtHlfTMTAGzG3OqxTnL4uoEKfNo5jxrbvHppAox2VXOnev4yP/pubhtml#

Any help is much appreciated.

Answer 1

When you publish a file via File > Publish to the web, you make the file contents visible to anyone, but these contents are not the same as the file itself. These published contents cannot be directly updated (the content will be updated if you edit the spreadsheet where the data comes from) and, of course, you cannot fire an Apps Script trigger from there either.

If you want anonymous users to be able to fire the script when they click the checkbox, you should share the file itself via Share > Advanced > Public on the web, and change the access role to Can edit.

Reference:

• Make Google Docs, Sheets, Slides & Forms public