Google Sheets Script -> How to copy row as values into another tab based on a cell trigger and then clear the trigger cell?

Question

this is my very first attempt at programming period so I apologize for what may come across as a simple question to you experienced folks. I'm working through Google Sheets scripting for this.

ISSUE: I have a file with multiple tabs, the key tabs with this question are marked as "submit" and "db". Within the "submit" tab I will have information (including formulas) in many rows from columns A to Y. When I enter "run" in column Z for a particular row I would like the information in that row only from columns A to Y to copy and paste as values to the next available row in the "db" tab. Once this is complete I would like the original trigger cell to be deleted (not cleared as I have a border around it) and the row above it to be deleted from columns A to Y as well (not cleared as I have borders and data validation)

I hope this makes sense.

Below is the code I've been using. I have been able to successfully move the information from the "submit" tab to the "db" tab but that has been about it. I've been playing with the copyValuesOnly, moveValuesOnly, clearContent, getFormulas/setFormulas however I simply cannot crack the code.

Thank you in advance!

function onEdit(event) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "submit" && r.getColumn() == 26 && r.getValue() == "run") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("db");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,numColumns);

s.getRange(row, 1, 1, numColumns).moveTo(target);
s.range.clearContent();

}
}

EDIT:

I've included a link to the sheet below, hopefully I provided the correct rights to it. As an example of what I want to do is if I were to select "run" in cell Z12 of the "submit" tab it would do the following:

copy the information from A12 to Y12 and paste as values to the "db" tab cells A2 to Y2 (as it's the next available row, once this copy is complete...
cell Z12 on the "submit" tab (trigger cell) would be would be deleted (border and data validation should remain)
the above row from cells D11 to Y11 should be deleted (border and data validations intact)

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

Ron M Ron M · Accepted Answer · 2021-02-05T21:54:40

Use this:

function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "submit" && r.getColumn() == 26 && r.getValue() == "run") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("db");
    var target = targetSheet.getRange(targetSheet.getLastRow()+1,1);
    s.getRange(row, 1, 1, numColumns).copyTo(target,{contentsOnly:true});

    //Clear column Z content
    r.clearContent();

    //Clear previous row Column D-Y
    s.getRange(row-1,4,1,22).clearContent();

  }
}

Explanation:

The reason why Sheet "submit" row 12 format and content was cleared is because you are using Range.moveTo(target) to copy the content to sheet "db". moveTo() cuts and pastes (both format and values) from this range to the target range.

To fix this, you can use Range.copyTo(destination, options)
To clear content of the modified cell (retain data validation and format), use clearContent(). Hence to clear the content of modified cell in column Z, use r.clearContent();
To clear the content of the previous row from column D-Y, get its range first then use clearContent(). Done using s.getRange(row-1,4,1,22).clearContent();

Output:

(Before)

(After)

Notice that the formula is still available in row 12

Google Sheets Script -> How to copy row as values into another tab based on a cell trigger and then clear the trigger cell?

2 Answers