使用Google Apps脚本将Google Spreadsheets合并到一个文件夹中 [英] Merge Google Spreadsheets in a folder with Google Apps Script

问题描述

我试图自动化一个流程来合并电子表格。我撰写了两篇博文（此处和< a href =http://stufftothepoint.blogspot.com/2013/12/very-simple-way-to-combine-google.html =nofollow> here ）描述如何使用公式。现在我试图通过允许用户合并文件夹内的电子表格或选择要自动合并的电子表格来简化此过程。如果可能，我希望使用Google Apps脚本完成所有这些工作。
所以我想知道什么是最佳方式：

1. 获取文件夹中所有电子表格的键


2. 有一个像文件选择器一样的窗口，用于选择用户需要合并的电子表格。
   

这两个选项将允许我可以自动化我在博客文章中的脚本。

解决方案

我想你可以建立自己的界面。
为了帮助我理解逻辑，我将所有必要的功能合并到了shhets下面：

您可以看到现行版本此处。

 函数myFunction（）{
 
 / *检索所需文件夹* / 
 var myFolder = DriveApp.getFolderById（0B0kQD4hSd4KASUJKb2cya0NET1U） ; 
 
 / *获取所有位于该文件夹中的电子表格* / 
 var spreadSheets = myFolder.getFilesByType（application / vnd.google-apps.spreadsheet）; 
 
 / *创建存储其他工作表的新电子表格* / 
 var newSpreadSheet = SpreadsheetApp.create（合并工作表）; 
 
 / *迭代文件夹* / 
上的电子表格，（spreadSheets.hasNext（））{
 
 var sheet = spreadSheets.next（）; 
 
 / *打开电子表格* / 
 var spreadSheet = SpreadsheetApp.openById（sheet.getId（））; 
 $ b $ * *获取所有工作表* / 
（spreadSheet.getSheets（）中的var y）{
 
 / *将工作表复制到新合并的Spread Sheet * / 
 spreadSheet.getSheets（）[y] .copyTo（newSpreadSheet）; 
} 
} 
} 
  

I'm trying to automate a process to merge spreadsheets. I've written two blog posts (here and here) describing how to do that using formulas. Now I'm trying to make this process to be much simpler by allowing people to merge spreadsheets inside the folder or pick what spreadsheets to merge automatically. If possible I would like to do all this using Google Apps Script. So I was wondering what is the best way to:

1. get keys of all spreadsheets in the folder
2. have a "file chooser" like window to pick spreadsheets that user need to merge

This two options will allow me to enable to automate the script that I have in the blog posts.

解决方案

I guess that you can build your own interface. To help with the logic I put all the necessary functions to merge the shhets bellow:

You can see the live version here.

function myFunction() {

  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFolderById("0B0kQD4hSd4KASUJKb2cya0NET1U");

  /* 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()[y].copyTo(newSpreadSheet); 
     }
  }      
}

这篇关于使用Google Apps脚本将Google Spreadsheets合并到一个文件夹中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！