title: Google Spreadsheet Script: Query(Importrange data not up dated when Cell Value is entered via script
body: <p>I am new to Scripting. </p>
  
  <p>I have One Master Task List where in Status of all the Project assigned to other can be tracked.  File Link:  <a href="https://docs.google.com/spreadsheets/d/1WPi_YLm3XgfTRHYrLfK_8f2tgUQE-4SpHsjU0vR1bes/edit?usp=sharing" rel="nofollow">https://docs.google.com/spreadsheets/d/1WPi_YLm3XgfTRHYrLfK_8f2tgUQE-4SpHsjU0vR1bes/edit?usp=sharing</a></p>
  
  <p>There are several other spreadsheets (Task) shared with persons to whom task are assigned. Relevant columns are pulled from Master File using Formula =Query(Importrange……. 
  File Link:  <a href="https://docs.google.com/spreadsheets/d/1KBvM-P1PrLlr7z-i47_cEQF-qe9zATA4cygsggDRHxI/edit?usp=sharing" rel="nofollow">https://docs.google.com/spreadsheets/d/1KBvM-P1PrLlr7z-i47_cEQF-qe9zATA4cygsggDRHxI/edit?usp=sharing</a></p>
  
  <p>I have added script to this Task file whereby whenever Assignee updates on status or Remark in Column H &amp; I, data is captured and MOVED to Master Sheet at the respective rows &amp; Column. </p>
  
  <p>Problem faces is that though Data gets properly recorded in the Master Task List File but same is not reflecting / does not get updated in Task File in-spite of the fact that  data is pulled from Master Task List file. Script reproduced below.</p>
  
  <pre><code>function onEdit() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var SourceSheet = ss.getActiveSheet();
    var firstDataRow = 2; // only take into account edits on or below this row
    var lastDataRow = ss.getLastRow(); // only take into account edits on or above this row
    var firstDataColumn = 8; // only take into account edits on or to the right of this column
    var lastDataColumn = 9; // only take into account edits on or to the left of this column
    var SourceCell = SourceSheet.getActiveCell(); //-
  
    if (SourceSheet.getName() != "Sheet1" ) return;
    if (SourceCell.getRow() &lt; firstDataRow || SourceCell.getColumn() &lt; firstDataColumn ||
        SourceCell.getRow() &gt; lastDataRow || SourceCell.getColumn() &gt; lastDataColumn) return;
  
    var SourceValue = SourceCell.getValue();
    var TargetCell = SourceSheet.getActiveCell();
    var SourceRow = SourceSheet.getActiveSelection().getRow();
    var SourceColumn = SourceSheet.getActiveSelection().getColumn();
    var InvoiceRange = SourceSheet.getRange("L"+SourceRow);
    var InvoiceCell = InvoiceRange.getValue();
    //Browser.msgBox(SourceValue, Browser.Buttons.OK_CANCEL);
  
    var df = SpreadsheetApp.openById("1WPi_YLm3XgfTRHYrLfK_8f2tgUQE-4SpHsjU0vR1bes");
    Logger.log(df.getName());
    var ds = df.getSheetByName("Task");
    var lastRow = ds.getLastRow();
    var startRow = 2;
    var DLastColumn = ds.getLastColumn();
    var DRange = ds.getRange(1,3,lastRow-startRow+1,1);  //-
    var numRows = DRange.getNumRows(); //-
    var MatchCellValues = DRange.getValues(); //-
  
    for (var i=0; i &lt;= numRows - 1; i++) {
     var MatchCell = MatchCellValues[i][0]; 
      if (MatchCell == InvoiceCell) {
              // Found our match
       Browser.msgBox('Data Updated', Browser.Buttons.OK_CANCEL);
       //Browser.msgBox(SourceValue, Browser.Buttons.OK_CANCEL);
  
        ds.getRange(i+1,SourceColumn).setValue(SourceValue);
        //ds.getRange(i+1,DLastColumn).setValue('*');
        SpreadsheetApp.flush();
        break; // Done, exit loop
      }
   } 
  
     var SourceSheet = ss.getActiveSheet();
    var cell = SourceSheet.getRange('k1') 
     cell.setValue('*')
     TargetCell.clearContent(); 
     SpreadsheetApp.flush();
  
    //var cell1 = SourceSheet.getRange('A1') 
    //cell.setValue("=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/1WPi_YLm3XgfTRHYrLfK_8f2tgUQE-4SpHsjU0vR1bes/edit#gid=0","Task!A:J")},"SELECT* WHERE Col7 = 'JOHN' ")")
  }
  </code></pre>
  
  <p>My second issue is that I get error message for line 53 in my script. I intend to use same in other script to rewrite same in case of accidental changes in Cell A1. </p>
  
  <p>Thanks in Advance.
  Regards,
  Deven.</p>