1

I have a script thats attached below, and the problem I'm facing is that after it has keyed in "ticker" to Raw Data in b1, my entire sheet is never done loading and thus when its time to check if it's true or false AND copy the value it returns with False (mostly always) and #N/A #Error!.

All help is appreciated guys.

function compareCells(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();                   
  var listSheet = ss.getSheetByName('Sheet18');                     
  var dataSheet = ss.getSheetByName('Raw Data');                    
  var listSheetLastRow = listSheet.getLastRow();                    
  var currRow = 2;                                                  

  for (i = 2; i <= listSheetLastRow; i++){                          
    var ticker = listSheet.getRange(currRow, 1).getValue();         
    dataSheet.getRange(1, 2).setValue(ticker);                      
    (Utilities.sleep(40000));                        
    var pe = dataSheet.getRange(7, 2).getValue();
    listSheet.getRange(currRow, 9).setValue(pe);

    var fcf = dataSheet.getRange(13, 2).getValue();
    listSheet.getRange(currRow, 10).setValue(fcf);

    var value1 = dataSheet.getRange(7, 2).getValue();               
    var value2 = dataSheet.getRange(13, 2).getValue();              

    if (value1 > 0.10 && value2 > 0.10){                             
      listSheet.getRange(currRow, 8).setValue('True');              
    }else{                                                          
      listSheet.getRange(currRow, 8).setValue('False');             
    }
    currRow++;                                                      
  }
}

I'm attaching the sheet that I'm working on here. https://docs.google.com/spreadsheets/d/16cO8kyencBvUSdkDEJrhY7GL12JCpA32ipj4KaCBEHY/edit?usp=sharing Hopefully it helps

  • One thing I thought is worth mentioning is that if I add Browser.msgBox(pe + ' || ' + fcf); to the script ...it seems to delay just fine...its without it that I have trouble with. – Saad Khan Mar 26 '14 at 16:34
  • 1
    i suggest that you avoid using the api calls within the for loop... get the entire sheet values into array outside the loop and iterate over the array – DavidF Mar 27 '14 at 6:32
  • Could you elaborate further, I'm really a beginner into scripting. I could get the first value in list sheet and last value. But then I don't understand the other part. – Saad Khan Mar 27 '14 at 11:45
  • Why don't you share a doc with us? – Jacob Jan Tuinstra Mar 28 '14 at 12:52
  • I'd reallly appreciate some help guys...! – Saad Khan Mar 31 '14 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.