Using python with custom spreadsheets; is it best practice to do the maths in python or with spreadsheet formulae

Question

My question is whether there is a best-practice rule on what to do preferentially when you need to do mathematical operations on numbers stored in a custom spreadsheets via a Python app.

Say you're creating a stock control app in python using a custom google spreadsheet for data storage, for example, is there a hard-and-fast preference on whether to add up totals using a google formula or programmatically in Python.

Is there any technical reason you can think of (apart from maximising convenience and minimising complexity) to prefer one approach over the other?

Answer 1

Please see several considerations on Google Sheets functions vs. Python. The bottomline is that there is now hard rule and the answer depends on your use case. Probably you will have to experiment to see which one works best.

1. Ease of development: Google Sheets functions do not require any and are easy to adjust. While Python code will have to be supported.

2. Data freshness in the spreadsheet: Google Sheets functions update on their own. You will have to re-run Python functions to get updated values.

3. Volume of data: Bulk Google Sheets functions are generally relatively fast, for example, when calculating one value from large amounts of data. Python should also be fast enough (sometimes faster), but you must load all this data into the Python script first, which can sometimes be slow.

4. Number of distinct calculations: Having a lot of Google Sheets functions, especially if they are chained and crunch massive amounts of data, can be very slow (in my experience - minutes and more while the spreadsheet is frozen). In these cases, calculating some steps or all of them in Python is orders of magnitude faster.

5. Overall spreadsheet setup: Mixing formulas and Python (or Google Apps Script) calculations in one chain can be challenging. There can be cases when Python tries to access a spreadsheet when it is partially calculated, and either gets an imprecise result or takes too long waiting. If you use Python to write data to a spreadsheet with many formulas (see #4), the operations can take a long time or even time out completely.

---

Personally, I usually try to do as much as I can with pure Google Sheets functions, and if it is not enough, introduce Python or Google Apps Script for some of the most crucial steps or all of it.

Answer 2

Python is a powerful programming language for data manipulation and analysis, which allows you to provide more flexibility and control. This can be an advantage if you have large datasets where you need to apply complex calculations.

Spreadsheet formula for simple logical and statistical calculations, since it is easier to use and apply if you already have the data to be calculated on your spreadsheet. Also if there is no need for really complex calculations.

---

In your situation since you already have the data to compute on your spreadsheet, I think you are better off using the Spreadsheet formula. The only challenge I'm seeing is if the format of your spreadsheet or the size of your data keeps changing, you will need to adjust it every now and then.