Automation Part 2: Automate spreadsheets with REFRESH(), REPEAT(), and SCHEDULE()

This is part two of our tutorial series on automating actions in dashdash.

Overview

In our first article, we described how to automate the extraction of data from JSON responses. But, dashdash has a couple more tricks up its sleeve - you can automatically execute functions exactly when you want using dashdash’s REFRESH(), REPEAT(), and SCHEDULE() automation functions.

What’s the difference between the three? Well, it all depends on what you want:

Do you want to… Then use…
Refresh data in a cell or range periodically. For example, every minute of every day. REFRESH()
Trigger formulas or a cell periodically. For example, every hour of every day. REPEAT()
Trigger the evaluation of a cell on specific days of the year and at specific times. For example, every Monday of June at 09:00. SCHEDULE()

:memo: Note: You can have a maximum of five automation functions in one view.

REFRESH()

REFRESH() lets you refresh the data in a cell or range periodically. This is particularly handy if you have a range of cells where you’d like to trigger an update or a formula execution.

When the formula is entered correctly, the cell will display REFRESH along with the refresh Hourglass icon. When you hover over the icon, it’ll display the next time the automation function will execute.

Syntax

REFRESH(range, [interval], [unit], [delay], [unit])

Parameter Mandatory Description
range Yes The cell or range that you want to refresh periodically. For example: A3 or A4:B5.
interval No The interval between cell or range refreshes. By default this is 60 (min). The minimum is 1 (min).
unit No The unit of time to use with the interval. Choose between:
- “min” for minutes (default)
- “h” for hours
- “d” for days
- “w” for weeks
- “m” for months
delay No The time until the first refresh. By default this is 1 (min). The minimum is 0 (min).
interval No The unit of time to use with the delay. Choose between:
- “min” for minutes (default)
- “h” for hours
- “d” for days
- “w” for weeks
- “m” for months

Examples

Refresh a single cell, A1, every 60 minutes with a 1 minute delay (the default interval and delay):

=REFRESH(A1)

Refresh example 1 screenshot

refresh-example-one

Refresh a single cell, A1, every 3 minutes with a 0 minute delay:

=REFRESH(A1, 3, "min", 0, "min")

Refresh example 2 screenshot

refresh-example-two

Refresh a range of cells, A1:A3, every 1 minute with the default delay:

Refresh example 3 screenshot

REPEAT()

REPEAT() lets you repeat the execution of a function (or a cell that contains a function) periodically. This automation function is great when you want to include the function within the actual formula, and not just refer to a cell.

When the formula is entered correctly, the cell will display the function it is repeating along with the repeat Repeat icon. When you hover over the icon, it’ll display the next time the automation function will execute.

Syntax

REPEAT(formula, [interval], [unit], [delay], [unit])

Parameter Mandatory Description
formula Yes The formula or cell you want to refresh periodically. For example: NOW() or A4.
interval No The interval between cell or range refreshes. By default this is 60 (min). The minimum is 1 (min).
unit No The unit of time to use with the interval. Choose between:
- “min” for minutes (default)
- “h” for hours
- “d” for days
- “w” for weeks
- “m” for months
delay No The time until the first refresh. By default this is 1 (min). The minimum is 0 (min).
interval No The unit of time to use with the delay. Choose between:
- “min” for minutes (default)
- “h” for hours
- “d” for days
- “w” for weeks
- “m” for months

Examples

Repeat the NOW() function every 60 minutes with a 1 minute delay (the default interval and delay):

=REPEAT(NOW())

Repeat example 1 screenshot

repeat-example-one

Alternatively, you can also refer to a cell that has the NOW() function:

Repeat the execution of a formula in a cell, A1, every 1 minute, with no delay:

=REPEAT(A1,1,"min",0)

Repeat example 2 screenshot

Repeat the NOW() function every 7 days with the default delay:

Repeat example 3 screenshot

image

SCHEDULE()

SCHEDULE() allows you to execute functions or cells at very specific times or intervals. This function is great when you require to execute a function at a specific time or day, such as every Monday of June at 11 am.

When the formula is entered correctly, the cell will display SCHEDULE along with the repeat Repeat icon. When you hover over the icon, it’ll display the next time the automation function is scheduled to execute.

Syntax

SCHEDULE(task, schedule_message, [time_zone])

Parameter Mandatory Description
task Yes The formula or cell to execute. For example NOW(), A1, or B2:B7.
schedule Yes When and how often to execute the task. We use a special syntax for scheduling, which you can read about in depth in our SCHEDULE() article.
time_zone No The UTC or GMT timezone in HH:MM to use when evaluating schedule_message. For example: Indian Standard Time (IST) = "UTC+05:30". By default, this is set to UTC.

Examples

To schedule an update of a single cell, A1, every Monday, Wednesday, and Fridayat 07:00:

=SCHEDULE(A1, "every monday,wednesday,friday 07:00")

Schedule example 1 screenshot

schedule-example-one

To schedule an update of a range of cells, A1:B2, every Monday, Wednesday, and Fridayat 07:00:

Schedule example 2 screenshot

schedule-example-two

To schedule an execution of a cell, A1 every 2 hours between 07:00 and '18:00`:

=SCHEDULE(A1,"every 2 hours from 07:00 to 18:00")

Schedule example 3 screenshot

Challenge

Now that you’ve got the basics of automation functions to schedule the execution of formulas, cells, and ranges, let’s see what you can do.

  1. Use each of the automation functions to execute the NOW() function, which is in a different view.
  2. Using OVERWRITE_DATA() and the function below, schedule the table to be overwritten on Tuesdays, Thursdays, and Saturdays at 9 am.
    =PARSE(SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">500","Germany","Internet",">2019"),"organizations")

Summary

And that’s it! Now you know how to how to use REFRESH(), REPEAT(), and SCHEDULE() to automate the execution of formulas, cells, and ranges! What’s next? Well, have a look at the spreadsheets you have already - maybe there’s an automated email you’d like to send to yourself with the newest stats, or perhaps you have a table you’d like updated every Monday.

In part three of the series, we’ll cover the INSERT(), OVERWRITE, and UPDATE() functions to show you how you can create forms and have users’ answers injected into a database. We can’t wait to show you how :wink:.

3 Likes