CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gavis
Gavis

Reputation: 233

Send a row from a spreadsheet to another based on 2 conditions with Apps Script

I am trying to send a row from a spreadsheet to another and the script below works fine. But now I am trying to add two improvements:

  1. Activate the script if in the column C there are the voices "Yellow 1" or "Green 2" or "Red 3" or "Brown 5"
  2. After sent the row to the target spreadsheet, delete the row from the source spreadsheet

I am trying with:

  1. IF statement
  2. s.deleteRow(row)

But I cannot reach the goal, cause there are some errors

function onEdit(event) {
  if(r.getColumn() == 3 && r.getValue() == 'Yellow 1','Green 2','Red 3','Brown 5') {
  var ss = SpreadsheetApp.openById('xxx');
  var r = sss.getSheetByName('source');
  var SRange = ss.getDataRange();
  var A1Range = SRange.getA1Notation();
  var SData = SRange.getValues();
  var tss = SpreadsheetApp.openById('yyy');
  var ts = tss.getSheetByName('destination');
  s.getRange(row, 1, 1, numColumns).moveTo(ts);
  ss.deleteRow(row);
}
}

Upvotes: 0

Views: 44

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal is as follows.

  • When one of the values of 'Yellow 1','Green 2','Red 3','Brown 5' is put in the column "C" of "source" sheet of the active Spreadsheet, you want to move this row to "destination" sheet in other Spreadsheet.
  • You want to run this script by OnEdit trigger.

Unfortunately, when I saw your showing script, I noticed that the script has a lot of modification points. For example, the variables of r, sss, s, row are not declared. So, in this case, how about the following sample script?

Sample script:

Please replace ### of var dstSheet = SpreadsheetApp.openById('###').getSheetByName('destination'); with your destination Spreadsheet ID. And, please install OnEdit trigger to the function installedOnEdit. Because when openById is used, it is required to use the installable trigger.

function installedOnEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var values = ['Yellow 1', 'Green 2', 'Red 3', 'Brown 5'];
  if (sheet.getSheetName() == 'source' && range.columnStart == 3 && values.includes(range.getValue())) {
    var dstSheet = SpreadsheetApp.openById('###').getSheetByName('destination');
    var rowValue = sheet.getRange(range.rowStart, 1, 1, sheet.getLastColumn()).getValues()[0];
    dstSheet.appendRow(rowValue);
    sheet.deleteRows(range.rowStart, 1);
  }
}
  • In this script, when you edit the column "C" of "souece" sheet, when the edited value is included in the values of ['Yellow 1', 'Green 2', 'Red 3', 'Brown 5'], the row is moved to the destination sheet. And, the row is deleted.

Reference:

Upvotes: 2

Related Questions