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.

  • 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 '14 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 '14 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 '14 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 '14 at 22:37
  • Raw Data!A1 should get data from the List sheet. Thanks for the edit! – Saad Khan Mar 26 '14 at 8:39

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
  • 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 '14 at 8:41
  • I'm glad I could help. Sure! Do it. – Punchlinern Mar 26 '14 at 9:13
  • How should I contact you ? – Saad Khan Mar 26 '14 at 10:34
  • to.oskar.modig@gmail.com – Punchlinern Mar 26 '14 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 '14 at 13:59

Your Answer

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

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