一次将Google Sheet数据全部导入到具有数据的现有表格

发布于 2025-02-13 10:20:21 字数 1019 浏览 3 评论 0原文

目前，我一直在使用此脚本，该脚本从我的Google Drive中的电子表格中导入数据。该函数起作用，但一次导入数据一行。有时，这些床单是400列以上，需要很长时间。我正在寻找它来获取所有数据并将其导入现有的电子表格和最后一个值的末尾。

function getData() {

get_files = ['July1-2022'];
  
  var ssa = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ssa.getSheetByName('CancelRawData');  
  
  for(z = 0; z < get_files.length; z++)
  {
  
    var files = DriveApp.getFilesByName(get_files[z]);
    while (files.hasNext()) 
    {
      var file = files.next();
      break;
    }
  
    var ss = SpreadsheetApp.open(file);
    SpreadsheetApp.setActiveSpreadsheet(ss);
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for(var i = 0; i < sheets.length; i++)
    {  
      var nameSheet = ss.getSheetByName(sheets[i].getName()); 
      var nameRange = nameSheet.getDataRange();
      var nameValues = nameRange.getValues();
      
      for(var y = 1; y < nameValues.length; y++)
      {
        copySheet.appendRow(nameValues[y]); 
      }
    }
  }
  SpreadsheetApp.getUi().alert("

原文

Currently I have been using this script which imports data from a spreadsheet located in my google drive. The function works but imports the data one line at a time. Some times these sheets are 400+ rows and that takes a long time. I am looking for it to grab all data and import it into an existing spreadsheet and the end of the last value.

function getData() {

get_files = ['July1-2022'];
  
  var ssa = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ssa.getSheetByName('CancelRawData');  
  
  for(z = 0; z < get_files.length; z++)
  {
  
    var files = DriveApp.getFilesByName(get_files[z]);
    while (files.hasNext()) 
    {
      var file = files.next();
      break;
    }
  
    var ss = SpreadsheetApp.open(file);
    SpreadsheetApp.setActiveSpreadsheet(ss);
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for(var i = 0; i < sheets.length; i++)
    {  
      var nameSheet = ss.getSheetByName(sheets[i].getName()); 
      var nameRange = nameSheet.getDataRange();
      var nameValues = nameRange.getValues();
      
      for(var y = 1; y < nameValues.length; y++)
      {
        copySheet.appendRow(nameValues[y]); 
      }
    }
  }
  SpreadsheetApp.getUi().alert("???? Congratulations, your data has been all imported", SpreadsheetApp.getUi().ButtonSet.OK);
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我也只是我 2025-02-20 10:20:21

我相信您的目标如下。

您想降低脚本的流程成本。

修改点：

appendrow在循环中使用。过程成本将很高。
在您的脚本中，sheet可以通过var sheets = everdsheetapp.open（file）.getSheets（）;编写。

当这些要点反映在您的脚本中时，如下所示。

修改后的脚本1：

此脚本使用电子表格服务（电子表格）。

function getData() {

  // Retrieve values.

  get_files = ['July1-2022'];

  var values = [];

  for (z = 0; z < get_files.length; z++) {

    var files = DriveApp.getFilesByName(get_files[z]);

    while (files.hasNext()) {

      var file = files.next();

      break;

    }

    var sheets = SpreadsheetApp.open(file).getSheets();

    for (var i = 0; i < sheets.length; i++) {

      values = [...values, ...sheets[i].getDataRange().getValues()];

    }

  }

  if (values.length == 0) return;
  // Put values.

  const maxLen = Math.max(...values.map(r => r.length));

  values = values.map(r => [...r, ...Array(maxLen - r.length).fill(null)]);

  var copySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CancelRawData');

  copySheet.getRange(copySheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

  SpreadsheetApp.getUi().alert("

I believe your goal is as follows.

You want to reduce the process cost of your script.

Modification points:

appendRow is used in a loop. The process cost will be high.
In your script, sheets can be written by var sheets = SpreadsheetApp.open(file).getSheets();.

When these points are reflected in your script, it becomes as follows.

Modified script 1:

This script uses the Spreadsheet service (SpreadsheetApp).

function getData() {
  // Retrieve values.
  get_files = ['July1-2022'];
  var values = [];
  for (z = 0; z < get_files.length; z++) {
    var files = DriveApp.getFilesByName(get_files[z]);
    while (files.hasNext()) {
      var file = files.next();
      break;
    }
    var sheets = SpreadsheetApp.open(file).getSheets();
    for (var i = 0; i < sheets.length; i++) {
      values = [...values, ...sheets[i].getDataRange().getValues()];
    }
  }
  if (values.length == 0) return;

  // Put values.
  const maxLen = Math.max(...values.map(r => r.length));
  values = values.map(r => [...r, ...Array(maxLen - r.length).fill(null)]);
  var copySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CancelRawData');
  copySheet.getRange(copySheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
  SpreadsheetApp.getUi().alert("???? Congratulations, your data has been all imported", SpreadsheetApp.getUi().ButtonSet.OK);
}

Modified script 2:

This script uses Sheets API. Before you use this script, please enable Sheets API at Advanced Google services.

function getData() {
  // Retrieve values.
  get_files = ['July1-2022'];
  var values = [];
  for (z = 0; z < get_files.length; z++) {
    var files = DriveApp.getFilesByName(get_files[z]);
    while (files.hasNext()) {
      var file = files.next();
      break;
    }
    var spreadsheetId = file.getId();
    var ranges = SpreadsheetApp.open(file).getSheets().map(s => `'${s.getSheetName()}'!${s.getDataRange().getA1Notation()}`);
    values = [...values, ...Sheets.Spreadsheets.Values.batchGet(spreadsheetId, { ranges }).valueRanges.flatMap(({ values }) => values)];
  }
  if (values.length == 0) return;

  // Put values.
  var dstSS = SpreadsheetApp.getActiveSpreadsheet();
  Sheets.Spreadsheets.Values.append({ values }, dstSS.getId(), 'CancelRawData', { valueInputOption: "USER_ENTERED" });
  SpreadsheetApp.getUi().alert("???? Congratulations, your data has been all imported", SpreadsheetApp.getUi().ButtonSet.OK);
}

Note:

When these scripts are run, all files of get_files are retrieved and all values are retrieved from all sheets in all Spreadsheets, and the retrieved values are put to the destination sheet of CancelRawData.