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 function sidebar
Go to Extensions > Flookup Data Wrangler > Schedule a Function > Lookup and Merge in your spreadsheet menu.
Select the lookup and merge 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 a specific range values until the end and then automatically stops.
Process Range_one in a loop: Continuously processes a specific range on a schedule, regardless of the values it contains.
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 values to be compared
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 Highlight Functions
Open the sidebar
Go to Extensions > Flookup Data Wrangler > Schedule a Function > Highlight Duplicate Values in your spreadsheet menu.
Select the highlight function to schedule
Choose the function to automate. It can either be HIGHLIGHT BY PERCENTAGE or HIGHLIGHT BY SOUND.
Choose the number of columns to compare for highlighting
Compare data in a single column: Compares rows of data inside a single column.
Compare two different columns: Compares data across two different columns.
Choose the mode to run
Process data to the end: Processes the table of values until the end and then stops.
Process data in a loop: Continuously processes the same range on schedule.
Specify values to process for highlighting
Select columns with target values and click "Grab selected range". If you select a single column, only Left_column will be active. If you select more than one column because you intend to process more than one column, then Left_column and Right_column will be active.
Specify the column to analyse
Use the Left_column and Right_column input fields to specify the index of the columns you would like to be analysed
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 duplicate removal 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 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.
After scheduling the function, be sure not to change the sheet name or modify the region where you expect the result to be populated.
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.