How can I activate a app script function in a sheet based on the input from other spreadsheet?

Question

I have below spreadsheets
Master spreadsheet
child spreadsheets( close to 40 )

I'm trying to create a function which can fetch the data from an of the child sheet and upload it as table in BigQuery.

So, I'm trying to write the main function in the master sheet and collected all the child sheet id's in the master sheet.

In all the child sheets I created a menu that will show option as "upload". Whenever an user clicks the upload button it will send the respective sheet id to the master sheet and dump it in one of the cell. So, based on the sheet the url will be created in the master sheet function and then it should be running.

Now the problem is I'm able to fetch the sheet id of the child sheet and pasted in the master sheet but unable to active the function in the master sheet.

In order to active the function I was using the onEdit() as the function name.

Is there anyway I can activate the master sheet function when a key of the child sheet is pasted in the master sheet.

Note: I wanted only single function for all the sheets. because every time any change is required in the code I need not make the changes in all the sheets(close to 40)

Answer 1

Answer:

onEdit() triggers do not get activated unless a user manually makes an edit to a sheet. You can not have a script automatically run as a result of a different script editing the master sheet.

Workaround:

You can however, make a time-based trigger to run every minute, which will run on your master sheet. You can run this for as long as you are copying into the master sheet from child sheets, and then remove the trigger after you have finished.

You can set up an installable trigger from within the Apps Script user interface for the script that is bound to your Sheet by following the menu path Edit > Current project's triggers, and then clicking the + Add trigger button in the bottom-right of the newly opened page. A pop-up will appear, on which you can set up the trigger with the following settings:

• Choose which function to run: onEdit (or whatever your function is called)
• Choose which deployment should run: Head
• Select event source: Time-driven
• Select type of time based trigger: Minutes timer
• Select minute interval: Every minute

References:

• Simple Triggers
• Installable Triggers - Time-driven triggers