CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
aediger2
aediger2

Reputation: 35

CopyPasteType.ValuesOnly returns blank cell sometimes

I am trying to create a script that put a randbetween function into a cell and then copy/pastes it with values only.

I have two scripts that work separately, but when I try to combine them into one script it returns a blank cell.

Here is my first script to put the function into the cell. This puts the function into the cell, but the Values_only part doesn't seem to do anything as it leaves the function in the cell.

function letterDie() {
  
  var ss = SpreadsheetApp.getActiveSheet();
  var rand = "=index(Scategories!$A$15:$B$41,match(RANDBETWEEN(1,26),Scategories!$A$15:$A$41,0),2)"
  var cell = ss.getRange('E1');
  
  var letter = cell.setValue(rand).copyTo(cell, SpreadsheetApp.CopyPasteType.PASTE_VALUES);

}

Then here is the code I have that copies and pastes the function with values only so that I am left with just the random letter. This code also works fine.

function copyPaste() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E1').activate();
  spreadsheet.getRange('E1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

I can run the first and then the second and it works fine, but when I try to combine them into one function, the cell is blank.

I have even tried creating a function to call the first, then the second but it still returns a blank cell.

function rollDie() {
  
  letterDie();
  copyPaste();
  
}

Upvotes: 0

Views: 72

Answers (1)

Marios
Marios

Reputation: 27390

The issue is that both functions are making changes to the spreadsheet file and these changes depend on each other. Namely, letterDie() sets the value of cell E1 and copyPaste() gets that value.

Try to flush() the pending changes between the executions of the two functions:

function rollDie() {
  
  letterDie();
  SpreadsheetApp.flush();
  copyPaste();
  
}

Upvotes: 1

Related Questions