Copy cell value of volatile function on form submission in Excel/Google Sheets
I am using Zapier to add new rows to Google Sheets via a form. One of the cell values in the new row calculates the stock price via Google Finance and another a unique ID.
The problem is both of these are "volatile functions" meaning they recalculate constantly and on any changes to the sheet(rand).
How can I copy the cell values of these two funtions when the form is filled in and new row added? Using Google Sheet functions or perhaps Google App Script?
The old copy/paste obviously works, but need something automated that works across large numbers of cells
https://docs.google.com/spreadsheets/d/1j8UTXo0-nBss5BR-_it3VYSi83UdjwsTAs3S3B0Dk6E/edit?usp=sharing
Not entirely sure what you are asking here as the sheet is not accessible, I've requested access. However from what I understand, when a new value is added (in a new row), you would like the formula also to be copied down and calculated.
I'm presuming that you already have all required values for the formula to work in the Zap.
Zapier has help for this here - https://zapier.com/help/google-sheets/#rows-are-being-added-below-rows-with-formulas
To give you a quick summary, you can write your formula in the Zap itself. Just replace the arguments that the function needs with the values in the Zap.
If you read their article, they also have a method to do this when all data for the function arguments are not available in the Zap.