Google Sheets onEdit - trying to copy a row to a new sheet by changing the value of a column within that same row

Question

Please see Sheet1 of my spreadsheet:

https://docs.google.com/spreadsheets/d/1EoOIQxWyKWOvtlCrmJNI76FAxGhzgXrE4s0F05tw2MY/edit#gid=0

As an example, I would like to copy the values (not formulas) of A3:G3 to the bottom row of the 'Master' sheet by changing the value in H3 to "submitResponse." Then, once that row is copied, I would like to automatically clear out cell H3, as well as range A2:G2 (the row ABOVE the row that was copied) because these will be variables that the user edits to adjust A3:G3 before it gets copied.

To help, here is a script that @Cooper built for me to push me in the right direction:

function onEdit(e){
  var sh=e.range.getSheet();
  if (sh.getName()=='Sheet1' && e.range.columnStart==8 && e.range.rowStart>1 && e.value=="submitResponse") {
    var msh=e.source.getSheetByName("Master");
    msh.appendRow(sh.getRange(e.range.rowStart,1,1,7).getDisplayValues()[0]);
  }
}

from this previous question: Modifying a flexible Google Sheets onEdit script to accommodate a wider range of inputs

Update:

function onEdit(e){ 
  var done = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('Y:Y'); 
  var sh=e.range.getSheet(); 
  if (sh.getName()=='submit' && e.range.columnStart==25 && e.range.rowStart>1 && e.value=="submit") { 
    var msh=e.source.getSheetByName("db"); 
    msh.appendRow(sh.getRange(e.range.rowStart,1,1,24).getDisplayValues()[0]); 
    done.clearContent(); 
  } 
 }

Update 2:

function onEdit(e){
  var submitColumn = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('Y:Y');
  var rowAbove = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('J11:L11');
  var sh=e.range.getSheet();
  if (sh.getName()=='submit' && e.range.columnStart==25 && e.range.rowStart>1 && e.value=="submit") {
    var msh=e.source.getSheetByName("db");
    msh.appendRow(sh.getRange(e.range.rowStart,1,1,24).getDisplayValues()[0]);
    submitColumn.clearContent();
    rowAbove.ClearContent();
  }
}

And what modificaitons have you triied to accomplish your goals? — Cooper
This is the one I thought would work for the submitResponse cell by appending it to the end: sh.getRange(e.range.rowStart,8,1,1).clearContents(); As far as the specific ranges, I was trying variations of what I had previously but I'm having trouble integrating them with your approach. SpreadsheetApp.getActive().getSheetByName('Sheet1).getRange('A2:G2').clearContents(); — SOtoTheRescue
There is no Range Method clearContents() Look here:developers.google.com/apps-script/reference/spreadsheet/range — Cooper
It's hackey but this worked for the submitResponse cell: 'function onEdit(e){ var done = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('Y:Y'); var sh=e.range.getSheet(); if (sh.getName()=='submit' && e.range.columnStart==25 && e.range.rowStart>1 && e.value=="submit") { var msh=e.source.getSheetByName("db"); msh.appendRow(sh.getRange(e.range.rowStart,1,1,24).getDisplayValues()[0]); done.clearContent(); } }' ... still unsure how to clear the row ABOVE the row that was copied. If it were a fixed row it'd be simple, but that row will vary. — SOtoTheRescue

Mateo Randwolf Mateo Randwolf · Accepted Answer · 2020-05-15T07:36:08

Solution

With Update 2 you were really close to solve this issue. However, you had a typo, replace:

rowAbove.ClearContent();

with

rowAbove.clearContent();

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

Google Sheets onEdit - trying to copy a row to a new sheet by changing the value of a column within that same row

1 Answers

Solution