copyTo function is only working on second pass, wi

问题:

So I Frankenstein's monster-ed this script probably 6-8 months or so ago. When last I used it everything was working exactly as expected, but now it's broken. I am trying to take a list of x-number of people, and either randomize the list or keep the list in the same order, then remove any empty cells, and then copy that information to a static column that I can use for later formuals.

What is currently happening, whether I try to randomize or not, is that on my first pass the sort and/or filter formulas work perfect, but the information does not copy, at all. It should end up on the sheet scriptsLocked in column C. Now after the first pass, if I then run the same option again right away (random or set), it will finally run the copyTo, but it leaves the first cell blank at scriptsLocked C1, the rest of the copied information is intact, but missing that first value. Any help would be superbly awesome, thank you!!

Here is my entire script code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('4mans Menu')
    .addItem('Clear Sheet', 'clearSheet')
    .addSubMenu(ui.createMenu('Generate Champ Lobbies')
    .addItem('Random', 'champMenuRandom')
    .addItem('Set', 'champMenuSet'))
    .addToUi();
}

function clearSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
    sheet.getRange("scriptsLocked!A1:C32").clearContent();
    sheet.getRange("champLobby!B4:C35").clearContent();

    sheet.getRange("champData!D2:H97").clearContent();
    sheet.getRange("champData!M2:Q97").clearContent();
}

function champMenuRandom() {
  var sheetScriptsLocked = "scriptsLocked"  

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetScriptsLocked);

  var cell = sheet.getRange("A1");
  var cell2 = sheet.getRange("B1");
  cell.setFormula("=sort('champLobby'!B4:B35,arrayFormula(randbetween(sign(row('champLobby'!B4:B35)),1000000)),true)");
  cell2.setFormula("=filter('scriptsLocked'!A1:A32; len('scriptsLocked'!A1:A32))")

  sheet.getRange("B1:B32").copyTo(sheet.getRange("C1"), {contentsOnly:true});
}

function champMenuSet() {
  var sheetScriptsLocked = "scriptsLocked"

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetScriptsLocked);

  var cell = sheet.getRange("B1");
  cell.setFormula("=filter('champLobby'!B4:B35; len('champLobby'!B4:B35))");

  sheet.getRange("B1:B32").copyTo(sheet.getRange("C1"), {contentsOnly:true});
}

回答1:

It's possible it is taking a while to calculate the sort and filter cells after you call setFormula, so the values that copyTo is copying are blank the first time you run it. arrayFormulas in particular can take a while to calculate.

Try placing a flush between setFormula and getRange:

cell.setFormula("=sort('champLobby'!B4:B35,arrayFormula(randbetween(sign(row('champLobby'!B4:B35)),1000000)),true)");
cell2.setFormula("=filter('scriptsLocked'!A1:A32; len('scriptsLocked'!A1:A32))")

SpreadsheetApp.flush();
//If that doesn't work try sleep for 1 or more sec:
//Utilities.sleep(1 * 1000)

sheet.getRange("B1:B32").copyTo(sheet.getRange("C1"), {contentsOnly:true});