How to sync data from 2 different columns to another speadsheet?

I have a sheet where I am trying to sync 2 sheets with column 13 and 14 by comparing it to Column column 2 Serial Numbers

I was able to get this code working to sync only one of the column which is Column 13 but now I want to also sync column 14 to the same sheet. Can anyone help me out here on what changes I have to do to replicate it with multiple columns?

    function updateMaster(sn,valuen) 
    {
      var destfile=SpreadsheetApp.openById("SHEETID");
      var ds=destfile.getSheetByName("SheetName");
      var lr=ds.getLastRow();
      var values=ds.getRange(1, 2,lr,1).getValues();
      for (var i=1;i<lr;i++)
      {
       // Browser.msgBox(values[i][0] + " " + sn)

        if(values[i][0]==sn)
        {
          ds.getRange(i+1, 33).setValue(valuen);
                break;   
        }
      }    
    }   

    function ReadRow(e) 
    {
      var range = e.range;
      sht=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var col=range.getColumn();
      var row=range.getRow();
      var value=range.getValue();
      if(col==13)
      {
        var sn=sht.getRange(row,2).getValue();

    //   Browser.msgBox(sn)
        updateMaster(sn,value) 
      }

    }

1 answer

  • answered 2019-09-10 08:10 ziganotschka

    If you want to sync both column 13 and 14 instead of column 13 only,

    change if(col==13) to if(col==13||col==14)

    However, in your function updateMaster you have the line ds.getRange(i+1, 33).setValue(valuen);, i.e. you are setting the value in column 33. Is this what you want to do for both column 13 and 14?

    If not, modify your function to function updateMaster(sn,valuen,col) and differentiate between columns e.g. like

      if(col==13){
        ds.getRange(i+1, 33).setValue(valuen);   
       } else if(col==14){
        ds.getRange(i+1, 34).setValue(valuen);   
       }
    

    and call updateMaster(sn,value,col); within function ReadRow(e) .

    Full code:

     function updateMaster(sn,valuen,col) 
        {
          var destfile=SpreadsheetApp.openById("SHEETID");
          var ds=destfile.getSheetByName("SheetName");
          var lr=ds.getLastRow();
          var values=ds.getRange(1, 2,lr,1).getValues();
          for (var i=1;i<lr;i++)
          {
           // Browser.msgBox(values[i][0] + " " + sn)
    
            if(values[i][0]==sn){
              if(col==13){
                ds.getRange(i+1, 33).setValue(valuen);   
              } else if(col==14){
                ds.getRange(i+1, 34).setValue(valuen);   
              }
           }    
        }   
    
        function ReadRow(e) 
        {
          var range = e.range;
          sht=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
          var col=range.getColumn();
          var row=range.getRow();
          var value=range.getValue();
          if(col==13||col==14){
            var sn=sht.getRange(row,2).getValue();
        //   Browser.msgBox(sn);
             updateMaster(sn,value,col);
          }
        }