Prevent manual copy of spreadsheet Google script

Question

I have a Google sheet that is created monthly, so have writtena script that will make a copy of the current month's sheet in a specific folder and then populate cells in the new spreadsheet with data from the old. It works well.

I am trying to come up with a solution to warn users if they try to manually copy the spreadsheet using the 'make a copy' menu item to instead use the custom menu item provided.

I thought about setting a flag in a cell, but of course that only works for the first time, as a manual copy also copies the flag. If I could detect when the file is copied manually, I could reset the flag and then test for it onOpen.

Can someone please point me in the right direction. Unfortunately the users need to be able to edit the document, so I don't think I can disable the option in the file menu.

Many thanks

Jack Brown Jack Brown · Accepted Answer · 2017-04-18T23:04:01

You can address this specific issue:

I thought about setting a flag in a cell, but of course that only works for the first time, as a manual copy also copies the flag.

By using PropertiesService, you can use document property to add a property to a specific document. This property can be accessed by the script, run by any user, on that specific document.

However, if you copy the document, the property is not copied. You can look for this property during onOpen and determine if the document was copied the way you want.

You can do something like this:

function setProp(){               //use this to set the property to the document
 var docProp =  PropertiesService.getDocumentProperties(); 
  docProp.setProperty("Copied", "false")              //Properties are stored as text immaterially of the type of value you pass to it!
}

function getProp(){               //use this to check if the property of the document was set, if set will return true, else false
  var docProp =  PropertiesService.getDocumentProperties(); 
  Logger.log(docProp.getProperty("Copied"))
  return docProp.getProperty("Copied") == "false"

}

function onOpen(e){                      
 var ui = SpreadsheetApp.getUi()
 if(getProp()){                        // Check to see if the property was set for the sheet. 
  ui.alert("Copied correctly")
 } else {                              // This will trigger each time they open the sheet,
   ui.alert("Copied Incorrectly, please use custom function to make a copy")
 }
}

Hope that helps!

Prevent manual copy of spreadsheet Google script

1 Answers