Repeat script until conditions are met-CodePudding

I have a google sheet with a timer set for 5am and everyday I want the sheet to:

Refresh, which includes some links to external data, so I add a 10 second pause. There are approximately 100 rows.
I successfully wrote a formula in column U to tell me if data is imported correctly, and if not it returns the text "ERROR".
Search for "ERROR" in column U.
If there are any "ERRORS" in column U, refresh the page again and wait 10 seconds.
Repeat until there are no "ERRORS"
Only when there are no "ERRORS" in column U, copy the values in column V and paste-values in column W.

I think I am close... I just can't figure out how to tell it to re-run the "IF" portion if still finds "ERRORS". Any input is appreciated!

Here is a link to the file -> https://docs.google.com/spreadsheets/d/1GFN3tXRlqxo9J9iNpZMPk-e-WlNGVjJ4zSqZ1_aEE_U

function HardKeyValues() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Players'), true);
  
  SpreadsheetApp.flush();
  Utilities.sleep(10000);
  
  var findVal = spreadsheet.getRange('U:U').getValue()
  if(findVal.match('ERROR')){
  
  SpreadsheetApp.flush();
  Utilities.sleep(10000)}
  
  else{
  spreadsheet.getRange('W:W').activate();
  spreadsheet.getRange('V:V').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);}
}

CodePudding user response：

I would not actually use the sleep timer this way

But if I did I would write it like this:

function HardKeyValues() {
  const ss = SpreadsheetApp.getActive();
  const psh = ss.getSheetByName('Players');
  SpreadsheetApp.flush();
  Utilities.sleep(10000);
  const vs = psh.getRange('U1:U'   psh.getLastRow()).getDisplayValues().flat();
  if (vs.find(e => e.includes("ERROR"))) {
    SpreadsheetApp.flush();
    Utilities.sleep(10000);
  } else {
    sh.getRange("V1:V"   sh.getLastRow()).copyTo(sh.getRange("W1:W"   sh.getLastRow(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false));
  }
}

Using indeterminate ranges like "W:W" often leads to many nulls at the end of the array.

CodePudding user response：

What about using a while loop ?

function HardKeyValues() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Players'), true);
  
  SpreadsheetApp.flush();
  Utilities.sleep(10000);
  
  var findVal = spreadsheet.getRange('U:U').getValue()
  
  while (findVal.match('ERROR')){
    SpreadsheetApp.flush();
    Utilities.sleep(10000)}
    findVal = spreadsheet.getRange('U:U').getValue()
  }
  
  spreadsheet.getRange('W:W').activate();
  spreadsheet.getRange('V:V').copyTo(spreadsheet.getActiveRange(), 
  SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

CodePudding user response：

You could maybe try using a for loop, just make sure to have a variable that can eventually meet the requirement for the for loop to stop, otherwise, it will turn into a forever loop ;)