CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
JamesReed68
JamesReed68

Reputation: 419

Generic Google App Scripts to import another spreadsheet rows based on the value of a column (condition)

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.

Upvotes: 0

Views: 716

Answers (1)

Cooper
Cooper

Reputation: 64042

Try this:

function copyRows() {
  const sss = SpreadsheetApp.openById('ssid');
  const ssh = sss.getSheetByName("Sheet0");
  const svs = ssh.getRange(2, 1, ssh.getLastRow() - 1, ssh.getLastColumn()).getValues().filter(r => r[19] == 'Yes');
  const dss = SpreadsheetApp.openById('ssid');
  const dsh = dss.getSheetByName("Sheet1");
  dsh.getRange(2,1,dsh.getLastRow()-1,dsh.getLastColumn()).clearContent();
  dsh.getRange(1, 1, svs.length, svs[0].length).setValues(svs);
}

Upvotes: 2

Related Questions