HOW TO SCHEDULE FLOOKUP FUNCTIONS
Introduction to Scheduling Functions
In this tutorial, we shall set up Flookup functions to run automatically at regular intervals. This handy feature uses the trigger functionality in App Script. You can schedule functions to run from once every hour to once every seven days, depending on the mode you choose.
Before scheduling any function, it is important that you first manually use that very function to clean a sample of that dataset in order to determine what parameters work best for you.
While setting the parameters of each available function, there will be visual cues that will guide you as to what feature is available for that function and what feature is not.
Steps to Schedule Lookup and Merge Functions
Open the sidebar
Go to Extensions > Flookup Data Wrangler > Schedule a Function > Lookup and Merge in your spreadsheet menu.
Select the function to schedule
Choose the function to automate. It can either be FLOOKUP to match by percentage similarity or SOUNDMATCH to match by sound similarity.
Choose the mode to run
Process Range_one to the end: Processes the table of lookup values until the end and then stops.
Process Range_one in a loop: Continuously processes the same lookup range on schedule.
Set the lookup values
Highlight the column with lookup values and click "Grab selected range" to read them into Range_one. If Range_one contains more than one column, only the first column will be processed.
Set the comparison values
Highlight the columns with values to compare to Range_one and click "Grab selected range" to read them into Range_two.
Specify the columns to analyse
Lookup_column: Specify the "Range_two" column to compare to "Range_one".
Return_column: Specify the "Range_two" column to return values from.
Set the similarity level
Set the desired level of similarity using the Threshold value. Skip this step if the field is inactive.
Set the schedule
Time of day: Set the time for the function to run. Leave this field unchanged if not needed.
Frequency: Determine how often the function runs:
If you set the "Time of day", then this parameter will establish how many days will pass between function executions.
If you do not set the "Time of day", this parameter will establish how hours will pass between function executions.
Lock the output position
Click any empty cell to lock the position you would like the result to populate from every time the function runs.
Schedule the function
Click the "Schedule" button to create a trigger that will run the function automatically.
Steps to Schedule Duplicate Removal Functions
Open the sidebar
Go to Extensions > Flookup Data Wrangler > Schedule a Function > Extract Unique Values in your spreadsheet menu.
Select the function to schedule
Choose the function to automate. It can either be EXTRACT BY PERCENTAGE or EXTRACT BY SOUND.
Choose the mode to run
Process Range_one to the end: Processes the table of lookup values until the end and then stops.
Process Range_one in a loop: Continuously processes the same lookup range on schedule.
Specify lookup values
Highlight the column with lookup values and click "Grab selected range" to read them into Range_one.
Specify the column to analyse
Lookup_column: Specify the column to analyse for duplicates.
Set the similarity level
Set the desired level of similarity using the Threshold value. Skip this step if the field is inactive.
Set the schedule
Time of day: Set the time for the function to run. Leave this field unchanged if not needed.
Frequency: Determine how often the function runs:
If you set the "Time of day", then this parameter will establish how many days will pass between function executions.
If you do not set the "Time of day", this parameter will establish how hours will pass between function executions.
Lock the output position
Click any empty cell to lock the position you would like the result to populate from every time the function runs.
Schedule the function
Click the "Schedule" button to create a trigger that will run the function automatically.
-----
Notes About Scheduling Functions
In order to stop or cancel the function schedule, simply select your function of focus and click the "Reset" button.
If you set the schedule to run hourly, you will exhaust your 90-minutes-per-day quota set by Google for consumer accounts. If you are using a Google Workspace account, then this quota extends to 6 hours a day.
Common Issues You Might Encounter
Triggers fail to run: Triggers have an execution limit of six minutes every hour. This limit is shared across all your existing triggers. If multiple triggers refresh simultaneously, some of them may be skipped or delayed to the next iteration. If this happens repeatedly, your triggers may not run at all.
Excessive Google triggers: There is an undocumented limit of 300 triggers per user for each project. Therefore, if you encounter issues with triggers not running, consider reducing the number of triggers in your project as a troubleshooting step.
Inaccurate function parameters: The scheduled function might fail to complete correctly if you used the wrong parameters while scheduling it.
Failed authorisation: Triggers can execute under the authority of any user who has editing rights to the spreadsheet or any suer who has opened the function sidebar in the spreadsheet at least once. If a trigger does not run, it might be as a result of the user not meeting these conditions.
Processed data appears in the wrong sheet: We currently offer four functions that you can schedule. To keep track of these functions effectively, we suggest that each user schedules one function per spreadsheet. In other words, if you schedule a function in one sheet, you should refrain from scheduling the same function under the same account in another sheet, unless you do not mind resetting the scheduler's target spreadsheet. We have adopted this approach to ensure that you do not face any quota limit errors.