title: Google App script - update row according to criteria (based on condition)
body: <p>I'm trying to automate a task that is below -</p>
  
  <ol>
  <li><a href="https://docs.google.com/spreadsheets/d/1AcYFOhs6XQHwhrToRaUau56I0XISEocGWmfqufH-acc/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1AcYFOhs6XQHwhrToRaUau56I0XISEocGWmfqufH-acc/edit?usp=sharing</a> (From here)</li>
  <li><a href="https://docs.google.com/spreadsheets/d/1dQiX1MOlDTnAkXKbk2AA-ojhThHbqqp_sXQhvNltCJk/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1dQiX1MOlDTnAkXKbk2AA-ojhThHbqqp_sXQhvNltCJk/edit?usp=sharing</a> (To there)</li>
  </ol>
  
  <p>These are the sample datasets that I'm using.</p>
  
  <p>So the "From here" Google Sheet data gets updated every once a week from system A1 in "From here".
  G sheet also gets updated when week # changes</p>
  
  <p>I would like to append values in "From here" to "To here" G sheet data according to the conditions below..</p>
  
  <ul>
  <li>If A1 matches WK# (Column B) in "To There" data, then fill in A,B,C,D for columns TS, TC, TD (in "To there" G sheet) accordingly</li>
  <li>Additionally, I would need these actions without embedding the formulas into each cell (there will be many users that use this google sheet (To here), so formulas shouldn't be shown in the cell</li>
  </ul>
  
  <p>So, writing a formula in the cell isn't the ideal option in this case</p>
  
  <p>Would there be any optimal way of accomplishing this task in Google Sheets using Apps Script? (Maybe on Open option?) or using Google Sheets Query?</p>
  
  <ul>
  <li>@Tedinoz, I just edited the question!</li>
  </ul>
  
  <p>So there is now "To There" Gsheet with the updated link</p>
  
  <p>basically the successful outcome is the yellow highlighted cells in "To There"</p>
  
  <p>The week gets updated by me, not by the system - whenever week changes, I will update this in cell A1</p>
  
  <p>So Below is what I tried</p>
  
  <pre><code>function vl() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet1 = ss.getSheetByName('Sheet1');
    var sheet2 = SpreadsheetApp.openById("1dQiX1MOlDTnAkXKbk2AA-ojhThHbqqp_sXQhvNltCJk");
    var range1 = sheet1.getRange("A:A1"); 
    var range1a = sheet1.getRange("A2:A5")
    var range2 = sheet2.getRange("A1:E200");
    var values1 = range1.getValues();
    var values1a = range1a.getValues();
    var values2 = range2.getValues();
  
    for (var i = 0; i &lt; values1.length; i++) {
      for (var j = 0; j &lt; values2.length; j++) {
        if (values1[i][0] === values2[j][1]) {
          values1[i][1] = values2[j][2]
          Logger.log(values1)
        }
      }
    }
  </code></pre>
  
  <p>My main struggle is on</p>
  
  <p>a) how to look up based on two criteria, which is "A1" in "From Here" sheet +
     "A2:A5" in "From here" sheet (It will have to be if "AND" statements...)</p>
  
  <p>b) after finding the matching values based on the above criteria, how would I move them onto "To There" rows accordingly..? I was thinking to use getRange().SetValues() method, but this does not seem right approach in this case..as it has to set values based on the matching criteria </p>