10

I don't like expanding formulas. First, when I modify them, I have to copy them again, it is like copy&paste programming. Second, the I'd like them to match the original range, which might change. Ideally, I'd like to define a formula and range it operates on (e.g., sheet1!B4:B) and it applies accordingly. And when I add one more row to sheet1 (which increases the open-ended range sheet1!B4:B), it automatically adds a new computed row.

I've found something that can do some of those tricks: ArrayFormula. It can simply copy some data from one sheet to another, using the open-end range. It seems it can perform some trivial operations like addition. But I am not sure how to perform some more complex operations, that might operate with ranges on their own, like sum. For example, I'd like to compute sum for sheet1:B4:B and autoexpand it (without manual expanding) to all the following columns, so the cell right to the first cell would contain sum(sheet1!C4:C), cell right to this one would contain sum(sheet1!D4:D) and so on. This doesn't look like I can just use ArrayFormula for this, but I might be wrong.

(Of course, sum is just an example, I'd like to be able to write some other formulas.)

I appreciate any idea how to do this.

Improve this question
4
  • 2
    use custom function, which gives .map()
    – TheMaster
    Mar 14, 2018 at 15:19
  • @I'-'I I've looked at the documentation etc. It seems that I have to pass all inputs as arguments (stackoverflow.com/a/9023954 ). So, I would need double-open-ended range (e.g., cell B4 and all following rows and all the following columns), but I am not sure if Sheets support it. I am also a bit concerned about offline usage – while this is not critical to be useable offline, it would be nice and it is critical not to provide outdated results. (Offline behavior: Correct results are ideal, no results are acceptable, bad results are unacceptable.)
    – v6ak
    Mar 16, 2018 at 16:20
  • @I'-'I I am sure it supports ranges as arguments, they are passed as 2D array, see developers.google.com/apps-script/guides/sheets/functions . However, I don't know if it supports ranges I need.
    – v6ak
    Mar 16, 2018 at 17:48
  • 1
    @I'-'I On offline: according to developers.google.com/apps-script/guides/sheets/… , it performs requests to the server. When offline (e.g., on Android), ot cannot do so. On ranges: I need open-end range like B4:∞∞, where ∞∞ is the rightmost and bottommost cell. I know how to write, say, B4:∞5 or B4:D∞, but not B4:∞∞.
    – v6ak
    Mar 16, 2018 at 17:53

1 Answer 1

Reset to default
9

Alternatives:

  • Use ArrayFormula / MMULT functions
  • Make a custom function
    • Limitations:
      • Only works when using the Google Sheets desktop web app on online mode.
      • Requires some sort of programming knowledge and skills (JavaScript/Google Apps Script)
      • 30 seconds execution time limit
  • Make a macro/script
    • Limitations:
      • Similar to custom functions but longer execution time
      • Require to be triggered by a custom menu, dialog, sidebar, simple or installable triggers, from the Apps Script Editor or by using the Apps Script API
  • Make an add-on for Android
    • Limitations:
      • Requires some sort of programming knowledge and skills (Java/JavaScript/Android Studio/Google Apps Script)
      • It's on Developer Preview
      • Doesn't work for the Google Sheets desktop web app
      • Doesn't work for the Google Sheets iOS app
Improve this answer
1
  • Thank you for the answer. All the options have some important limitations, but that seems to be the reality. It also seens that all of those slow it down by recomputing all the cells in the group on every change. The ArrayFormula seems to have also quite limited applicability, i.e., some formulas probably cannot be redesigned this way. All but the ArrayFormula seem to break refactoring (e.g., column/row insertion or sheet rename) and cannot be simply edited from phone… I will accept the answer, as it seems to comprehensively list all the options, although I'm not excited about any of them…
    – v6ak
    May 30, 2018 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.