如何使用onChange触发器确定在工作表中插入的行数? [英] How to determine the amount of rows inserted in a sheet with onChange trigger?

问题描述

我想将一些数据从电子表格复制到另一个电子表格.第一列通常在大小上有所变化，第二列从第一列开始有几列，然后其他一些列都有自己的数据.

I want to replicate some data from a spreadsheet to another. The first is often changing in size and the second one has a few columns from the first and then some other columns with it's own data.

我想根据第二行中插入或删除的行在第二行中插入/删除行.它们必须匹配先前的数据.

I'd like to insert/delete rows in the second one depending on the rows inserted or deleted in the first one. They have to match the previous data.

我尝试使用onChange触发器来检测何时检测到INSERT_ROW或REMOVE_ROW changeType.当仅插入一行时，它工作正常，但当它们插入多个时，效果不佳.同样，当他们插入一行并且撤消之后，动作changeType是EDIT而不是REMOVE_ROW.失败.

I've tried to use an onChange trigger to detect when an INSERT_ROW or REMOVE_ROW changeType was detected. It works fine when it's just a row inserted, but not when they insert more than one. Also, when they insert a row and after they undo the action changeType is EDIT and not REMOVE_ROW. Fails.

然后，我决定在每行中添加一个具有索引编号的隐藏列，因此，如果第5行和第6行之间的数字之间存在空格，这意味着我必须在第5行之后的第二张表中插入3行，然后我重建索引以检查进一步的更改...如果删除了几行，它也可以工作.

Then I decided to add a hidden column with an index number in each row, so if there is a gap of numbers between row 5 and 6 it means that I have to insert 3 rows in the second sheet after the 5th row, then I rebuilt the index to check further changes... It also works if several rows have been removed.

在我意识到用户可以复制行或复制/粘贴整行然后使用新信息修改数据之后，该方法似乎工作正常，但是索引也会被复制，并且当我检查差异以检查是否存在任何差异时行已被删除，然后失败.

That way seemed to work fine until I realised that the users can duplicate the rows or copy/paste the full row and then modify the data with new info BUT the index is also copied and when I check the differnce to check if any row has been removed, then it fails.

第一次尝试:

  if (event.changeType == 'INSERT_ROW' && tab =='Hoja 1') {
    for (var i = 0 ; i < indexes.length ; i++) {
      if (indexes[i] =='') {
        destSheet.insertRowAfter(i+1);
      }
    }
  }
  if (event.changeType == 'REMOVE_ROW' && tab =='Hoja 1') {
    for (var i = 0 ; i < indexes.length ; i++) {
      if (indexes[i]-indexes[i+1] < -1 && indexes[i] != 0) {
        if (indexes[i] != lastRow) {
          destSheet.deleteRows(i+3,(indexes[i]-indexes[i+1])*-1-1)
        }
      }
    }
  }
  for (var j = 0; j < lastRow-1; j++) {
    indexs.getCell(j+1, 1).setValue(j+1);
  }
}

第二次尝试:

function checkLines (sheet, destSheet)  {
  for (var i = 0 ; i < indexes.length ; i++) {
    if (indexes[i] =='') {
      destSheet.insertRowAfter(i+1);
    }
    if (indexes[i]-indexes[i+1] < -1 && indexes[i] != 0) {
      if (indexes[i] != lastRow) {
        destSheet.deleteRows(i+3,(indexes[i]-indexes[i+1])*-1-1)
      }
    }
  }
}

我想在目标工作表中复制源工作表中选定列的副本，如果第一行更改了它的行，则第二行中应该也是如此.

I'd like to have a copy of the selected columns of the source sheet in a destination sheet and if the first one changes its rows the same should happen in the second one.

在某些情况下，目的地并没有被确定，因此，在下面的列中写入的信息与在第一列中的信息不匹配.

In some cases the destination doesn't get actualised and then the information written in the following column doesn't match with the info of the first columns.

以下是到源工作表示例的链接: https://docs.google.com/spreadsheets/d/19OnWJZQ7Qedit?usp = sharing

Here is an link to an example of the source sheet: https://docs.google.com/spreadsheets/d/19OnwKIEm2OFymjsjqeoQYWcA9BNAJTM0ap2rdpQlZoQ/edit?usp=sharing

这里是目的地: https://docs.google.com/spreadsheets/d/10vbMSqQJ1edit?usp = sharing

我将不胜感激任何建议...并且，如果您需要进一步的说明，请告诉我...英语不是我的母语，并且我不确定您是否理解我的问题.

I'll appreciate any suggestion... And please let me know if you need further explanations... english is not my native language and I'm not sure if you understand my problem.

非常感谢您.

V.

推荐答案

有效范围

The active range¹ has the number of rows inserted.

function onChange(e){
  if (e.changeType === 'INSERT_ROW'){
    const rng = SpreadsheetApp.getActiveRange();
    const row = rng.getRow();//Row where insert row is done
    const lrow = rng.getLastRow();//Last row in the newly active range
    const numRows = 1+lrow-row;//number of rows inserted
  }
}

还要查看添加开发人员元数据 ² 到行，这样当它们移动时，您也许可以识别出来.

Also look at adding developer metadata² to rows, so that when they're moved, you might be able to recognize it.

我想在目标工作表中复制源工作表中选定列的副本，如果第一行更改了它的行，则第二行中应该也是如此.

I'd like to have a copy of the selected columns of the source sheet in a destination sheet and if the first one changes its rows the same should happen in the second one.

最简单的方法是获取所有数据并将其粘贴到目标表中( getValues 和 setValues ).

Easiest way is to just get all the data and paste it in the destination sheet(getValues and setValues).

这篇关于如何使用onChange触发器确定在工作表中插入的行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！