If you have a Google Sheet with some associated Apps Script code in your personal drive, then you copy it into a shared drive (e.g., your company share), it will strip the project properties, including script properties that you may have (e.g.) set to hold credentials.
This seems like a good plan, to prevent information leaking when scripts are copied around.
Unfortunately, it also seems to permanently remove the ability to add script properties ever again. If you open the Project properties and check the Script properties, the “Add row” link is simply missing.
If you create a new spreadsheet on the shared drive, it will work fine – so in some cases, it might be easy to just copy/paste the spreadsheet info and the app script into an entirely new document, created from scratch.
Unfortunately this didn’t work for my spreadsheet, which had a lot of names ranges, buttons, images, and drawings, which don’t lend themselves to copy/pasting easily.
Been battling this for a while and can’t find a simple solution, although other people seem to have the same problem. There’s also several bugs in Google’s Issue Tracker that seem to be related.
The easiest fix I’ve found is:
- Create a new copy of the spreadsheet in my own drive
- Delete the scripting from the spreadsheet
- Copy the spreadsheet over into the shared drive
- Re-add the scripting into the spreadsheet
- Add the project properties you need
This preserves all the spreadsheet bits that are frustrating to recreate manually and (for me at least) re-creating the script is a simple copy/paste.