HOW TO SCHEDULE DATA CLEANING 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 Match and Merge Functions

1. Open the function sidebar

   • Go to Extensions > Flookup Data Wrangler > Schedule functions > Fuzzy match data in your spreadsheet menu.

2. Select the lookup and merge function to schedule

   • Choose the function to automate. It can either be Fuzzy match by percentage to match by percentage similarity or Fuzzy match of by sound to match by sound similarity.

3. Choose the mode to run

   • Process Lookup_values to the end: Processes a specific range values until the end and then automatically stops.

   • Process Lookup_values in a loop: Continuously processes a specific range on a schedule, regardless of the values it contains.

4. Set the lookup values

   • Highlight the column with lookup values and click "Grab selected range" to read them into Lookup_values.

If Lookup_values contains more than one column, only the first column will be processed.

5. Set the values to be compared

   • Highlight the columns with values to compare to Lookup_values and click "Grab selected range" to read them into Table_array.

6. Specify the columns to analyse

   • Lookup_column: Specify the "Table_array" column to compare to "Lookup_values".

   • Return_column: Specify the "Table_array" column to return values from.

7. Set the similarity level

   • Set the desired level of similarity using the Threshold value. Skip this step if the field is inactive.

8. Choose whether you would like to run the function to run every n hours by clicking the "HOURLY" button or every n days by clicking the "DAILY" button.

9. Set the schedule

   • Time of day: If you chose "HOURLY" in the previous step, set the time for the function to run. Otherwise, skip this step.

   • Frequency: Determine how often the function runs:

     1. If you choose "DAILY", then this parameter will establish how many days will pass between function executions. For example, a value of 3 will cause the function to run once every three days at the time you set. You can set an interval of up to 30 days.

     2. If you choose "HOURLY", this parameter will establish how hours will pass between function executions. For example, a value of 5 will cause the function to run once every 5 hours. You can set an interval of up to 24 hours.

10. 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.

11. Schedule the function

    • Click the "Schedule" button to create a trigger that will run the function automatically and close the sidebar.

Steps to Schedule Duplicate Removal Functions

1. Open the sidebar

   • Go to Extensions > Flookup Data Wrangler > Schedule functions > Extract unique values in your spreadsheet menu.

2. Select the duplicate removal function to schedule

   • Choose the function to automate. It can either be Extract by percentage or Extract by sound.

3. 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.

4. Specify lookup values

   • Highlight the column with lookup values and click "Grab selected range" to read them into Range_one.

5. Specify the column to analyse

   • Lookup_column: Specify the column to analyse for duplicates.

6. Set the similarity level

   • Set the desired level of similarity using the Threshold value. Skip this step if the field is inactive.

7. Choose whether you would like to run the function to run every n hours by clicking the "HOURLY" button or every n days by clicking the "DAILY" button.

8. Set the schedule

   • Time of day: If you chose "HOURLY" in the previous step, set the time for the function to run. Otherwise, skip this step.

   • Frequency: Determine how often the function runs:

     1. If you choose "DAILY", then this parameter will establish how many days will pass between function executions. For example, a value of 1 will cause the function to run once every day at the time you set. You can set an interval of up to 30 days.

     2. If you choose "HOURLY", this parameter will establish how hours will pass between function executions. For example, a value of 2 will cause the function to run once every 2 hours. You can set an interval of up to 24 hours.

9. 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.

10. 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 might exhaust your 90-minute daily quota set by Google for consumer accounts. If you are using a Google Workspace account, then this quota extends to 6 hours a day.

• Be sure not to change the sheet name or modify any of the parameters you configured while scheduling your functions.

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 two 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.

Explore Further

• How to Automate Data Cleaning in Google Sheets with Macros

• Tips for Cleaning Data in Google Sheets