I have an inventory spreadsheet with a function that copies and renames an Order Template to create a new Order sheet when you click a button. The function then renames the sheet to the name for the order. Additionally, I have another function, updateSheetList()
, that copies all the Order sheet names and populates a column which is used with data validation to create drop down menu within a cell on the sheet.
Currently, I have a button on the sheet to update this list for the drop down menu. I would like to automate this so that when a new order sheet is either added through the copy/rename function or deleted manually, it runs the updateSheetList()
function automatically.
The current code that I have to do this is below:
function onChange(e){
if (e.changeType == 'INSERT_GRID' || e.changeType == 'REMOVE_GRID'){
updateSheetList();
}
The trigger works when a sheet is deleted but does not work when I use the copy/rename function. I have realized that the INSERT_GRID
changeType only works when a sheet is manually created and not when it is duplicated.
So is there a way to either create a custom changeType so that the onChange()
function triggers when a sheet is duplicated? Is there an alternative workaround to get this to work without using the onChange()
trigger?