I am making a Google Sheets app that reads column A which has a stock symbol and puts the price in the column to the right using =GOOGLEFINANCE
I have this refreshing every day and appending to the column between A and B with its date code in B1.
Whenever it refreshes, the data is copied as opposed to new data being added. It seems like it is adding the formula itself to the spreadsheet as opposed to putting in the value itself.
Here is my code
function myFunction() {
SpreadsheetApp.getActiveSheet().insertColumns(2);
SpreadsheetApp.getActiveSheet().getRange('B1').setValue('=now()');
SpreadsheetApp.getActiveSheet().getRange('B2:B10').setValue('=iferror(GOOGLEFINANCE(A2:A10,"price"),"")');
}
Best Answer
If I understand correctly, you want the columns to keep a "snapshot" of the prices at the time the column was added, rather than have the values continue updating.
In order to do this, we can replace the formulas with the calculated values at the time to "freeze" the values:
I manually tested this with the
GOOGLEFINANCE
function and it did replace the function with the current price as a numerical value. You could add this to the end of your function to "freeze" the prices after getting them.Another solution might be to use the
start_date
parameter on theGOOGLEFINANCE
function. You could get the current date as a value and then pass that into the formula as thestart_date
so that would always fetch the price for that date.