如何使用 Google App Script 合并 Google 电子表格中的多个标签? [英] How can I merge multiple tabs in a Google Spreadsheet using Google App Script?

问题描述

如何使用 Google App Script 合并 Google 电子表格中的多个标签?所有标签中的数据都在不断变化.

How can I merge multiple tabs in a Google Spreadsheet using Google App Script ? Data in all the tabs keep changing.

例如，我在 Google 电子表格中有Sheet1"、Sheet2"和Sheet3".所有这些工作表中的数据都有 3 列 - 姓名和电子邮件 ID &地区.现在，我想将这 3 个工作表/选项卡中存在的数据合并/组合到第 4 个工作表中，即具有相同列(名称、电子邮件 ID 和区域)的工作表 4".第 4 张表的数据应为 - Sheet1 的数据，然后是 Sheet2，然后是 Sheet3.所有 3 张纸中的行数不断变化.

For example, I have 'Sheet1', 'Sheet2' and 'Sheet3' in a Google spreadsheet. Data in all these sheets have 3 columns - Name and email ID & Region. Now, I want to merge/combine the data present in these 3 sheets/tabs into a 4th Sheet i.e. 'Sheet 4' having same Columns (name, email Id & Region). The 4th sheet should have data as - data of Sheet1 followed by Sheet2 and then Sheet3. The number of rows in all the 3 sheets keeps changing.

推荐答案

=query({Sheet1!A1:C; Sheet2!A1:C; Sheet3!A1:C}, "where Col1 is not null";, 0)

我不会为此使用脚本；工作表公式要快得多，至少在大多数情况下是这样.确保使用分号分隔范围.分号是数组文字的 End_Of_Row 运算符.

I wouldn't use a script for this; the worksheet formulas are much faster, at least most of the time. Make sure you use semicolons to separate the ranges. Semicolons are the End_Of_Row operator for array literals.

如果你真的想使用脚本...

If you really want to use a script...

function combineSheets() {
  var sApp = SpreadsheetApp.getActiveSpreadsheet();
  var s1= sApp.getSheetByName("Sheet1");
  var s2= sApp.getSheetByName("Sheet2");
  var s3= sApp.getSheetByName("Sheet3");
  var s4= sApp.getSheetByName("Sheet4");
  //  If Sheet4 doesn't exist you'll need to create it here.
  
  var s1values = s1.getRange(1,1,s1.getLastRow(),3).getValues();
  var s2values = s2.getRange(1,1,s2.getLastRow(),3).getValues();
  var s3values = s3.getRange(1,1,s3.getLastRow(),3).getValues();
  
  //  Now, we can put out all together and stuff it in Sheet4
  var s4values = [];
  s4values =  s1values.concat(s2values,s3values);
  s4.getRange(1,1,s4values.length,3).setValues(s4values);
}

这篇关于如何使用 Google App Script 合并 Google 电子表格中的多个标签?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！