Ah, the ARRAYFORMULA formulas in Google Sheets! These little wonders can solve complex problems in the blink of an eye. But how can you use them effectively to gather all the data you need? Don’t worry, I’m here to explain it all in detail.
How Many Changes Can You Make in a Day?
Answering your question is not easy. On average, you can make anywhere from 1 to 1000 additions, modifications, or deletions per day. But let me give you more details to help you better understand.
These formulas are used in a complex system composed of a tracking number generator, a calendar, and a timesheet (among other applications). Specifically, these formulas are used in the table-based calendar system (not Google Calendar). Generally, there are about 10 to 50 additions/modifications/deletions per day in the calendar.
A Glimpse of the System
Let me give you a brief description of the system:
Tracking Numbers (New Records per Day: 1 to 200): This is the heart of the system, everything relies on it.
- You enter task data, and it generates a sequential number.
- You can group multiple tracking numbers under a parent tracking number (which is important for later).
- Associated/supporting tables are used for clients, assets, client equipment, correction logs, technician logs (equipment errors related to tracking tasks), estimation logs, etc.
- Existing estimation and correction keys can be transformed into new tracking numbers. For example, 20 correction items can be grouped under a single tracking number, which will then be estimated and sent. Then, clients can approve different combinations of items. Each approved key becomes a new tracking number. So, there will be 17 new tracking numbers and one parent number (used to issue a global invoice). The 18 numbers are interconnected in the system (which is important for later).
Timesheet (New Records per Day: 10 to 100): Allows technicians to fill out a timesheet for the tracking numbers assigned to them. Some information determines if the task has been completed.
Calendar (New Records per Day: 10 to 50):
- Based on tables, completely independent of Google Calendar.
- Composed of a management and scheduling application, as well as a read-only visualization application for technicians. This separation ensures optimal performance.
- Allows management to view tracking numbers and all related data (client, corrections, estimations) and create events in the calendar by adding relevant data (notes, asset data, correction or estimation repair information, parts purchases, etc.).
- In the case of individual tracking projects taking place within a single day, when a task is completed, there is no risk of duplicate work.
- On the other hand, for parent tracking numbers, which typically involve multiple technicians over multiple days, we use the parent number to create a new event in the calendar. We hide the parent number, and on-site technicians check the notes section to know the tasks to be done and the associated tracking number. Technicians mark the task as completed in the timesheet application. The calendar notes, which indicate the list of 17 tracking numbers, are automatically updated, and the data for the completed task is removed from this list (the calendar notes). Keep in mind that a project with a parent number consisting of 17 parts may require tracking 15 daily entries or more (a total of 255 notes).
The system is quite complex, with 12 applications working together. The technicians’ calendar application only accesses the calendar table in a read-only manner for optimal performance. Therefore, data cannot be referenced through virtual columns in the technicians’ application. They can be referenced in the management application but then need to be saved in a physical column to appear in the technicians’ application. As soon as you save data in AppSheet, you must update each individual instance for the virtual data changes to be correctly saved and the technicians’ calendar to display correctly. I know there are ways to do this, but they all require someone to do something in the application. The ARRAYFORMULA formula on the Google Sheets side takes care of everything without needing to update anything. So, you can be sure that the calendar data is always up to date for all technicians.
I use many ARRAYFORMULA formulas on the Google Sheets side in all applications to facilitate automatic updates, such as updating the calendar notes. For example, the correction table contains a formula that checks estimations and timesheets to see if estimations have been created or if corrections have been made. I also use an ARRAYFORMULA formula to create sequential tracking numbers (without issues) because the keys confuse our technicians. The keys are still there, but technicians no longer use them for communication. It’s simpler to say that you’re working on task 3520 and look for that number throughout the system or memorize it to enter it in your timesheet, rather than using the number 6029e56b.
I hope I’ve provided enough details without confusing you. In any case, if you have additional questions, don’t hesitate to reach out. And don’t forget to visit Crawlan.com for more Google Sheets tips and tricks!
