合并多个标签，下载为CSV文件 [英] Merge multiple tabs, download as CSV

问题描述

我在Google文件夹中有一打纸.使用下面的脚本，我可以获取要复制到新电子表格中的每个文件的第一个选项卡.但是现在我希望将所有数据复制到一个选项卡中，并将其作为csv文件下载到共享驱动器(而不是Google Apps).数据的格式始终相同，只是有时每张纸的行数不同.

I have a dozen sheets in a google folder. With the script below I can get the first tab of each file to be copied in a new spreadsheet. But now I want all the data to be copied in just one tab, and have it downloaded as csv file to a a shared drive (not google apps). The format of the data is always the same, just sometimes the number of rows differs per sheet.

此外，我想根据名称而不是数字来选择选项卡-避免将来出现错误.有什么建议可以解决这个问题吗?

In addition, I would like to select the tab based on name instead of number - to avoid errors in the future. Any suggestions how to tackle this problem?

function mergeSheets() {

/* Retrieve the desired folder */
var myFolder = DriveApp.getFoldersByName('name of folder').next();

/* Get all spreadsheets that resided on that folder */
var spreadSheets = myFolder.getFilesByType("application/vnd.google-    apps.spreadsheet");

/* Create the new spreadsheet that you store other sheets */  
var newSpreadSheet = SpreadsheetApp.create("Merged Sheets");

/* Iterate over the spreadsheets over the folder */
while(spreadSheets.hasNext()) {

var sheet = spreadSheets.next();

/* Open the spreadsheet */
var spreadSheet = SpreadsheetApp.openById(sheet.getId());

/* Get all its sheets */
//    for(var y in spreadSheet.getSheets()) {

  /* Copy the sheet to the new merged Spread Sheet */
  spreadSheet.getSheets()[0].copyTo(newSpreadSheet); 
//    }
}      
}

推荐答案

下面是另一种可能的解决方案，可以使用更快的方法将一个文件夹中的所有电子表格编译为一个电子表格工作表(

Below is another possible solution to compile all the spreadsheets from a folder into a single spreadsheet sheet using a faster method (concatenate arrays).

唯一的限制是所有工作表必须具有相同的列数(行数并不重要)

The only restriction is that all the sheets must have the same number of columns (row number is not important)

从这张纸上，很容易将其内容导出到CSV并将其保存为驱动器文件.我以您的代码为起点，并像注释一样注释了迭代每个电子表格中所有工作表的部分，但是您当然可以取消注释for循环.我添加了一些注释来解释该过程.

From this single sheet it will be easy to export its contnet to CSV and save it as a drive file. I used your code as a starting point and commented the part that iterate all the sheets in each spreadsheet just as you did but you can of course uncomment the for loop). I added a few comments to explain the procedure.

function mergeSheets() {
  /* Retrieve the desired folder */
  //var myFolder = DriveApp.getFoldersByName('name of folder').next();
  // or better 
  var myFolder = DriveApp.getFolderById('0B3qSFd3iikE3RDhxekVYNk5zUkk');

  /* Get all spreadsheets that resided on that folder */
  var spreadSheets = myFolder.getFilesByType(MimeType.GOOGLE_SHEETS);

  /* Create the new spreadsheet that you store other sheets */  
  var newSpreadSheet = SpreadsheetApp.create("Merged Sheets");
  var newSheetTab = newSpreadSheet.getActiveSheet();
  var values = [] ;// new array to get all sheets contents
  /* Iterate over the spreadsheets over the folder */
  var header = [];
  while(spreadSheets.hasNext()) {

    var sheet = spreadSheets.next();
    Logger.log(sheet.getName()); // log the sheet name to check
    /* Open the spreadsheet */
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());

    /* Get all its sheets */
    //    for(var y in spreadSheet.getSheets()) {

    //for(var sheet in spreadSheet.getSheets()){
      var sheet = spreadSheet.getSheets()[0];// comment this line if you keep the for loop
      var range = sheet.getDataRange();
      var data = range.getValues();
      if(header.length==0){ 
        header = data.shift(); // get the header only once and remove header from data in the same time
      }else{
        var dummy = data.shift(); // remove header from data but don't keep it
      }
      values = values.concat(data); // add data to final array in each while loop 
   // }
  }
  values.unshift(header); // add the header at the array start
  Logger.log(values);//see result
  newSheetTab.getRange(1,1,values.length, values[0].length).setValues(values);// batch write to the sheet
}

// then export this single sheet as CSV see code from Google tuto
// https://developers.google.com/apps-script/articles/docslist_tutorial#section-3-saving-a-selected-range-to-a-csv-file

这篇关于合并多个标签，下载为CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！