2

I have a spreadsheet in which I want to be able to expand the number of columns using a function rather than using auto-fill because the new cells are getting filled with content based on complex formulas and depending on a lot of things. In one of these cells I want the content to be something like =SUM(A1:A8) But if I just do cell.setValue("=SUM(A1:A8)") I get "error: Unknown range name A1", unless I afterwards edit the cell content and press enter, without really having changed its content.

Is there any way of fixing this? I don't want the calculation to be in the expand-columns-function because I do want the cell-calculation to be updated when one of the referenced cells change. Is there a way of doing it besides having an onEdit-function listening to changes in the referenced cells and updating the calculations?

Thanks

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
Clox
  • 1,923
  • 5
  • 28
  • 43
  • slightly related (10 results found with same tags plus "auto fill"): http://stackoverflow.com/questions/16859941/autofill-script-google-spreadsheet-script – cregox May 20 '14 at 13:42

1 Answers1

6

To set a formula on a cell you should use cell.setFormula not setValue.

I'm not sure I understood you "expand" issue completely, but I think an opened range sum function may suit you better than apps-script. e.g.
=SUM(A:A) or =SUM(A2:A)

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Ah, course. That was stupid of me not having thought of that. I didn't quite get your second sentance though, haha. What I meant was basically that everytime when I use this spreadhseet I want to add a couple more columns, then auto-fill them. However I don't think the normal auto-fill feature would do because I'm filling the new cells with content based on complex formulas and conditions, plus I want to specify the width of the column amongst other things. Hence I'm using a scripted function that adds the new columns and fills their cells. – Clox Dec 03 '11 at 23:21
  • I think I understand now. Well, using apps script seems like the right thing to do given your requirements. Since the setFormula solved the calculation problem onEdit is not necessary anymore, right? If so, is there anything left to answer? Should I just remove my 2nd sentence and you'd accept the answer? – Henrique G. Abreu Dec 04 '11 at 00:50
  • Yeah, sorry. Just waited for an answer because I was curious if there was a better way of doing something that I didn't know of or something like that. Anyway, I've accepted and upvoted =) Thank you! – Clox Dec 04 '11 at 10:02
  • 1
    Thanks Clox. It was a genuine doubt of mine, not a complain :) And yes, if not through a formula, you'd have to use onEdit, which would be very cumbersome. Also, onEdit has a lot of minor "misbehaviors" that make it not a great solution. Because of this, you'd also have to provide a way (e.g. a menu) to force a full recalculation. – Henrique G. Abreu Dec 04 '11 at 10:13