Copy and clear row when a cell in the row matches a cell value in another tab on the sheet

user_18374200 0
Viewed 24

I created a sales sheet that is called "A" that I use for my job where I have all my prospects that im going to call.

I write down and update the total amount calls made to every prospect in Column E starting from row 6 in that tab.

So I want to copy the row first and then clear the row starting from column B when the amount of calls matches the number of a cell in another tab called "Backlog" and the cell is C1.

The tab I want to copy the row too is called "Nej och Ej Akt".

I have made a testsheet and I hope it makes it more clear to what I want to do.

https://docs.google.com/spreadsheets/d/1oeQmtIvoeWHpHwm4BrkHPvEb6agjBRY7ryctaKnSMcs/edit?usp=sharing

google-sheets
1 Answers

Here is a working script.

function onEdit(e) {
  var spreadsheet = e.source;
  var sheet = spreadsheet.getActiveSheet();
  var range = e.range;
  
  if(sheet.getSheetName() == 'A' && range.getColumn() == 5) {
    var value = e.value;
    // used getDisplayValue since it shows 6 but actual value is 6.2 in the example
    var threshold = spreadsheet.getSheetByName('Backlog').getRange(1, 3).getDisplayValue();
    if(value == threshold) {
      var row = range.getRow();
      var rowData = sheet.getRange(row, 2, 1, 13).getValues();

      // append the row values except column A and E
      spreadsheet.getSheetByName('Nej och Ej Akt').appendRow(rowData[0].filter((x, i) => i != 3));

      // clear B:D and F:N of that row
      sheet.getRangeList([`B${row}:D${row}`, `F${row}:N${row}`]).clearContent();
    }
  }
}

Sample Data:

sample

Output (A):

A

Output (Nej och Ej Akt):

Nej

Note:

  • There are some unclear details from your post so I assumed some of them (All of them have comments above). Feel free to note it below if I was mistaken for my assumptions.

Assumptions:

  1. 'Backlog'!C1 contains a decimal value BUT shows only the whole number. Instead of getting the real value (e.g. 6.2), what I did was to get the displayed value instead (e.g. 6).
  2. Legend above shows that green colored cells are to be copied. Given that column E is not green, I excluded it from copying and clearing. But if you want E to be also copied to 'Nej och Ej Akt' and cleared afterwards, then just use the script below instead.

Modifications:

function onEdit(e) {
  var spreadsheet = e.source;
  var sheet = spreadsheet.getActiveSheet();
  var range = e.range;

  if(sheet.getSheetName() == 'A' && range.getColumn() == 5) {
    var value = e.value;
    // used getDisplayValue since it shows 6 but actual value is 6.2 in the example
    var threshold = spreadsheet.getSheetByName('Backlog').getRange(1, 3).getDisplayValue();
    if(value == threshold) {
      var row = range.getRow();
      var rowData = sheet.getRange(row, 2, 1, 13).getValues();

      // include E to be copied over
      spreadsheet.getSheetByName('Nej och Ej Akt').appendRow(rowData[0]);

      // include E to be cleared
      sheet.getRange(`B${row}:N${row}`).clearContent();
    }
  }
}

Output (A):

A

Output (Nej och Ej Akt):

Nej

user_161186860
Related