Google表格-根据特定列将行移动到另一张表格 [英] Google Sheets - Moving row to another sheet based on specific column

问题描述

对于Google表格脚本编辑来说是新手，但是我正在尝试在表格之间移动行.我已经使用getLastRow成功完成了此操作，但是现在我正尝试根据特定的列进行此操作.原因是信息所指向的工作表具有预设的数据验证字段和复选框，从而导致该行每次都移至第1000行.

Very new to the Google Sheets script editing, but I'm trying to move rows between sheets. I've done this successfully using getLastRow however now I'm attempting to do this based on a specific column. The reason for this is that the sheet the info is heading to has preset data validation fields and checkboxes, resulting in the row going to row 1000 every time.

我正在寻找在当前工作表的下拉菜单上选择一个选项时，它将找到基于某一列的第一个空闲行，并替换该行(除预设字段外，该行基本上为空).

I'm looking for when an option is selected on the dropdown menu on the current sheet, it will find the first free row based on a certain column, and replace that row (which is essentially empty except for the preset fields).

目前，我的情况如下:

function onEdit(event) {
  // Move rows to different sheets based on action selected

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = SpreadsheetApp.getActiveRange();

  if(range.getColumn() == 5 && range.getValue() == "Active") {
    var row = range.getRow();
    var numColumns = sheet.getLastColumn();
    var targetSheet = spreadsheet.getSheetByName("Active");
    var target = targetSheet.getRange(targetSheet.getLastRowSpecial(8) + 1, 1);
    sheet.getRange(row, 1, 1, numColumns).moveTo(target);
    sheet.deleteRow(row);

在进行谷歌搜索时，我发现了以下示例和其他示例，但似乎无济于事:

and I found the below and other examples when googling but nothing seems to work:

function getLastRowSpecial(column){
  // Get the last row with data for the whole sheet.
  var numRows = targetSheet.getLastRow();

  // Get all data for the given column
  var data = targetSheet.getRange(1, column, numRows).getValues();

  // Iterate backwards and find first non empty cell
  for(var i = data.length - 1 ; i >= 0 ; i--){
    if (data[i][0] != null && data[i][0] != ""){
      return i + 1;
    }
  }
}

任何帮助将不胜感激！

推荐答案

function onEdit(e) {
  //Logger.log(JSON.stringify(e));
  //e.source.toast(e.range.columnStart, e.value)
  var sh=e.range.getSheet();
  if(e.range.columnStart==8 && e.value=="Active") {
    var tsh=e.source.getSheetByName("Active");
    var target=tsh.getRange(getColumnHeight(8,tsh,e.source) + 1, 1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).moveTo(target);
    sh.deleteRow(e.range.rowStart);
  }
}

function getColumnHeight(col,sh,ss){
  var ss=ss||SpreadsheetApp.getActive();
  var sh=sh||ss.getActiveSheet();
  var col=col||sh.getActiveCell().getColumn();
  var s=0;
  var h=0;
  if(sh.getLastRow()) {
    var v=sh.getRange(1,col,sh.getLastRow(),1).getValues().map(function(r){return r[0];});
    v.forEach(function(e,i){if(e==''){s++;}else{s=0;}h++;});
  }
  return (h-s);
}

这篇关于Google表格-根据特定列将行移动到另一张表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！