修改灵活的Google Sheets onEdit脚本以适应更大范围的输入 [英] Modifying a flexible Google Sheets onEdit script to accommodate a wider range of inputs

问题描述

下面是一个脚本，它使我可以将五个不同的onEdit函数组合为一个函数.

Below is a script that allows me to combine five different onEdit functions into a single function.

function onEdit(e){
  if (e.range.getA1Notation() != "H12" || e.value != "submitResponse") return;
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var m = sh.getSheetByName("Master");
  ss.getRange(2,1,ss.getLastRow(),3).copyTo(m.getRange(m.getLastRow()+1,1,ss.getLastRow(),3)); 
  ss.getRange('H12').clearContent();
  e.range.clearContents();
}

我现在希望使此脚本更加灵活.请参阅我的电子表格的Sheet1: https://docs.google.com/电子表格/d/1EoOIQxWyKWOvtlCrmJNI76FAxGhzgXrE4s0F05tw2MY/edit#gid = 0

I'm now hoping to make this script more flexible. See Sheet1 of my spreadsheet: https://docs.google.com/spreadsheets/d/1EoOIQxWyKWOvtlCrmJNI76FAxGhzgXrE4s0F05tw2MY/edit#gid=0

如果我可以将H:H的整个列用于提交每个对应的行，而不是将自己限制在H12，那就太好了.因此，当我将H2更改为SubmitResponse时，它将A2:G2复制/粘贴到Master的最后一行.当我将H3更改为SubmitResponse时，它将A3:G3复制/粘贴到Master的最后一行.依此类推.

It would be great if instead of limiting myself to H12, I could use the entire column of H:H to submit each corresponding row. So when I change H2 to submitResponse, it copies/pastes A2:G2 to the last row of the Master. When I change H3 to submitResponse, it copies/pastes A3:G3 to the last row of the Master. And so forth.

我为此尝试了一下，但是执行时没有运气.我想我缺少了什么.

I tried my hand at this but no luck on execution. I guess I'm missing something.

​function onEdit(e) {
  var ss = e.source;
  var s = ss.getActiveSheet();
  var r = e.range;
  var actionCol = 8;
  var rowIndex = r.getRowIndex();
  var colIndex = r.getColumnIndex();
  var colNumber = s.getLastColumn()-1;
  if (e.value == "submitResponse" && colIndex == actionCol) ;
    var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
    var targetRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master").getRange(getLastRow()+1, 1, 1, colNumber);
    sourceRange.copyTo(targetRange,SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }

推荐答案

尝试一下:

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 sh=e.range.getSheet();
  if (sh.getName()=='Sheet1' && e.range.columnStart==8 && e.range.rowStart>1 && e.value=="Yes") {
    e.range.setValue('');
    var msh=e.source.getSheetByName("Master");
    msh.appendRow(sh.getRange(e.range.rowStart,1,1,7).getDisplayValues()[0]);
  }
}

这篇关于修改灵活的Google Sheets onEdit脚本以适应更大范围的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！