谷歌脚本将特定的列从一张纸复制到另一张最后一行onEdit [英] Google Script to copy specific columns from one sheet to another sheets last row onEdit

问题描述

我正在寻找一些关于谷歌应用程序脚本的帮助。目前我有一个电子表格，有3张。第一张表格是来自Google表单的数据。第二张是打开标签，第三张是关闭标签。

当数据从表单进入电子表格时，它只有表格所具有的列。 打开工作表包含表单（表单1）中的数据和一些其他单元格的组合，以便我添加更多信息，而不是提交表单的人员。由于这个列错误匹配，我不能将整行从表1复制到表2.这也需要在编辑触发器上完成。因此，当编辑触发器触发时，我想将行数据存储到内存中，然后将列B复制到标签2的列C的最后一行。列C> E，列D> B等。

如何将整行存储到内存中（我假设的一个数组），然后仅将特定单元格按特定顺序复制到不同工作表的最后一行？

希望有道理： - /

解决方案

正如你所说，你会必须使用数组来获取数据，并按照您选择的顺序将其写回另一个工作表。
这是非常基本的操作，有很多方法来实现它。
一种很容易理解的方法如下：
$ b $ ul

获取数组中的行数据

逐个挑选每个值并存储在另一个数组中

回写到另一个表中的最后一行+1。

ul>

请注意，数组的索引从0开始，行和列从1开始，所以您必须进行一些数学运算！

 函数copyRow（）{
 var ss = SpreadsheetApp.getActive（）; 
 var sheet = ss.getActiveSheet（）; 
 var rowIdx = sheet.getActiveRange（）。getRowIndex（）; 
 var rowValues = sheet.getRange（rowIdx，1,1，sheet.getLastRow（））。getValues（）; 
 Logger.log（rowValues）; 
 var destValues = []; 
 destValues.push（rowValues [0] [0]）; //将数据从col A复制到col A 
 destValues.push（rowValues [0] [2]）; //从col拷贝数据C到col B 
 destValues.push（rowValues [0] [1]）; //将数据从col B复制到col C 
 destValues.push（rowValues [0] [3]）; //将数据从col D复制到col D 
 //继续按照您的要求
 var dest = ss.getSheets（）[1]; //继续选择工作表（这里是第二个工作表）
 dest.getRange（dest.getLastRow（）+ 1,1,1，destValues.length）.setValues（[destValues]）; //使用正确的顺序更新目标工作表和所选值，括号用于构建2D数组需要写入范围
} 
  

如果您希望该函数在编辑时运行然后调用它 onEdit（），就完成了！

I am looking for some help on google app scripting. Currently I have a spreadsheet that has 3 sheets. First sheet is data that comes from a google form. Second sheet is an "open" tab and third is a "closed" tab.

When data comes into the spreadsheet from the form it has only the columns that the form has. The "open" sheet has a combination of data from the form (sheet 1) and some additional cells to add more information to by me and not the person submitting the form. Because of this column miss-match I cannot just copy a whole row from sheet 1 to sheet 2. This also needs to be done onEdit trigger. So when an edit trigger fires, I want to store that rows data into memory and then copy column B to the last row of tab 2, column C. Column C > E, column D > B. etc..

How would I store the whole row into memory (an array I presume), and then copy only specific cells, in a specific order into the last row of a different sheet?

Hope that makes sense :-/

解决方案

As you said, you'll have to use arrays to get data and write it back to another sheet in your selected order. This is pretty basic manipulation and there are many ways to achieve it. One way that is very easy to understand is as follow :

• get row data in an array
• pick up each value one by one and store in another array
• write back to the last row +1 on another sheet.

Note that arrays are indexed starting from 0 , rows and columns start from 1 and A so you'll have to do some math !

function copyRow(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var rowIdx = sheet.getActiveRange().getRowIndex();
  var rowValues = sheet.getRange(rowIdx,1,1,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][2]);// copy data from col C to col B
  destValues.push(rowValues[0][1]);// copy data from col B to col C
  destValues.push(rowValues[0][3]);// copy data from col D to col D
  // continue as you want
  var dest = ss.getSheets()[1];//go on chosen sheet (here is the 2cond one)
  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
}

If you want that function to run on edit then just call it onEdit() and you're done !

这篇关于谷歌脚本将特定的列从一张纸复制到另一张最后一行onEdit的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！