Data that arrives clean
Codes and long numbers land exactly as the export wrote them, and every date in one style.
Spreadsheet automation
Keep the spreadsheet. Lose the hour it eats every week.
Every week someone downloads the export from the sales system, deletes the title rows, fixes the dates, pastes it in, drags the formulas down and emails the report. We leave the spreadsheet as it is and make that part run by itself, with the numbers checked before anything is sent.
Where the hour starts to go wrong
Excel rewrote gene names in 3 of every 10 papers that used it.
Excel reads anything that looks like a date as a date. A scan of genetics papers from 2014 to 2020 found that 30.9% of those with gene lists in Excel had names changed, the way SEPT2 becomes 2-Sep. In 2020 the committee that names human genes reported it had renamed every symbol Excel turned into a date.
Your export has the same weak spots. Microsoft lists four changes Excel makes on its own when a file is opened or pasted, so 00123 becomes 123 and JAN1 becomes January 1. It also says they don’t directly affect data brought in through Power Query, Excel’s own import tool. That is step two below.
19.6%of papers with Excel gene lists had gene name errors, 2005 to 2015
30.9%in the second scan: 3,436 of 11,117 papers, 2014 to 2020
15digits is where Excel cuts off a long number
4kinds of change Excel makes to your data unasked
Much of this is already inside Excel and Sheets.
We start with what you already pay for. Microsoft’s own documentation describes this chore: a file from an accounting site, opened every morning and tidied by hand. Power Query, Excel’s built-in import tool, records each cleaning step and runs them again on refresh. Office Scripts are steps Excel records and replays, and Power Automate, Microsoft’s scheduler, can run them at a set time. Apps Script does the same in Google Sheets.
Where those cover the job, we set them up and add nothing. Each also has limits its maker publishes, shown here, and Office Scripts in Power Automate need a business Microsoft 365 license. Where a limit gets in the way, a small program of ours takes over that part.
If the sheet has outgrown being a sheet, with several people editing copies of their own, we’d rebuild it as an app instead ›
What you’d get
The same spreadsheet, with the chores done for it.
A workbook that fills itself
This week’s rows are added under last week’s, with the formulas carried down, before you open the file.
Formulas someone has checked
We read the formulas behind the report, fix the ones that miss rows, and add a total the run compares with the export each week.
The report, sent on time
The summary is rebuilt from the new rows and emailed as a PDF to your list, at the hour you choose.
A message when something’s off
If the export is missing a column or the totals don’t match, the run stops before the workbook changes and emails a named person why.
A log of every run
Each run records when it ran, how many rows came in and whether the totals matched, so a question about last month has an answer.
The part nobody sees
The report in your inbox is the visible part. What lets you stop thinking about it sits underneath. In Google Sheets, for one, a timed script runs under the account of the person who set it up, so it needs an owner who won’t change jobs. See what it takes ›
- Somewhere to run when every laptop is shut
- A sign-in of its own, not one employee’s
- Every export kept exactly as it arrived
- A rerun that can’t add the same week twice
How a project goes
- 1
Talk
A free call. You describe the problem in your own words, and we tell you honestly whether software is the answer.
- 2
Proposal
A written scope, timeline and price before any work starts.
- 3
Build
We design, code and test in phases. You see working software at the end of each one.
- 4
Launch
We put it live: app stores, hosting, domains, the lot.
- 5
Keep it running
Monthly maintenance if you want it: updates, fixes and regular reviews.
Before you ask
Can’t Excel or Google Sheets do this on their own?
Often they can, and we start there. Power Query, Excel’s built-in import tool, records each cleaning step and runs them again on refresh. Office Scripts with Power Automate, and Apps Script in Google Sheets, run steps on a schedule. Their makers also publish the limits: a script run from Power Automate times out at 120 seconds and can’t refresh most data, and one Apps Script run ends at 6 minutes. We add a program of our own only where those limits get in the way.
Will we have to change how our spreadsheet is laid out?
Very little, as a rule. The new rows need one tab to land in, and your summary tabs, charts and formulas go on reading from it as they do now. If a formula behind the report is wrong or misses rows, we show you before we change it.
Does this work with Google Sheets too?
Yes. In Sheets the scheduler is Apps Script, which Google runs for you. Google’s published limits are 6 minutes for one run, and 90 minutes of timed runs a day on a free account or 6 hours on Google Workspace. Google also picks the exact minute inside the hour you choose, so we set the run an hour ahead of when the report is due.
We already have a macro that does part of this. Can you work with it?
Yes, and we read it first, because a macro that works is worth keeping. Two things Microsoft documents are worth knowing. Excel in a web browser can’t run VBA macros, the kind most older workbooks use. And Excel on Windows blocks macros by default in files that came from the internet, such as email attachments. If either one is getting in your way, we move the same steps somewhere that doesn’t depend on the macro.
What happens when the export changes, or a run fails?
The run checks the file before it touches the workbook: that the columns it expects are there, and that the totals match. If a check fails it stops, leaves the workbook as it was, and emails the person you named with the reason. Once the cause is fixed, the same week can be run again without adding its rows twice.