将数据Sheet1复制到Sheet2，以便您可以对数据Sheet1进行排序&编辑两张表（谷歌应用脚本？） [英] Copying Data Sheet1 to Sheet2 so you can sort & edit both sheets (google apps script?)
问题描述
我在护目镜工作表中工作，认为我需要使用谷歌应用程序脚本来做我想做的事，但我是一家非营利性大学医院的心理医生，试图做一些好事而不是程序员（可能会显示），我迫切需要帮助。我试图建立一系列的电子表格来跟踪参加我们治疗方法的研讨会。
1）我有一张表单Participant_Registration，其中输入了基本信息
2）我想将信息仅从Participant_Registration的前四列（A：D）传输到第二张表Learning_Sessions_Attendance
3）我也将相同的信息传递给第三张表单Consultation1_Attendance - 但我需要先筛选并选择那些分配给该组的人。
以下是我的电子表格副本的链接。
https://docs.google.com/spreadsheets/d / 17d0bT4LZOx5cyjSUHPRFgEZTz4y1yEL_tO3gtSJ4UJ8 /编辑？usp =共享
更一般地说，这就是我想要做的。这可能在谷歌应用程序脚本？它看起来应该是这样的。
1）我有sheet1中的原始数据2）我想要第一个四列（A：D）转移到工作表2（如果我需要一个触发器变量，这是很好的）
3）我希望他们以这样的方式转移，如果4）理想情况下，如果源表中的数据发生了变化（如果数据表中没有数据，则数据仍然正常）（仍然链接到正确的行）。
Sheet1）在Sheet2中将进行相同的更改。
5）理想情况下，这一切都将自动发生，无需人工干预。
任何想法？我非常需要你的帮助。我已经遍布论坛，git hub，并且做了大量的搜索，并尝试了很多我看到的例子，但没有任何作用。我真的需要帮助。
下面是我的示例脚本，每个脚本都有一个问题：
//下面的代码按照我的意愿复制从sheet1到sheet2的范围。如果在从sheet1复制数据之后发生问题，我们将数据添加到sheet2上的其他列。如果我们再次部署某个变量（如果人们必须这样做），那么它将覆盖数据，意味着来自sheet1的数据没有连接到sheet2上的正确个体。
函数CopyRange（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = ss.getSheetByName（Participant_Registration）;
var range = sheet.getRange（A14：D）;
var values = range.getValues（）;
var target = ss.getSheetByName（Learning_Sessions_Attendance）;
var target_range = target.getRange（A10：D）;
range.copyTo（target_range）;
}
所以我又试了一次。这次我试图将最后编辑的行从sheet1复制到sheet2。这个函数似乎不适合我。
函数CopyRow2（）{
// Get Spreadsheets
var source = SpreadsheetApp.openById（1egn6pnRd6mKMGuQxX_jtgwYDtkuMUv2QJItLdh7aIEs）;
var target = SpreadsheetApp.openById（1egn6pnRd6mKMGuQxX_jtgwYDtkuMUv2QJItLdh7aIEs）;
//设置表格
var source_sheet = source.getSheetByName（Participant_Registration）;
var target_sheet = target.getSheetByName（Learning_Sessions_Attendance）;
var rowIdx = source_sheet.getActiveRange（）。getRowIndex（）;
var rowValues = source_sheet.getRange（rowIdx，1,1，source_sheet.getLastRow（））。getValues（）;
Logger.log（rowValues）;
var destValues = [];
destValues.push（rowValues [0] [0]）; //将数据从col A复制到col A
destValues.push（rowValues [0] [1]）; //从col中复制数据B到col B
destValues.push（rowValues [0] [2]）; //将数据从col C复制到col C
destValues.push（rowValues [0] [3]）; //将数据从col D复制到col D
var dest = source.getSheets（）[4];
dest.getRange（dest.getLastRow（）+ 1,1,1，destValues.length）.setValues（[destValues]）; //使用正确的顺序更新目标工作表的选定值，方括号在那里建立需要写入范围的二维数组
}
所以我尝试了一次又一次。我有很多的例子，但似乎没有工作。
非常感谢。
Chandra
为了自动发生这种情况（一张纸更换另一张纸），您肯定需要一个事件/触发器以在您更改单元格时运行脚本。 （即onEdit（）函数）。不过，由于脚本有时可能会失败（即使它们完美无缺，这是由于Google的一些问题），但并不保证表单总是包含相同的数据。
但是，如果我能以另一种方式提出建议，不要让ID成为可选项。如果这是一个真实的ID（如个人身份证号码），请专门创建另一个ID用于处理表单。
我编辑了第二张表，显示了如何在不使用脚本的情况下提供建议。您唯一需要注意的是：
VLOOKUP forumla将在第一张表格中搜索该ID并返回数据在同一行。你可以用任何你喜欢的方式对任何表格进行排序。只要你不改变人的身份证。因此，在图表2中，在名字，姓氏和电子邮件地址：
= vlookup（ A10，Participant_Registration！$ A：$ D，2，false）
= vlookup（A10，Participant_Registration！$ A：$ D，3，false）
= vlookup（A10，Participant_Registration！$ A：$ D，4，false）
只需将此公式向下延伸
我希望这有助于。我会不惜一切代价避免编写脚本。这将是我的最后一招。 （如果你想重新排列你的工作表，脚本也需要改变，如果没有，他们可能会导致麻烦，写现有的数据......）
$ hr
我还添加了一个按钮（插入 - 绘图），并在其中放置了一个脚本（右键，点击向下箭头，transfer？script - 由葡萄牙语翻译）。
如果您锁定了工作表2中的所有四列并锁定了工作表1中的ID列，那么用户将无法改变ID并导致混乱。他们可以在表格1中编辑人员，而不是在表格2中更改公式。脚本不受排序或空白空间的影响（它将人员添加到找到的第一个空行中）。
我为四列标题添加了命名范围。 （使用命名范围，脚本可以引用名称而不是坐标，这使您可以重新排列工作表的插入和删除列，或者使用 CUT 和粘贴来移动它们，但如果重新排列列，VLOOKUP 公式将需要手动更新）。
以下是代码:(如果您设法创建对话框并在该对话框中询问个人数据，那么它可以变得更好，那么您可以锁定所有内容 - 而且您除了添加之外，还需要一个编辑按钮）。
function AddPerson（）
{
var S1Name =Participant_Registration;
var S2Name =Learning_Sessions_Attendance;
var ID1Name =regID;
var ID2Name =learnID;
//这些变量不用于这个脚本
var FN1Name =regFirstName;
var FN2Name =learnFirstName;
var LN1Name =regLastName;
var LN2Name =learnLastName;
var Email1Name =regEmail;
var Email2Name =learnEmail;
var sSheet = SpreadsheetApp.getActiveSpreadsheet（）;
var Sheet1 = sSheet.getSheetByName（S1Name）;
var Sheet2 = sSheet.getSheetByName（S2Name）;
var ID1 = getRangeByName（sSheet，Sheet1.getName（），ID1Name）;
var ID2 = getRangeByName（sSheet，Sheet2.getName（），ID2Name）; Logger.log（ID2：+ ID2.getValue（））;
var Empty1 = getFirstEmpty（ID1）;
var Empty2 = getFirstEmpty（ID2）;
var Biggest1 = getBiggestID（ID1）; Logger.log（Biggest 1：+ Biggest1）;
var Biggest2 = getBiggestID（ID2）; Logger.log（Biggest 2：+ Biggest2）;
if（Biggest1！== Biggest2）
Browser.msgBox（警告：一张表中有ID不在另一张表中）） ;
var最大;
if（Biggest1> Biggest2）Biggest = Biggest1;
else Biggest = Biggest2;
最大++;
Empty1.setValue（Biggest）;
Empty2.setValue（Biggest）;
}
函数getFirstEmpty（Header）
{
while（Header.getValue（）！==）
{
Header = Header.offset（1,0）;
}
返回标题;
}
函数getBiggestID（Header）
{
var Sheet = Header.getSheet（）;
var LastRow = Sheet.getLastRow（）;
var Values = Sheet.getRow（），Header.getColumn（），LastRow - Header.getRow（）+ 1）.getValues（）;
var len = Values.length;
var MaxID = 1;
for（var i = 0; i< len; i ++）
{
var val = Number（Values [i]）;
if（！isNaN（val）&& val> MaxID）
MaxID = val;
}
返回MaxID;
$ b $函数getRangeByName（spreadSheet，sheetName，rangeName）
{
Logger.log（Trying range：+'+ sheetName +'！+ rangeName）;
return spreadSheet.getRangeByName（'+ sheetName +'！+ rangeName）;
}
I am working in goggle sheets and think I need to use a google apps script to do what I want, but I am a psychologist at a non-profit University hospital trying to do some good and not a programmer (which probably shows) and I am desperately in need of help. I am trying to set up a series of spreadsheets to track participation in workshops for our treatment method.
1) I have a sheet "Participant_Registration" where basic information is entered
2) I want to transfer information from only the first four columns (A:D) of "Participant_Registration" to a second sheet "Learning_Sessions_Attendance"
3) I am also transferring the same information to a third sheet 'Consultation1_Attendance' – but I need to first filter and select only those people assigned to that group.
Here is a link to a copy of my spreadsheet. https://docs.google.com/spreadsheets/d/17d0bT4LZOx5cyjSUHPRFgEZTz4y1yEL_tO3gtSJ4UJ8/edit?usp=sharing
More generically this is what I am trying to do. Is this possible in google app scripts? It seems it should be.
1) I have original data in sheet1
2) I want the first four columns (A:D) to transfer to sheet2 (it is fine if I need a trigger variable)
3) I want them to transfer in such a way that if you sort either sheet, the data are still fine (still linked to the right line).
4) Ideally if there is a change to the data in the source sheet (Sheet1) the same change will be made in Sheet2.
5) Ideally this would all happen automatically without human intervention through a script.
Any ideas?? I so need your help. I have been all over the forum, git hub, and done a ton of searches and tried following a lot of examples I saw but nothing works. I really need help.
Here are my sample scripts each with a problem:
//The following code copies a range from sheet1 to sheet2 as I wanted. A problem occurs if after if we copy the data from sheet1 we add data to other columns on sheet2. Later if we sort on some variable (which people are bound to do) if the function is deployed again it will overwrite data meaning the data from sheet1 are not connected to the right individual on sheet2
function CopyRange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Participant_Registration");
var range = sheet.getRange("A14:D");
var values = range.getValues();
var target = ss.getSheetByName("Learning_Sessions_Attendance");
var target_range = target.getRange("A10:D");
range.copyTo(target_range);
}
So I tried again. This time I tried to just copy the last edited row from sheet1 to sheet2. This function does not appear to work for me.
function CopyRow2() {
// Get Spreadsheets
var source = SpreadsheetApp.openById("1egn6pnRd6mKMGuQxX_jtgwYDtkuMUv2QJItLdh7aIEs");
var target = SpreadsheetApp.openById("1egn6pnRd6mKMGuQxX_jtgwYDtkuMUv2QJItLdh7aIEs");
// Set Sheets
var source_sheet = source.getSheetByName("Participant_Registration");
var target_sheet = target.getSheetByName("Learning_Sessions_Attendance");
var rowIdx = source_sheet.getActiveRange().getRowIndex();
var rowValues = source_sheet.getRange(rowIdx,1,1,source_sheet.getLastRow()).getValues();
Logger.log(rowValues);
var destValues = [];
destValues.push(rowValues[0][0]);// copy data from col A to col A
destValues.push(rowValues[0][1]);//copy data from col B to col B
destValues.push(rowValues[0][2]);//copy data from col C to col C
destValues.push(rowValues[0][3]);//copy data from col D to col D
var dest=source.getSheets()[4];
dest.getRange(dest.getLastRow()+1,1,1,destValues.length).setValues([destValues]);//update destination sheet with selected values in the right order, the brackets are there to build the 2D array needed to write to a range
}
So I tried again and again. I have lots of examples but none seem to work.
Thanks so much. Chandra
For that to happen automatically (one sheet's change updating another sheet), you will surely need an "event/trigger" to run a script whenever you change a cell. (that is the "onEdit()" function).
But since scripts are likely to fail sometimes (even when they are perfect, that's because of some Google issues), it's not guaranteed that the sheets will always contain the same data.
But, if I could suggest another way, do not let ID be optional. If that is a real ID (like the person ID card number), create another ID exclusively for working with the sheet.
I have edited your second sheet showing a suggestion of how to do it without using scripts. The only things you must be aware of are:
- Do not create two people with the same ID.
- You have to insert (only) the ID manually in the second sheet.
The VLOOKUP forumla will search for that ID in the first sheet and return the data in the same line. You can sort any sheet in whatever way you like. As long as you don't change people's IDs.
So, in sheet 2, use this in the First Name, Last Name and Email address:
=vlookup(A10,Participant_Registration!$A:$D,2,false)
=vlookup(A10,Participant_Registration!$A:$D,3,false)
=vlookup(A10,Participant_Registration!$A:$D,4,false)
Just extend this formula downwards
I hope this helps. I would avoid scripting for that at any cost. It would be my last resort. (Scripts also need to be changed if you want to rearrange your sheet, and if not, they might cause trouble, write over existing data...)
I also added a button (insert - drawing) and put a script in it (right button, click down arrow, "transfer? script" -- translated from Portuguese).
If you lock all four columns in sheet2 and lock the ID column in sheet 1, people will not be able to chang IDs and cause mess. They can edit people in sheet 1 and not change the formula in sheet2. Script is not affected by sorting or empty spaces (it adds the person in the first empty row it finds).
I added "named ranges" for the four column headers. (With named ranges, the script can refer to names instead of coordinates, which enables you to rearrange the sheet inserting and deleting columns, or moving them with CUT and paste - but the VLOOKUP formula will need manual update if you rearrange columns).
Here is the code: (it could get better if you manage to create dialog boxes and ask for the person's data inside that dialog, then you could lock everything - and you would need an edit button besides the add).
function AddPerson()
{
var S1Name = "Participant_Registration";
var S2Name = "Learning_Sessions_Attendance";
var ID1Name = "regID";
var ID2Name = "learnID";
//these vars are not used in this script
var FN1Name = "regFirstName";
var FN2Name = "learnFirstName";
var LN1Name = "regLastName";
var LN2Name = "learnLastName";
var Email1Name = "regEmail";
var Email2Name = "learnEmail";
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
var Sheet1 = sSheet.getSheetByName(S1Name);
var Sheet2 = sSheet.getSheetByName(S2Name);
var ID1 = getRangeByName(sSheet, Sheet1.getName(), ID1Name);
var ID2 = getRangeByName(sSheet, Sheet2.getName(), ID2Name); Logger.log("ID2: " + ID2.getValue());
var Empty1 = getFirstEmpty(ID1);
var Empty2 = getFirstEmpty(ID2);
var Biggest1 = getBiggestID(ID1); Logger.log("Biggest 1: " + Biggest1);
var Biggest2 = getBiggestID(ID2); Logger.log("Biggest 2: " + Biggest2);
if (Biggest1 !== Biggest2)
Browser.msgBox("Warning: there are IDs in one sheet that are not in the other sheet");
var Biggest;
if (Biggest1 > Biggest2) Biggest = Biggest1;
else Biggest = Biggest2;
Biggest++;
Empty1.setValue(Biggest);
Empty2.setValue(Biggest);
}
function getFirstEmpty(Header)
{
while (Header.getValue() !== "")
{
Header = Header.offset(1,0);
}
return Header;
}
function getBiggestID(Header)
{
var Sheet = Header.getSheet();
var LastRow = Sheet.getLastRow();
var Values = Sheet.getRange(Header.getRow(), Header.getColumn(), LastRow - Header.getRow() + 1).getValues();
var len = Values.length;
var MaxID = 1;
for (var i = 0; i < len; i++)
{
var val = Number(Values[i]);
if (!isNaN(val) && val > MaxID)
MaxID = val;
}
return MaxID;
}
function getRangeByName(spreadSheet, sheetName, rangeName)
{
Logger.log("Trying range: " + "'" + sheetName + "'!" + rangeName);
return spreadSheet.getRangeByName("'" + sheetName + "'!" + rangeName);
}
这篇关于将数据Sheet1复制到Sheet2，以便您可以对数据Sheet1进行排序&编辑两张表（谷歌应用脚本？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
