Google表格通过脚本将多张表格合并为一张表格 [英] Google Sheets combine several sheets into one sheet with script

问题描述

我需要用脚本将几张纸合并为一张纸.另外，在合并工作表时，我需要通过将其名称分为两列来将它们分开.例如，包含单词"cat"的表格被称为猫".应该在左列和包含单词"dog"的表中.应该在右列.目前，我有一个脚本，可以将所有带有特定单词的工作表过滤到一个数组中.我是Google表格的新手，我们将不胜感激.

I need to combine several sheets into one sheet with script. Also, when combining sheets I need to separate them by checking their names in to two columns. For example sheets which contain a word "cat" should be on the left column and sheets which contain a word "dog" should be on the right column. Currently, I have a script which filters all sheets with a specific words to one array. I am new to Google Sheets, any help will be appreciated.

这是我当前的脚本:

function myFunction() 
 {
 var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var newSheet = activeSpreadsheet.getSheetByName("AllSheets");

if (newSheet != null) {
    activeSpreadsheet.deleteSheet(newSheet);
}

newSheet = activeSpreadsheet.insertSheet();
newSheet.setName("AllSheets");
   const ss = SpreadsheetApp.getActiveSpreadsheet();
   const allSheets = ss.getSheets();
   const allSheets_names=allSheets.map(sheet=>sheet.getSheetName())  

   const neededSheets= ["Cat", "Dog"];
   
   const filteredListOfSheetsNames = []   
   neededSheets.forEach(ns =>allSheets_names.forEach( (as,index) =>
       {if (as.indexOf(ns)>-1){filteredListOfSheetsNames.push(as)}}))                   
       const filteredListOfSheets =  filteredListOfSheetsNames.map(name =>ss.getSheetByName(name))  
           
}

推荐答案

最好是创建两个数组-用于摘要表的两个不同范围

• 您将一个猫床单"附加到另一个，狗床单"
• 既然您已经正确找到了感兴趣的工作表并对其进行了排序，则可以在内部循环中定义一个辅助数组(每种工作表分别使用一个单独的数组)，在该数组中附加一种类型的所有工作表中的数据
• 将此数组设置为目标工作表的所需范围(侧面)
• 重要的是要知道原始表的(最大)尺寸，以便正确划分目标表的表皮/右侧.

The best is to create two arrays - for two different ranges of the summary sheet

• To one you append the "Cat sheets", to the other the "Dog sheets"
• Since you already found and sorted your sheets of interest correctly, within your inner loop you can define an auxiliary array (a separate one for each type of sheets) to which you append the data from all the sheets of one type
• Set this array to the desired range (side) of the destination sheet
• It is important to know the (maximum) dimensions of the origin sheets to divide the lest/right sides of the destinations sheet correctly.

示例:

function myFunction() 
{
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = activeSpreadsheet.getSheetByName("AllSheets");  
  if (newSheet != null) {
    activeSpreadsheet.deleteSheet(newSheet);
  }  
  newSheet = activeSpreadsheet.insertSheet();
  newSheet.setName("AllSheets");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();
  const allSheets_names=allSheets.map(sheet=>sheet.getSheetName())    
  const neededSheets= ["Cat", "Dog"];  
  const filteredListOfSheetsNames = [];       
  neededSheets.forEach(function(ns){  
    var i = neededSheets.indexOf(ns);
    filteredListOfSheetsNames[i]=[];
    allSheets_names.forEach( (as,index) => {                            
      if (as.indexOf(ns)>-1){
        filteredListOfSheetsNames[i].push(as);
        }
      }
    )
    const filteredListOfSheets  =  filteredListOfSheetsNames[i].map(name =>ss.getSheetByName(name));  
    var array = [];
    filteredListOfSheets.forEach(function(sheet){var values = sheet.getRange(1,1,sheet.getLastRow(),13).getValues(); array.push(values);});
    array = [].concat.apply([],array);
    if(array.length > 0){
      newSheet.getRange(1,i*13+1, array.length, array[0].length).setValues(array);
      }
    }
  )
}

这篇关于Google表格通过脚本将多张表格合并为一张表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！