在工作表之间复制数据的脚本 [英] Script to copy data between sheets

问题描述

我正在尝试创建一个脚本，以将数据从工作表1复制到工作表2，同时对其重新排序.我从Google表单获取数据，因此数据正在不断更新.

I'm trying to create a script to copy data from sheet 1 to sheet 2 and at the same time reorder it. I get my data from a Google form, so data is constantly updating.

这里有两个图像作为示例.N°1是我的数据存储方式，N°2是我希望其数据存储在工作表2中的方式.

Here are two images as examples. N°1 is how I have my data, N°2 is how I want it to be in sheet 2.

这个想法是让脚本在每次出现新行时都复制数据.

The idea is to have the script copying the data every time a new row appears.

来自表单的数据.

这就是我想要的样子.

这是我的初始代码:

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Ingreso'); //source sheet
  var testrange = sheet.getRange('J:J');
  var testvalue = (testrange.getValues());
  var csh = ss.getSheetByName('Auxiliar Ingreso'); //destination sheet
  var data = [];
  var columnasfijas = [];
  var cadena = [];


  //Condition check in G:G; If true copy the same row to data array
for (i=1; i<testvalue.length;i++) {

  data.push.apply(data,sheet.getRange(i+1,1,1,9).getValues());

  if ( testvalue[i] == 'Si') {
   data = (sheet.getRange(i+1,1,1,9).getValues()).concat  (sheet.getRange(i+1,11,1,9).getValues()); // this beaks up into 2 rows Idon't know why
   /*cadena = (columnasfijas);
   data.push.apply(data, columnasfijas);*/
 }

  csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
 }
//Copy data array to destination sheet

 //csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);

}

在这一行中，我在连接不同长度的数据时也遇到了麻烦.它应该是:(i + 1,1,1,6).concat .....(i + 1,11,1,3)

In this line, I'm also having trouble concatenating different lengths of data. It should be: (i+1,1,1,6). concat.....(i+1,11,1,3)

数据=(sheet.getRange(i + 1,1,1,9).getValues()).concat(sheet.getRange(i + 1,11,1,9).getValues());//这会分成两行，我不知道为什么

当我按原样运行它时，我收到一个错误，该长度应为9而不是3.

When I run it as it should by I receive an error that the length should be 9 instead of 3.

推荐答案

使用公式而不是应用程序脚本可以更简单地完成此任务:

This can be accomplished more simply using formulas instead of app scripts:

=sort(importrange("spreadsheetURL", "Sheet1!A2:AA10000"),sort_col#,TRUE/FALSE,[sort_col2#],[TRUE/FALSE]...)

有关导入范围功能的文档: https://support.google.com/docs/answer/3093340 有关排序功能的文档: https://support.google.com/docs/answer/3093150

Documentation on importrange function: https://support.google.com/docs/answer/3093340 Documentation on sort function: https://support.google.com/docs/answer/3093150

输入公式后，单元格上可能会出现红色三角形，请确保单击该单元格，然后单击允许访问"按钮以使一个电子表格可以访问另一个电子表格.

Once you input the formula, there will likely red triangle on the cell, be sure to click on the cell and click the Allow Access button to give one spreadsheet access to the other.

这篇关于在工作表之间复制数据的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！