How to Overwrite the Column that is calculated based on other column

Hi All,

Let’s assume that I have a table with 3 column as follows.

|Price |Quantity | Total     |
| 10   | 5       | = a2 * b2 |

Now these price and Quantity column gets overwritten every month using a {data} field. What I want is Total column to overwrite as well as the Price and Quantity get overwritten. How do I automate this Total Price calculation?

Thanks,
Pankaj Kumar

1 Like

Hey Pankaj — given that the Total column is being computed based on a multiplication formula, it will be computed automatically when new data is being written/overwritten in the Price & Quantity columns.

Have you already tried overwriting the Price & Quantity columns?

Here’s what I’m thinking you need —

overwrite

Happy to jump on a quick call too to help you set this up :blush:

Well here you are assuming that table 1 will have only 3 data rows. Now if you add 4th data row in table 1, your overwrite will only populate the price and Quantity in table 2 and you need to manually calcualte the Total column for new added row. What I need is as I add rows in table 1 all column of table needs to updated or inserted. I guess I need a feature where I can apply a formula on column instead of cell like in you example if I somehow can put a formula that this total column , that will muliply the Price and Quantity. eg =compute(a2:a, b2:b, multiplication) i.e first data source and second data source and third as formula to apply.

1 Like

Ah! I see. You can achieve this by using our FILL function.

Also, my bad on using the bound ranges in the demo scenario :sweat_smile: — we can just as easily change the usage to reflect working on rows beyond 3 data rows too.

Does this look more to what you’re trying to do?

example-scenario

Hi Sourabh,

Well it does acheive what I was trying to acheive but I has to use a dummy row in table2 to keep the formula so that fill can follow that to fill the rest of actual rows. Let me explain my question a little bit more.

Go to this public sheet

Now assume that this {data} cell in table 1 is returned from a API call (here I had just used the range to json to explain but in actual this {data} cell is updated with some API call.

Now you are not allowed to change anything in table 1 and in the table 2 you are allowed to put any formula in first row only i.e header row for column C, D & E. A1 cell of Table 2 contain “=OVERWRITE(‘Table1’!$C$1)” formula. Now how to you change table 2 such that all 3 column i.e C, D & E get computed as column A & B get overwritten with new data.

Double in Header of table 2 means double of what is says like 2 * price, 2 * quantity, total = c2 * e2

You are not allwed to add more table. Only thing you can do is put as many as formula or coulmn in row1 of table 2. Also assume that this {data} cell can have any number of entries.

Thanks,
Pankaj Kumar

1 Like

Cool challenge — while I don’t have a solution right now, will reach out in case something pops-up :blush:

You are also correct about having a formula in row 2 of Table2 (that can be used by FILL) and this is typically a non-dummy computation too (i.e. users generally use this row with actual data) — would love to understand any reservations you may have against that approach as that would help our Product team understand your needs better.

Assume that a user is receiving new monthly sales data using a API to some service. But the API data needs to filtered out i.e some modification is required on the data before it can be used. Now best way for this is use the overwrite formula in table with {data} json. Now every month this api will write the new data in the table. Sometimes this api will write only the headers assuming there is no sale in a given month, in this case your formula that fill follow will be gone as overwrite clears the rows and empty row got removed. In the next month user needs to rewrite the formula in first row (excluding header row) that fill can follow, making the automatic process manual again i.e super power lost :frowning:

Here a few notes that may help with the process —

For this, I’d recommend you could make use of our FILTER function or PARSE (which uses JsonPath) and then you wouldn’t necessarily have to OVERWRITE the data first.

As a rule of thumb, if a particular column is making use of a formula, I’d recommend NOT to have that column as part of the destination of any of our INSERT, UPDATE or OVERWRITE functions — this way, you can avoid having the cell with the formula (that would be consumed by the FILL function) be overwritten and it’d accordingly stay intact.

This is generally easier to explain over our app building session calls (preferably, over screen shares) but I’m hoping this explanation provides a more broader view on how we’ve been working with this approach so far along :slightly_smiling_face:

I do see your point about ensuring no one (even, accidentally) deletes the 2nd row which has the formula and that can be a hassle for sure :sweat_smile:
Our teams are aware of that and we are considering building an AUTOFILL type of solution but what/how that would look like is still in the making.

1 Like