Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

enter image description here


enter image description here

I am running into some obstacles while trying to design my ideal Google Spreadsheet. I am trying to do the following listed below and I am having a hard time. I was wondering if anyone can help me out.

In Sheet15 from A2 to A999 I will have a list of values. I want it to copy value from Sheet15 cell A2 to sheet Raw Data cell B1, wait for 40 seconds, check in Raw Data if B7 > 10% and B13 > 10%, if both conditions are met, in Sheet15 cell H2 write True, else False.

Second run would be copy value from Sheet15 cell A3 to Raw Data cell B1, wait for 40 seconds, check to see if Raw Data B7 > 10% and B13 > 10%, if both conditions are met, in Sheet15 H3 write True, else False.

Keep continuing this until A999 or so.

share|improve this question
    
Welcome to Web Applications !! The title of your post isn't a catchy one, and besides that, it gives people over here the shivers. What have you tried yourself? Were you able to find some here on Web Applications? –  Jacob Jan Tuinstra Mar 24 at 19:43
    
Well, I was trying to write =if formula, and I kept on trying that and I thought it was possible. I found out only here that it isn't possible. I do not know how to code which is why i need help... –  Saad Khan Mar 24 at 20:38
1  
Please add some screenshots, possibly some formula or code that you have created, and a more specific title for your question, like: "How do I do X in a Goole Sheet with a script?" –  user 99572 is fine Mar 24 at 21:01
    
I edited your question to match the screenshots (the names of the sheets), please check that I understood your question correctly. (When someone has approved the edit). Does the Raw Data sheet get values from someplace else, based on what is in A1? –  Punchlinern Mar 25 at 22:37
    
Raw Data!A1 should get data from the List sheet. Thanks for the edit! –  Saad Khan Mar 26 at 8:39

1 Answer 1

up vote 1 down vote accepted

I believe this is what you want. If the comments in the code isn't enough, feel free to ask me for assistance.

function compareCells(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();                   //Get active Spreadsheet
  var listSheet = ss.getSheetByName('Sheet15');                     //Get sheet "Sheet15"
  var dataSheet = ss.getSheetByName('Raw Data');                    //Get sheet "Raw Data"
  var listSheetLastRow = listSheet.getLastRow();                    //Get number of last row with data in "Sheet15"

  var currRow = 2;                                                  //Start looping on row to, since row 1 is header

  for (i = 2; i <= listSheetLastRow; i++){                          //For-loop, looping through all rows with data in "Sheet15"
    var ticker = listSheet.getRange(currRow, 1).getValue();         //Getting value in column A in current row from "Sheet 15"
    dataSheet.getRange(1, 2).setValue(ticker);                      //Setting above value in cell A1 in "Raw Data"
    Utilities.sleep(40000);                                         //Pausing for 40 seconds (40 000 milliseconds)

    var value1 = dataSheet.getRange(7, 2).getValue();               //Getting value from B7 in "Raw Data"
    var value2 = dataSheet.getRange(13, 2).getValue();              //Getting value from B13 in "Raw Data"

    if (value1 > 0.1 && value2 > 0.1){                              //If both values gotten above is greater than 0.1 (10%)
      listSheet.getRange(currRow, 8).setValue('True');              //Set the value of the cell in current row, in the H column to "True"
    }else{                                                          //Else
      listSheet.getRange(currRow, 8).setValue('False');             //Set the value of the cell in current row, in the H column to "False"
    }
    currRow++;                                                      //Go to next row
  }
}
share|improve this answer
    
ok, I put the code in, had some minor minor adjustments to make but excellent overall. But for some reason it isn't writing true for any of them. I'd like to show it to you by sharing it on Google Drive if you're interested. –  Saad Khan Mar 26 at 8:41
    
I'm glad I could help. Sure! Do it. –  Punchlinern Mar 26 at 9:13
    
How should I contact you ? –  Saad Khan Mar 26 at 10:34
    
to.oskar.modig@gmail.com –  Punchlinern Mar 26 at 11:10
    
for some reason it seems sometimes it works and really sometimes it doesn't work. I've shared the file with you on your email. If you have some time please access it so I can show you. –  Saad Khan Mar 26 at 13:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.