1

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.

enter image description here

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');  
};
7
  • 1
    How do you call your function 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? Commented Jul 2, 2024 at 0:47
  • 1
    @Wicket After May 18, 2022, it seems that the values retrieved by GOOGLEFINANCE can be used with Google Apps Script. stackoverflow.com/a/46028979 When I tried to use the value from =GOOGLEFINANCE("CURRENCY:USDINR")*0.995*1 using the OP's script, I confirmed that the formula could be converted to a value. So, I would like to propose reopening this question. Commented Jul 2, 2024 at 0:49
  • 1
    @Tanaike Thank you very much for your reply. I agree with the reopening. Commented Jul 2, 2024 at 1:34
  • Additional to Tanaike's request, have you replicated this in a new spreadsheet?, something additional you could try is adding the flush() function to your code to see if it affects the execution of the copy paste action. Commented Jul 3, 2024 at 22:32
  • This is only happening sometimes. The actual excel has lot of personal info, hence didn't share that. But shared entire code of app script. Majority of the times the there is no issue even without flush(). Commented Sep 2, 2024 at 1:10

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.