I have following function
function copyValues(range) {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange(range).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}
Majority of the times, it is working fine, but sometimes it is pasting a value Loading...
It may be related to some custom/named function I have, which is listed below.
USD2INR=Summary!$K$8*usd_amount
The cell Summary K8 has following value.
=GOOGLEFINANCE("CURRENCY:USDINR")*0.995*1
Few cells also have values like =-GOOGLEFINANCE("VOOG") * 2.983
My guess is that it is because of this google finance functions that are trying to get values and this is what is causing this issue during copy paste function getting executed. Can anyone suggest what I can do to fix this issue.
I call the copuValues function through macros menu in google sheets that executes following script.
function copyValues(range) {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange(range).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}
function moveRight() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getCurrentCell().offset(0, 1).activate();
}
function moveTo(sheet, address) {
sheet.setCurrentCell(sheet.getRange(address)).activate()
}
function setValue(text) {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getCurrentCell().setValue(text);
}
function setFormula(address, formula) {
var spreadsheet = SpreadsheetApp.getActive();
moveTo(spreadsheet, address);
spreadsheet.getCurrentCell().setFormula(formula);
}
function goToLastRow(sheet) {
sheet.setActiveRange(sheet.getRange("A" + (sheet.getLastRow() + 1)));
}
function createAssetTimeSeries() {
var spreadsheet = SpreadsheetApp.getActive();
var timeSeriesSheet = spreadsheet.getSheetByName('Asset Timeseries');
spreadsheet.setActiveSheet(timeSeriesSheet);
goToLastRow(spreadsheet);
var startCell = spreadsheet.getCurrentCell();
var rows = startCell.getRow();
spreadsheet.getCurrentCell().setValue(new Date()); // Set Date
moveRight();
copyValues('Summary!C18');
moveRight();
copyValues('Summary!C19');
moveRight();
copyValues('Summary!C20');
moveRight();
copyValues('Summary!D18');
moveRight();
copyValues('Summary!D19');
moveRight();
copyValues('Summary!D20');
};

copyValues? And, I guess that in this case, when your situation can be correctly replicated, it might help to understand the reason for your current issue. So, in order to replicate and test your situation, can you provide your whole script and the sample Spreadsheet?=GOOGLEFINANCE("CURRENCY:USDINR")*0.995*1using the OP's script, I confirmed that the formula could be converted to a value. So, I would like to propose reopening this question.