I am developing a Google spreadsheet that I want to make publicly accessible to a community. I've released the first version of it, and now everybody in the community has that first version's link bookmarked. I am calling it released
In order to develop version2, I need to do a lot of changes. While developing all those changes, I don't want the community to be affected, so I made a copy of the released spreadsheet and called it version2. I've made my changes in version2 spreadsheet. Now I need to merge those changes back to the released version that everyone has a link to.
I thought I could just delete a sheet from released spreadsheet, and use
"Copy to.." to copy a sheet from version2 to the released spreadsheet. This works... but the copied sheet is always
Copy of Sheet_Name (even when it's a brand new sheet that never existed in released version).
OK, I can rename
Copy of Sheet_Name to
Sheet_Name, however all links that were pointing to
Sheet_Name before are no longer valid, even though newly renamed
Sheet_Name exists. Refreshing or even opening the formula and pressing enter does not solve the issue. The only way to solve the issue is to change the formula to some other sheet name, and then fix it back to correct
Sheet_Name. Obviously I cannot do this for the whole spreadsheet.
My last attempt was to use
Copy To... from version2 spreadsheet to create
Copy of Sheet_Name alongside existing
Sheet_Name in released spreadsheet, then copy contents of
Copy of Sheet_Name to
Sheet_Name (otherwise there is a 1000 cells copy limit), and then delete
Copy of Sheet_Name.
As inconvenient as that was... it worked. Except that all my
Named and Protected Ranges information (which was heavy modified in version2) did not copy over.
So, first and foremost: can I copy
Named and Protected Ranges information from one spreadsheet to another?
Second, is there a better way of doing all this? I cannot ask the community to keep changing their bookmark. I don't want to use any "tinyurl" redirects either, as people are more likely to bookmark the actual address than tinyurl, so I will be back to square one were the community has outdated bookmarks.