google sheets script conditionally move row to another spreadsheet

Question

I am new to this and I am having some trouble trying to figure this out. I have a spreadsheet that collects data from a google form. I am trying to find a way to move that data based on a column answer to a different google sheet. (Not a sheet in the same document but a different document all together). It seems like there is some information about moving to a different tab in the same document, but not a different document.

I will first say that I tried just an IMPORTRANGE function, but that only mirrors the data, and does not let you update or change cells.

Here is what I have been able to piece together so far, but I may be way off.

I have a trigger that would run every hour.

function myFunction() {
 var ss = SpreadsheetApp.openById('1U0I9SkbGkHgm-vRkwf2Ppc_yxlqrVlg2t8yKRy3sYuI');
 var sheetOrg = ss.getSheetByName("Form Responses 1");
 var value1ToWatch = "ANDERSON";
 var value2ToWatch = "BARNES";
 var sheetNameToMoveTheRowTo = "Sheet1"; //sheet has same name for each target openByID(" *url key* ")
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var ts1 =SpreadsheetApp.openById("1PxV1PQrMdu_2aSru4dpan8cUgHYdlYPUp5anyzjMAms");
     sheet1in = ts1.getSheetByName("Sheet1");
 var ts2 = SpreadsheetApp.openById("1BYyQZNiXc2QqsyqWs7uazjl5B6mfFYM1xj3u8gWyYOQ");
     sheet1in = ts2.getSheetByName("Sheet1");

 arr = [], 
values = sheetOrg.getDataRange().getValues(),
i = values.length;
while (--i) {
  if (value1ToWatch.indexOf(values[i][1]) > -1) {
    arr.unshift(values[i])
   sheetOrg.deleteRow(i + 1) 
   sheet1in.getRange(sheet1in.getLastRow()+1, 1, arr.length, 
     arr[0].length).setValues(arr); 
  };

 if (value2ToWatch.indexOf(values[i][1]) > -1) {
    arr.unshift(values[i])
   sheetOrg.deleteRow(i + 1) 
   sheet2in.getRange(sheet2in.getLastRow()+1, 1, arr.length, 
 arr[0].length).setValues(arr);
};
}
}

1. The sheet google forms dumps the information into is "Form Responses 1".
2. Column B is the cells I want to get the values from. (There are a total of 9 different values that it can be like "ANDERSON", "BARNES", "SMITH", etc).
3. For sheetNameToMoveTheRowTo is "Sheet1" - that may be confusing and I may need to change that, but for example
   • ts1 =SpreadsheetApp.openById("1PxV1PQrMdu_2aSru4dpan8cUgHYdlYPUp5anyzjMAms") the sheet name that I want the information moved to is "Sheet1".
   • ts2 = SpreadsheetApp.openById("1BYyQZNiXc2QqsyqWs7uazjl5B6mfFYM1xj3u8gWyYOQ") the sheet name is also "Sheet1" but in a different document.

I think if I were able to get the "ANDERSON" one to work, then I can just add additional variables for each possible response, and just copy and paste additional "IF" statements, just changing the valueToWatch and targetSheet values. <= if that is not correct please let me know

I have tried to both debug, and run the script above but nothing happens. There are no errors reported on the debug, but it is not moving any information over.

Any idea what I am doing wrong?

// UPDATE I got this to work. I have updated the code listed with what worked for me.

Answer 1

Here's a simple example of moving data from one spreadsheet to another.

function movingDataToSS(){
  var ss=SpreadsheetApp.getActive();
  var dss=SpreadsheetApp.openById('ssId');
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var dsh=dss.getSheetByName('Sheet1');
  var drg=dsh.getRange(1,1,rg.getHeight(),rg.getWidth()).setValues(vA);
}

If you interested in placing some conditions on your output by only getting output from odd rows and even columns.

function movingDataOddRowsAndEvenCols(){
  var ss=SpreadsheetApp.getActive();
  var dss=SpreadsheetApp.openById('ssId');
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var h=rg.getHeight();
  var w=rg.getWidth();
  var dsh=dss.getSheetByName('Sheet1');
  for(var i=0;i<h;i++){
    var out=[];
    if(i%2==0){
      for(var j=0;j<w;j++){
        if(j%2==1){
          out.push(vA[i][j]);
        }
      }
      dsh.appendRow(out);
    }
  }
}

Answer 2

I think that copyTo() method will not work like you mentioned, it operates on same SpreadSheet. I'm sending you example with looping on source sheet data and then setting the target sheet values with it.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var allrange = ss.getActiveRange();
  var range = sheet.getRange("A1:A10");
  var values = range.getValues();
  var allvals = [];
  for(var i = 0; i < values.length; i++) {
    allvals.push( values[i] ) ;
  }
  var dataLength = allvals.length;
  // alert data
  var ui = SpreadsheetApp.getUi();
  // ui.alert( JSON.stringify(allvals) )

  // copy to new Google SpreadSheet
  var newSheet = SpreadsheetApp.openById('1F79XkNPWm2cCWlB2JP3K4tAYRESKUgtHK4Pn2vbJEiI').getSheets()[0];
  var tmp = "A1:A" + dataLength ;
  var newRange = newSheet.getRange( tmp );
  newRange.setValues( allvals );
}