Daily
Refresh a workbook in OneDrive every day
A workbook stored in OneDrive gets fresh values daily and you get the new total.
- Runs
- Daily
- Apps
- OneDrive
- AI
- Not needed
- Access
- read+write
Send this to your assistant
Create a ClickRuntime automation: every day at 6pm, update the Price column in my OneDrive Portfolio.xlsx with today's closing prices and email me the portfolio total.
First time? Adding ClickRuntime to your assistant takes about two minutes.
Why use it
Why teams use this automation
A small business owner tracks gold prices, exchange rates or competitor listings in an Excel workbook stored in OneDrive, then builds quotes and forecasts on top of it. Each morning someone has to look up the latest numbers, paste them into the right table and remember which cells feed which formulas. On the day it is skipped, the quote goes out with stale inputs and nobody notices until the margin looks wrong.
Each day, the rows of a named table in the workbook are read through the Excel API for OneDrive. Fresh values are fetched from a public data source, the table rows are updated in place so your formulas and charts keep working, and an email gives you the new total for the figure you care about.
It is useful for pricing sheets, currency conversion tables, inventory valuations and personal finance trackers. Anyone who currently pastes numbers from a website into a workbook every morning can stop.
What you get out of it
Formulas always run on fresh inputs
A workbook that updates itself keeps every quote and forecast built on yesterday's data at worst, instead of last week's. That matters most for quotes and forecasts, where one outdated number can quietly carry through a whole sheet of results.
The daily paste job disappears
Looking up values and pasting them in is a five-minute task that is easy to forget. Doing it on a schedule makes the workbook reliable on busy days and holidays.
Charts and pivots stay connected
Because existing table rows are updated instead of new files being created, the charts and formulas pointing at that table continue to work without repairs. You keep all the formatting, validation and links to other sheets you spent time building, and you avoid rebuilding anything after each refresh.
The email gives an early reading
The total in your inbox tells you the new figure before you open the file, which is enough for a quick check on whether anything moved significantly. It works as a daily sanity check, and a large jump can prompt a closer look before you act on the new data.
A shared file with one source of truth
Colleagues who open the workbook all see the same data, so there is less debate about whose numbers are current. It also cuts down on the situation where one person holds the only up-to-date copy on a local drive.
How it works
What happens, step by step
Every day, ClickRuntime reads the rows of the table in your Excel workbook in OneDrive.
It fetches today's values for each row from a public source, such as exchange rates.
It writes the new values back into the workbook.
You get an email saying how many rows were updated and the new total.
Before it runs live
- Connect OneDrive
What you get
Build your own
Need it to work differently?
Your own rules
Conditions, thresholds, filters and exceptions, written the way you'd explain them to a colleague.
Any number of apps
One automation can read from one app and update several others. Over 248 apps are ready, and anything with an API works too.
AI where it helps
Summaries, classification, extraction and drafts use ClickRuntime's built-in AI, with no extra key.
On a schedule or on each event
Runs as often as every 5 minutes, or the moment an app sends an event, and keeps track of what it already handled.
Changes you can ask for
- Run it at a different time, more or less often, or only on weekdays
- Add an AI step, for example to summarize, sort or draft a reply
- Add another app, for example also send it to Slack
- Combine it with other OneDrive steps into one automation
Under the hood
The code that runs this automation
from clickruntime import on_trigger, credentials, http, notify
BOOK = "https://graph.microsoft.com/v1.0/me/drive/items/01ABCDEF.../workbook/tables/Rates"
@on_trigger
def run(event):
h = {"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"}
rates = http.get("https://open.er-api.com/v6/latest/USD").json()["rates"]
rows = http.get(f"{BOOK}/rows", headers=h).json()["value"]
total = 0
for i, r in enumerate(rows):
currency, amount = r["values"][0][0], float(r["values"][0][1])
usd = amount / rates[currency]
total += usd
http.patch(f"{BOOK}/rows/itemAt(index={i})", headers=h, json={"values": [[currency, amount, rates[currency], round(usd, 2)]]})
notify.email_me("Workbook refreshed", f"{len(rows)} rows updated. Total in USD: ${total:,.2f}")
return {"rows": len(rows), "total": round(total, 2)}FAQ
Common questions
Does the workbook have to be in a table?
Will it work if someone has the file open?
Which data sources can supply the values?
What permissions does it need?
Does it need AI?
How many runs does it use?
Can I change how it works?
Related
You might also like
Archive old files from a temp folder weekly
Files that have sat untouched for months move to Archive, and you get a list of what moved.
See how it worksBack up an export to OneDrive every night
A dated copy of your data export is saved nightly, older copies are pruned, and you hear if it fails.
See how it worksGet a weekly OneDrive storage report
Each Monday you see how much storage you're using and which files changed last week.
See how it worksKeep exploring
Set up “Refresh a workbook in OneDrive every day” from your assistant
Copy the prompt above, or connect your assistant first. Free plan, no credit card.