如果不是基于多个单元格中的条件进行重复，则将单元格复制到另一个表单 [英] Copy Cell one sheet to another if not a duplicate based on criteria in multiple cells

问题描述

这是前一篇文章的后续内容，但我无法弄清楚如何将它添加到之前的文章中。这里是前一篇文章的链接。
将Data Sheet1复制到Sheet2，以便您可以对&编辑两张表（谷歌应用脚​​本？）

第二部分
感谢Daniel，
我有以下
在工作表1（Participant_Registration）上有一个按钮，当按下时，它将一个ID添加到工作表1和工作表2（Learning_Sessions_Attendance）。
然后，我们使用vlookup函数将sheet1中的列BD转移到sheet2
中。我写了自己的脚本为参与者添加指令。

现在在这里是我现在需要帮助的东西。
对原始脚本的小修复
这个脚本虽然很棒，但它开始将ID的编号设置为2而不是1。有什么方法可以解决这个问题？

新脚本
在表1（Participant_Registration）中，如果列L，M或N的值为Group1，我希望将ID（在列A中）转移到sheet3（Consultation1_Attendance）。
如果L，M或N的值为Group2，我希望（列A）中的ID转移到Sheet4（Consultation2_Attendance）。

以下是新脚本的注意事项，这些脚本使我很难编写它。
在培训过程中，人们可能会更改组，这意味着该功能可能需要onEdit功能，并且不能传输最后一行。如果身份证尚未在该表格中，则需要转移活动行并仅将其转交给咨询考勤表。
我尝试了查询公式 - 不起作用，因为您无法对咨询出席表中的行进行排序，并且我们正在向该表中添加新数据。

注意：最终我会玩这个剧本，有10个可能的咨询小组。我不知道是否有办法自动将工作表Consultation1_Attendance复制到组数中，并将其填入分配给该组的人员。

这里是指向表单的链接。
https://docs.google.com/电子表格/ d / 1cvKO2tPKeZdLWHc2XVfli8lYVylXCas4VgSG-wB_lLM / edit？usp =共享

code.gs下的代码运行。我在代码中的尝试在ConsultGpAssign.gs下（他们非常难过）。我只是在学习。我一直在尝试各种各样的事情，并且可以真正使用帮助。

我也粘贴了一个显然不适用的尝试。

 函数onEdit（event）{
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var s = event.source.getActiveSheet（）; 
 var r = event.source.getActiveRange（）; （s.getName（）==Participant_Registration&&（（r.getColumn（）== 12&& r.getValue（）==Group1）| 
 
 | |（r.getColumn（）== 13&& r.getValue（）==Group1）||（r.getColumn（）== 14&& r.getValue（）==Group1 ）））{
 var row = r.getRow（）; //不知道这是否工作
 
 //只想传递ID列A不知道该怎么做
 var numColumns = s.getLastColumn（）; 
 var targetSheet = ss.getSheetByName（Consultation1_Attendance）; 
 var target = targetSheet.getRange（targetSheet.getLastRow（）+ 1，1）; 
 s.getRange（row，1，1，numColumns）.moveTo（target）; //只有在信息不在表单上时才传输信息。不想删除
} 
} 
  

解决方案

  s.getRange（row，1，1，numColumns）.moveTo（target） ; //只有在信息不在表单上时才传输信息。不想删除
  

moveTo（）方法削减范围。但是你的评论指出你不想删除。切割范围将删除它。您已经定义了目标范围。使用 getValues（）来获得您想要复制的内容：

  var myValuesToCopy = s.getRange（row，1，1，numColumns）.getValues（）; 
 target.setValues（myValuesToCopy）; 
  

This is a follow-up to a previous post, but I could not figure out how to add it to the prior post. Here is the link to the prior post. Copying Data Sheet1 to Sheet2 so you can sort & edit both sheets (google apps script?)

PART II Thanks to Daniel, I have the following On sheet1 (Participant_Registration) there is a button that when pushed adds an ID both to sheet1 and to sheet2 (Learning_Sessions_Attendance). We then used the vlookup function to transfer columns B-D from sheet1 to sheet2 I wrote my own script to add instructions for participants.

Now here is what I need help with now. Small fix to original script The script, while fantastic, starts numbering ID’s at 2 instead of at 1. Is there any way to fix this?

New script On sheet1 (Participant_Registration) if Column L, M, or N have a value = "Group1" I would like the ID (in column A) transferred to sheet3 (Consultation1_Attendance). If L, M, or N have a value = "Group2" I would like the ID in (column A) transferred to Sheet4 (Consultation2_Attendance).

Here are the considerations for the new script, which have made it hard for me to write it. During the course of a training, people may change groups, which means the function needs to likely be an onEdit function and cannot just transfer the last row. It needs to transfer the active row and to only transfer it to the consultation attendance form if the ID is not already on that form. I tried the Query formula - does not work as then you cannot sort the rows in the consultation attendance sheet, and we are adding new data to that sheet.

Note: Eventually I will play with this script to have 10 possible consultation groups. I’m not sure if there is a way to automate copying the sheet Consultation1_Attendance by the number of groups and populating it with people who are assigned to the group.

Here is a link to the sheet. https://docs.google.com/spreadsheets/d/1cvKO2tPKeZdLWHc2XVfli8lYVylXCas4VgSG-wB_lLM/edit?usp=sharing

The code under code.gs runs. My attempts at the code for this are under ConsultGpAssign.gs (they are very sad attempts). I am just learning. I have been trying all kinds of things and could really use help.

I also pasted one attempt that is clearly not working here.

function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Participant_Registration" && ((r.getColumn() == 12 &&     r.getValue() == "Group1") || (r.getColumn() == 13 && r.getValue() == "Group1") || (r.getColumn() == 14 && r.getValue() == "Group1"))) {
    var row = r.getRow();  //Not sure if this works

    //only want to transfer ID column A not sure how to do this
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Consultation1_Attendance");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target); //Only want to  transfer information if it's not already on sheet. Do not want to delete
  }
}

解决方案

Looking at this line:

s.getRange(row, 1, 1, numColumns).moveTo(target); //Only want to  transfer information if it's not already on sheet. Do not want to delete

The moveTo() method cuts the range. But your comment states that you don't want to delete. Cutting the range will delete it. You already have a target range defined. Use getValues() to get what you want to copy:

var myValuesToCopy = s.getRange(row, 1, 1, numColumns).getValues();
target.setValues(myValuesToCopy);

这篇关于如果不是基于多个单元格中的条件进行重复，则将单元格复制到另一个表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！