How can I disable automatic recalculation on google sheets?

Question

Simplified scenario:

Sheet Customer_Orders, has blocks of rows with each row having product code, count ordered, and size. Bunch of other stuff is looked up/calculated on the basis of these three tidbits. By the end of the season this sheet has about 5000 rows.

Sheet Raw_Inventory has start of year in the first 500 rows, and then does a query to Customer_orders. By season end this sheet has about 2000 rows.

Near as I can tell, this query runs every time I change one of the 3 fields in Cust_Orders.

Sheet Inv_Status is a pivot table that runs against Raw_Inventory, and again, I think that every time Raw_Inventory is modified, the pivot table is recalculated. (There are a couple of other pivot tables that use the same data.)

The result is that making a change on Cust_Orders can result in up to 2 minutes while the calculations catch up.

(Hardware: Mac Pro, 24 GB ram, 3.2 GHz, 4 core; Current version of Chrome running under Yosemite)

What I would like to do is one of the following:

• Lengthen the time between updates.
• Be able to recalculate sheet Raw_Inventory manually.

A partial workaround:

I've created a new sheet that imports raw_Inventory. This copy is used for the pivot table. ImportRange only runs every 30 minutes.

The next step will replace the query with 1 zillion simple assignment statements. I'm hoping that this will replace querying 3000 lines with querying a single line when I make a change in Cust_Orders.

Carpel

Answer 1

This is not an answer to my original question -- how to control recalculation, but is a workaround, and ultimately a better solution.

Quick restatement of problem:

CustOrders pulled descriptions of inventory off of RawInv sheet.

RawInv updated from CustOrders. This wasn't quite a circular dependency, as RawInv only updated quantities from CustOrders. But it meant that anytime a change was made in CustOrders, RawInv needed to be recalcuated.

This was made worse by having one query per line creating descriptions.

---

The solution amounted to refactoring.

• Another spreadsheet was created, CustSupport.
• It kept RawInv and Trees -- the latter being the descriptions. It also had the master reference sheet for prices and round off tables. These two tabs are rarely changed, and are copied as needed to sheets that use them.
• It imported a copy of CustOrders. Since this copy had no dependencies back to to main ordering sheet, I didn't have to wait for it to recalculate.
• RawInv recalculated from this copy of CustOrders.
• I did a wholesale replacement of Querys with VLookups. This required some rearrangement of columns.

Carpel

Answer 2

I had a similar problem, I solved it by creating an enabling cell and in that cell I put 0 or 1 and then I used that cell inside the formula. In such a way that:

	A	B
1	enable formula	0
2	= if(B1=0; 0; complex_formula1)	= if(B1=0; 0; complex_formula2)
3	= if(B1=0; 0; complex_formula1)	= if(B1=0; 0; complex_formula2)

This way when I need to change the spreadsheet I disable formulas (putting 0 on A1), change spreadsheet and on end I enable formulas (putting 1 on A1).

It's not the best solution, but it worked for me.

Mitis

I'd run into this solution before. The problem is that when it's off, all the calculated cells go blank, which means it's no good as a reference. – Carpel

Yes, you right, it's not the best solution. Unfortunately, google spreadsheet doesn't have option to stop recalculate. I searched every where. – Mitis

Answer 3

There is no way to disable automatic recalculation in Google Sheets. One option is to replace the formulas by the values either by using copy/paste as value only or by using a script. The advantage of using a script is that it also could be used to add again the formulas when needed.

Related

• Ho do I stop and start autoupdating in Google Sheets?
• Formulas always recalculating when refreshing/loading spreadsheet

Rezzani

Answer 4

To work around this issue, I wrote a simple script:

function triggeredCalc() {
    //calculate sum in column A below A1 in a temporary cell
    //then copy the result into the result cell and clear the temporary cell
    sheet = SpreadsheetApp.getActive();
    tempRange = sheet.getRange('B1:B1');
    resultRange = sheet.getRange('A1:A1');
    tempRange.setValue('=SUM($A2:$A)');
    resultRange.setValue(tempRange.getValue());
    tempRange.setValue("");
}

Morril

Answer 5

The method of SauloAlessandre resolves the re-calculation but not the storage as mentioned in the comments

I'd run into this solution before. The problem is that when it's off, all the calculated cells go blank, which means it's no good as a reference.

This simple artifact does the job of disabling re-calculation and keeping the last calculated value.

It does however require to enable circular dependency (not a big deal) because J32 depends on itself.

---

And yes, you can modify the calculating IF to store its own value

=if(L32,rand()*100,K32)

The problem comes when you are happy with the result, walk away and forget to turn it off, and the formula keeps sending API request consuming your credit ... (no need to worry if you are not doing API requests inside your formula).

The version on the first GIF is more verbose for illustrative purposes.

---

With a bit of creativity, you can get visual feedback of when was the last update and conditional formatting on the switch to avoid leaving it ON.

LAST_UPDATE() is a named function with ="Updated: " & TEXT(NOW(), "hh:mm:ss dd-mmm")

---

To enable circular dependency

File > Settings.

---

WARNING: If you use this to prevent execution of expensive calculations/calls, be aware that they may still happen when you open the sheet or after some time (look at Logs of executions). For that reason I ended up blocking their execution with 2 keys (the switch and a condition of content in the cell [cell must be empty or error or a special code]).

=if(switch,{"str_enabler",calculation},{LAST_UPDATE(),NEXT_CELL_VALUE()})

Feel free to raise bugs/complaints or suggest improvements.

Unlay