Overview
I’ve had a bit of trouble with filling data and adding manual transactions taking an excruciatingly long time - sometimes upwards of 90 seconds. There are a lot of great suggestions on this community already for improving the performance of your spreadsheet, and I wanted to contribute by adding a technique I’ve started using that’s been enormously helpful.
It’s pretty simple, but I just added a plain dropdown of TRUE or FALSE, and then linked the calculations for my biggest reports to that value. If it’s TRUE, the report generates results, if it’s FALSE, they sit idly by.
For example:
With the Calendar disabled…
It’s empty of data:
When enabled:
It’s populated with results:
Implementing the kill switch
The hard part of implementing this is identifying the key place(s) to add the kill switch status checks. For the calendar pictured above, I use a temporary table of transaction data that is then summarized into the days of the calendar. The formula for that temporary table originally looked like this:
=FILTER(
<REST OF FORMULA DELETED FOR BREVITY>
)
After adding the kill switch check in cell X16, it looks like this:
=IF(X16,FILTER(
<REST OF FORMULA DELETED FOR BREVITY>
),IFERROR(1/0))
Now, the calendar no longer recalculates when the Transaction sheet is updated.
I say it’s tricky to identify the best places to put the checks because on one of my reports, I had to update several formulas to check the kill switch status, so it depends on how the individual report is written.
I should also note that you’re essentially breaking a report with this technique, and that can generate a lot of errors and sometimes unpredictable behavior. It can be alarming to see a sea of red triangles and #REF or #NA all over the screen, but if you turn the kill switch back on, they’ll all go away and the report will render as normal.
Using a script to turn off reports automatically
If you have several reports you want to turn off at once, or have them turned off by default when you open your sheet, the easiest option I found was using a script.
I didn’t have any experience with scripts before this, but it was surprisingly easy:
- Open the AppsScript page:
- Under Files, click the “+” and select Script:
- Give the file a name
- Paste this into the text window:
function onOpen() {
//this function will run when the spreadsheet opens
//this will create a new menu at the top of Google Sheets
var menu = SpreadsheetApp.getUi();
menu.createMenu('Report Formulas')
.addItem('Formulas ON', 'ReportFormulasOn')
.addItem('Formulas OFF', 'ReportFormulasOff')
.addToUi();
//this will call the function to turn off the reports automatically everytime you start your spreadsheet
//remove this line if you don't want that to happen
ReportFormulasOff();
}
function ReportFormulasOn() {
//add a variable for each sheet you want to set the kill switch for
var calendarSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Calendar');
var expenseAnalysisSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Expense Analysis');
var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
//This sets the actual cell with the kill switch value
calendarSheet.getRange('X16').setValue('TRUE');
expenseAnalysisSheet.getRange('R3').setValue('TRUE');
dashboardSheet.getRange('AC1').setValue('TRUE');
}
function ReportFormulasOff() {
//add a variable for each sheet you want to set the kill switch for
var calendarSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Calendar');
var expenseAnalysisSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Expense Analysis');
var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
//This sets the actual cell with the kill switch value
calendarSheet.getRange('X16').setValue('FALSE');
expenseAnalysisSheet.getRange('R3').setValue('FALSE');
dashboardSheet.getRange('AC1').setValue('FALSE');
}
- Click Save to Drive
Now, when you open your Tiller spreadsheet, a menu should appear to the right of “Help”, with two options. Click the Formulas OFF option, and the script will automatically turn off the reports you added to the script. It can take a few seconds to run, so don’t worry if it doesn’t happen instantly.
Clicking Formulas ON will of course turn them all on at the same time, but you can always toggle each report individually by changing the value for the kill switch on that sheet.
Notes
This site (How to Add a Script to Google Sheets (Easiest Way in 2024!)) was a huge help to me with writing the scripts.
