I have a google sheet with a timer set for 5am and everyday I want the sheet to:
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);}
}
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.
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);
}
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 ;)