Generic Google App Scripts to import another spreadsheet rows based on the value of a column (condit-CodePudding

I am trying to create a generic template which will import data from a master Spreadsheet, to a child spreadsheet based on a condition.

Example: Delete all rows in child spreadsheet, then, import all rows from master spreadsheet that contain "Yes" into the empty child spreadsheet.

The problem with this code, is that it only works within the same spreadsheet since it uses copyTo. In order for GAS to communicate with other spreadsheets, I need to remove copyTo and replace it with getValues and setValues. I cant seem to get this to work still.

Here is the current code I am using. How can I get this code (or similar code) to achieve what I am looking to accomplish?

function copyRows() {

  //Source Info.
  var sourceSpreadsheet = SpreadsheetApp.openById('15a9...');
  var sourceSheet = sourceSpreadsheet.getSheetByName("Sheet1");
  var sourceLastRow = sourceSheet.getLastRow();
  
  //Destination Info.
  var destinationSpreadsheet = SpreadsheetApp.openById('1DtL...');
  var destinationSheet = destinationSpreadsheet.getSheetByName("Sheet1");
  var destinationLastRow = destinationSheet.getLastRow();
  
  //Condition to search for.
  for (var i = 2; i <= sourceLastRow; i  ) {
    var cell = sourceSheet.getRange("T"   i);
    var val = cell.getValue();
    
    if (val == "Yes") {
      var srcRange = sourceSheet.getRange("A"   i   ":Z"   i);
      var destinationRow = destinationSheet.getLastRow();
      var destinationRange = destinationSheet.getRange("A"   (destinationRow 1)   ":Z"   (destinationRow 1));
      
      srcRange.copyTo(destinationRange);
    }
  }
};

All of the previous questions in Stackoverflow seem to only be for importing data to a new tab within the same spreadsheet.

CodePudding user response：

Try this:

function copyRows() {
  //Source Info.
  const sss = SpreadsheetApp.openById('15a9...');
  const ssh = sss.getSheetByName("Sheet1");
  const svs = ssh.getRange(2, 1, ssh.getLastRow() - 1, ssh.getastColumn()).getValues().filter(r => r[19] == 'Yes');
  //Destination Info.
  const dss = SpreadsheetApp.openById('1DtL...');
  const dsh = dss.getSheetByName("Sheet1");
  dsh.clearContents();
  dsh.getRange(1, 1, svs.length, svs[0].length).setValues(svs);
}

Filter