[{"id":"528e7c85-ad74-4b92-9f07-1b35686aae11","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"b2467823-b20a-4944-8436-3f4a40c2c3a6","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f9b599ec-cdc1-43e1-9642-54622ec0faf6","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"0c129b8a-7ec0-4a16-8d94-9d4c96121d6b","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"747081eb-5a06-4bac-8e9e-ce86732d168f","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"7d934b01-e977-477a-b989-d25201e93a61","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"62adecb9-3003-4dce-9fa0-33dcdebbd1ed","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"e2676e30-9712-47a0-ae38-733003d74c61","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6ef06797-8c4c-4b4b-ab10-bb5049aedeff","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"48e7593f-03b2-454d-a45c-cfebca71f3aa","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"59bb5a92-94c0-471a-bbe7-422ee9c9671d","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"56744168-6aab-46c6-8144-702458dc0030","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6e65ca69-e34b-47ac-a900-df6142d1173a","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"69a3b51b-bbd6-4bf9-b1bf-404bc1130679","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"7e9ac756-4b27-4ccd-87b4-d959dae40aac","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f9f145be-535c-49f0-85e0-46b82e05ebb9","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f21c39dc-8e0d-4974-8795-5568b46411b4","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f1c5b466-1fd4-4130-88dd-9a624a6ea008","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"61aee7ec-5eae-4b92-b381-bc6236377b8b","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"8f3919c2-f0c9-4000-8008-be65026b6290","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1ec6d1ad-90cc-4094-980f-43aa5fbf9540","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"78b0db4b-017c-4839-8c1e-60f6f5cc6d2b","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"cdf43116-31aa-4260-b33a-9a31c6845eb2","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"9e8044a9-aa63-4694-997d-13747cd3ee9d","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"ecd23122-8f53-4c60-837d-198fd699dfc0","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"7a9b6718-9f7b-4f86-a13f-1af00e6e1fd8","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"363dda9e-fe69-4e08-aae8-37d0147254e3","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"5c96fdcf-19a2-4aed-838a-8ed87a27a690","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"43ba9142-9a42-4717-866b-92a6acafcf6e","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"58f7dc81-7471-48e9-9020-1a2359e01121","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"0536592d-c039-4a1b-b482-aa0ba315a78d","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2981cffc-37a3-4e64-b682-71d898259ddc","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"bdfcce31-4e7a-46f0-8571-d643d6ae5215","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"a1c74507-1bc8-4333-b919-337bcd7f421a","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6bb55ad9-04e0-47f2-8343-5e9cb0f24fbc","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f668bfbe-c4fa-4b5f-add5-f82db90b9710","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"6c4638bd-062c-4cc3-97c1-0daafc8624de","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"55aca15e-f3aa-46ab-95c6-9af4e0c779bc","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"000b26f4-c878-447f-acfc-d7e5571dbd01","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2822ff18-ccc4-40ab-b205-760e5dc66a93","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"978e1414-788c-42b3-ab45-c91e172a80f8","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2fced7df-d3ca-43a7-86d8-dfebd34541f7","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"f7b8e156-8572-43aa-aec7-fe176eacc758","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2a1a5e3f-2bbe-4b96-8587-dbe13a089d4d","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"9a3f74e4-ebdf-4c28-ac95-6901cccf878c","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"9fd5b62f-8f1b-444e-9dcb-3d71965cfc6a","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"ab33528a-79c3-4b1c-82dd-15c99eb9419b","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"3a2e4995-96ce-4123-b7a9-d801c3135e5c","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"1480a066-6c2d-4d50-bf05-fcfb0ea27869","tags":[{"product":null,"links":null,"id":"78ee7eb0-7046-4328-b5b1-adffaa31c45b","name":"New","color":"red","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"533dc174-547d-4419-8260-a62dc1e3a2b6","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"2756d249-e4ca-48db-9775-1b76fc3efc5a","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]},{"id":"c0e02434-b0d8-4b70-83ef-f6ad2ac07536","tags":[{"product":null,"links":null,"id":"a6e583c6-ea84-4023-910e-aaf8c71c5fea","name":"Upd","color":"blue","productId":"c15646e4-63d8-4965-8d62-7549a2986e99"}]}]
SpreadJS supports shared formulas so that the users can apply shared formulas to a range of cells using the formula method. This avoids duplicating the same formula for each cell in a range and ensures better performance when importing or exporting from JSON files and inputting formulas. It also helps to cut down the exported file size and lowers the memory cost.
Consider a scenario where you have a worksheet filled with data in the following table format:
Let's say, you want to add a function in the Total column that will calculate the total price for each item.
The formula that calculates the total price will be Qty * Price, that is, for cell E3, it will be “=C3*D3”.
There are two ways to do this:
Apply formula to individual cells
In this method, the cell references for each individual row have to be updated so that the Spread cells can parse each formula individually. But this may consume a lot of time for worksheets with large data and complex formulas. You may also need to add extra lines of code and use loops as well.
Apply shared formula
The second method uses shared formulas. This will automatically update the cell references in each row and calculate the total.
Instead of adding a formula for each cell in the Total's column, we use the shared formula "=C3*D3". The shared formulas approach uses the Formula (value?: string, autoAdjustReference?: boolean) function, which provides the autoAdjustReference parameter. This parameter can be set to true to adjust the relative reference of each cell.
In order to use the shared formula, specify the range over which you want to apply the formulas. In this example, the range is [E3:E6], and you can use the getRange method to get the specific range of cells. You can also set the first cell value in the formula method, that is, E3 and the function will calculate the values for the rest of the cells in the Total column automatically.
The following code snippet depicts the use of the formula method to implement shared formulas in worksheets:
// Use the Shared formula by setting autoAdjustReference to true
sheet.getRange(2, 4, 4, 1).formula("=C3*D3", true);
Let's set the autoAdjustReference parameter to false and see the difference.
As observed, only the cell values in C3 and D3 are taken and updated in cell range E3 through E6 when the parameter is false.