Google表格:将文件夹范围导入母版表格 [英] Google Sheets: Import folder range into Master Sheet

问题描述

我希望从源文件夹中的电子表格中导入行，该文件夹每天都会使用新的电子表格进行更新.是否可以将源文件夹中每个新电子表格中的特定范围拉入母版表中?

I'm looking to import rows from spreadsheets in a source folder that will be updated daily with new spreadsheets. Is it possible to pull a specific range from every new spreadsheet in the source folder into a Master Sheet?

我一直在使用下面的代码来回答类似的问题.除复制已导入到母版表的数据外，此方法非常有效.我该如何解决?

I've been using the following code from an answer to a similar question here. This works perfectly except it duplicates the data that has already been imported to the Master Sheet. How can I fix this?

function getdata() {
  //declare multiple variables in one statement - Initial value will
  //be undefined
  var destinationSpreadsheet,destinationSS_ID,destsheet,destrange,file,files,
      sourcerange,sourceSS,sourcesheet,srcSheetName,sourcevalues;

  srcSheetName = "Type the Name of Source Sheet Tab Here";
  destinationSS_ID = "Type the Name of destination spreadsheet file ID Here";
  files = DriveApp.getFolderById("Folder ID").getFiles();
  destinationSpreadsheet = SpreadsheetApp.openById(destinationSS_ID);
  destsheet = destinationSpreadsheet.getSheetByName('Sheet1');  

  while (files.hasNext()) {
    file = files.next();
    if (file.getMimeType() !== "application/vnd.google-apps.spreadsheet") {
      continue;
    };
    sourceSS = SpreadsheetApp.openById(file.getId());
    sourcesheet = sourceSS.getSheetByName(srcSheetName);
    //sourcesheet.getRange(start row, start column, numRows, number of Columns)
    sourcerange = sourcesheet.getRange(2,1,sourcesheet.getLastRow()-1,6);
    sourcevalues = sourcerange.getValues();

    //Write all the new data to the end of this data
    destrange = destinationSpreadsheet.getSheetByName("Sheet1")
        .getRange(destsheet.getLastRow()+1,1,sourcevalues.length,sourcevalues[0].length);

    destrange.setValues(sourcevalues);         
  };
};

推荐答案

• 您要将数据文件-TEST#"电子表格的"Sheet1"的值放入"MASTER"电子表格的"Sheet1".
• 当"MASTER"中存在数据文件-TEST#"的值时，您不想放置数据文件-TEST#"的值.

如果我的理解是正确的，那么该修改如何?修改后的脚本流程如下.请认为这只是几个答案之一.

If my understanding is correct, how about this modification? The flow of modified script is as follows. Please think of this as just one of several answers.

1. 从"MASTER"电子表格的"Sheet1"中检索值.
2. 从数据文件-TEST#"电子表格的"Sheet1"中检索值.
3. 比较两个值.如果两个工作表中的行的值都不相同，则将这些值从数据文件-TEST#"放到主"中.

修改后的脚本:

function getdata() {
  var masterSpreadsheetId = "###"; // Spreadsheet ID of "MASTER" spreadsheet.
  var folderId = "###"; // Folder ID including "Data File" files.
  var dataFileSheetName = "Sheet1";

  var files = DriveApp.getFolderById(folderId).getFiles();
  var masterSpreadsheet = SpreadsheetApp.openById(masterSpreadsheetId);
  var destSheet = masterSpreadsheet.getSheetByName('Sheet1');
  var destSheetValues = destSheet.getRange(2, 1, destSheet.getLastRow() - 1, 6).getValues();
  var values = [];
  while (files.hasNext()) {
    var file = files.next();
    if (file.getMimeType() !== "application/vnd.google-apps.spreadsheet") continue;
    var dataFileSS = SpreadsheetApp.openById(file.getId());
    var dataFileSheet = dataFileSS.getSheetByName(dataFileSheetName);
    var dataFileValues = dataFileSheet.getRange(2, 1, dataFileSheet.getLastRow()-1, 6).getValues();
    Array.prototype.push.apply(values, dataFileValues);
  };
  var res = values.filter(function(e) {
    return !destSheetValues.some(function(f) {
      return e.every(function(g, k) {
        var temp = f[k];
        if (k < 2) {
          g = new Date(g).getTime();
          temp = new Date(temp).getTime();
        }
        return g == temp;
      });
    });
  });
  if (res.length > 0) destSheet.getRange(destSheet.getLastRow()+1,1,res.length,res[0].length).setValues(res);
}

注意:

• 在此示例脚本中，使用了"MASTER"，"Data File-TESTA"，"Data File-TESTB"和"Data File-TESTC"的共享示例电子表格.因此，如果您的实际情况使用了电子表格文件名和工作表名的不同名称，请对其进行修改.
• filter()
• some()
• every()

这篇关于Google表格:将文件夹范围导入母版表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！