Copy of a scripted spreadsheet doesn't have the script.

[MatnZero]

Hello!

Lately in my workspace people are having problems making copy of a main template scripted spreadsheet, sometimes the copy doesn't have the associated AppScript, or it doesnt' have the correct Services. Instead, a new project is created with blank script.

They make hundreds of copies everyday and it looks like occurs every 20-30 copies. I tried to replicate the error but it happens randomly and with no apparent reason when creating a new copy.

We aways had this main spreadsheet just doing little ajustments based on our client necessities, but this issue just started at the end of last year, and everyday now some of them doesn't have the bounded script.

Does someone know what could be happening?

[JasonTM1]

Maybe the event logs could give a clue as to whats going wrong?

[MatnZero]

Hello Jason, thank you for your response. We tried checking the event logs, but we can just see actions involving the spreadsheet itself, not from the script (create, edit, copy, and so on):

The only log that appears for us its when someone creates a new appscript project on a spreadsheet that initially doesn't had any project bounded:

I tried digging on Apps Script documentation and it's says that every app script project uses google cloud in background, and since  we're not a customer of Google Cloud, we cannot talk with their support or see the logs of the projects. 

[JasonTM1]

I have limited Apps Script experience, but one small script I use a lot to convert batches of docx to Gdoc files will output events to the console log. I wonder if you could have the script output to a log file or otherwise view the console log?

[JasonTM1]

Reading over your initial post again it appears that I may not understand the issue completely. Is it the script that does the copies or that the script is part of the sheet which you are duplicating but sometimes the script piece is not included in the copy?

[MatnZero]

Basically my co-workers manually makes a copy of a "template" spreadsheet that has a script that helps them in their job. Normally the copy works fine, but sometimes the copy doesn't has the script, and they get a error when try to activate the script using a button inside the spreadsheet. When I try to see the Appscript project in that copy, instead a new blank project is created, like there was no script at all (The name of a default project is "Projeto sem título" in my language, like the highlighted row in the second print).

[JasonTM1]

Gotcha! I will play around with one of ours to see if I notice any other logging available anywhere.

[MatnZero]

Thank you very much for your time, Jason!

[JasonTM1]

While not ideal, have you tried using the URL for your file with a /copy# at the end instead of the usual 'edit'? This should create and then open a copy of your original "template" file. It may have the same issue if the problem is a bug in Google's code for creating copies, just a guess.

[JasonTM1]

I just created a test Sheet which included an a Script. Using the URL/copy# method I made a dozen copies of the Sheet. Each time I was asked if I wanted to include the script as well which I approved. In every case the script was included with the new Sheet.

I know this test is not extensive, and using the URL may not be practical for you, but it did work for me.

[JasonTM1]

I was thinking about an easy way for your users to save this URL/copy# string if it turns out to work. Bookmarks might be a simple solution. Just have your users open the original 'template' Sheet, bookmark the location(URL), then edit the bookmark so it contains the /copy# portion. Going forward they will just open a new tab/window and click the bookmark to generate a copy of the file, hopefully with the script as well.

[MatnZero]

That is a great insight, I never thought about using the link to create a copy of a sheet.
I did some tests here but the copies end up in the "My Drive" of the person who created the copy, what could not be the best since every copy needs to be in the same folder of the template sheet.

For now the work around that we found was creating a huge button inside the template file, and added a function to delete the button after clicking on it, so if the file has no app script projet the button cannot be removed, and the user delete the "failed" copy and create another.

Thank you very much for your suggestions and since we didn't find yet the real reason for the copies not having the script, maybe we will still be looking for some alternatives!

[JasonTM1]

That is a very clever test for the existence of your script. Well done.

As for the /copy# method of creating a new file, yes it does create the new file in the users' My Drive. In case it helps you, or others reading this in the future, I think you could add a moveTo(destination) method to the script in order to relocate the file. Documentation here.

I had not used the URL/copy# technique in a long time but may try it again in my environment as a replacement to creating New-from-Template documents. It can be a very quick way to create a standard document assuming they can be initially generated in your My Drive as you correctly pointed out.

I found a number of other posts around the web of people reporting duplicated documents randomly dropping the included scripts. Hopefully Google can identify the issue and correct it.