Google表格onEdit-尝试通过更改同一行中列的值来将行复制到新表中 [英] Google Sheets onEdit - trying to copy a row to a new sheet by changing the value of a column within that same row

问题描述

请参阅我的电子表格的Sheet1:

Please see Sheet1 of my spreadsheet:

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

作为一个例子，我想通过将H3中的值更改为"submitResponse"，将A3:G3的值(而非公式)复制到主"表的底部.然后，一旦复制了该行，我想自动清除单元格H3以及范围A2:G2(该行位于所复制的行上方)，因为这些是用户可以修改以在之前调整A3:G3的变量它会被复制.

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.

为提供帮助，以下是@Cooper为我构建的脚本，用于将我推向正确的方向:

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]);
  }
}

来自上一个问题:更新:

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(); 
  } 
 }

更新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();
  }
}

推荐答案

解决方案

使用Update 2，您真的可以解决这个问题.但是，您有错字，请替换:

Solution

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

rowAbove.ClearContent();

使用

rowAbove.clearContent();

我希望这对您有所帮助.让我知道您是否需要其他任何东西，或者您是否不了解某些内容. :)

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

这篇关于Google表格onEdit-尝试通过更改同一行中列的值来将行复制到新表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！