SheetJS+Google Apps Script转Excel到CSV报错及大文件导入优化求助

背景与初始方案超时问题 #

我在Google Drive文件夹中有6个文件（5个XLSX、1个CSV），需导入到Google Sheet的6个不同标签页。此前使用Tanaike的方案处理小文件正常，但我的文件含约5万条记录、大小4-5MB，执行时出现**"Error: Exceeded maximum execution time"**超时错误，脚本如下：

function importExcel1(file, sheet) {
  // Library Key: 1B0eoHz03BVtSZhJAocaGNq94RjoXocz8xGMaLzwVdmAvYW5k8s5Yd360
  // Retrieve values from XLSX file.
  const MD = MicrosoftDocsApp.setFileId(file.getId());
  const srcSS = MD.getSpreadsheet();
  const values = srcSS.getSheets()[0].getDataRange().getValues();
  // console.log(values); // Confirm the retrieved values in the log.

  MD.end(); // When this line is run, the Spreadsheet created as a temporal file from the XLSX file is removed.
  if (values.length > 0) {
    sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  }
}

SheetJS方案报错问题 #

之后改用SheetJS库方案（改编自Tanaike的另一方案），脚本如下：

function importFiles() {
  var folderId = 'folder ID'; // ID of the folder where files are stored
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  while (files.hasNext()) {
    var file = files.next();
    var fileName = file.getName();

    // Skip files that already have "Done" in the name
    if (fileName.includes('Done')) {
      console.log(`Skipping already processed file: ${fileName}`);
      continue;
    }

    var fileType = fileName.slice(0, 6).toLowerCase();
    var sheet;
    var range;

    switch (fileType) {
      case 'parcel':
        sheet = ss.getSheetByName("import - shipping");
        range = 'A:T';
        break;
      case 'kit-re':
        sheet = ss.getSheetByName("import - kitting");
        range = 'A:G';
        break;
      case 'req-re':
        sheet = ss.getSheetByName("import - orders");
        range = 'A:U';
        break;
      case 'billin':
        sheet = ss.getSheetByName("import - billing codes");
        range = 'A:G';
        break;
      case 'req_li':
        sheet = ss.getSheetByName("import - order line");
        range = 'A:G';
        break;
      case 'packag':
        if (fileName.endsWith('.csv')) {
          sheet = ss.getSheetByName("Manual Extract - Package Grid");
          range = 'A:AO';
        }
        break;
      default:
        continue; // Skip files that do not match the criteria
    }

    if (sheet && range) {
      sheet.getRange(range).clearContent();
      SpreadsheetApp.flush();
      console.log("Sheet: " + sheet.getName());
      console.log("File: " + file.getName());
      if (file.getMimeType() === MimeType.CSV) {
        importCSV(file, sheet);
      } else {
        convertExcelToCSV(file, sheet);
      }

      // Rename the file by adding "Done" to the end of its name
      // file.setName(fileName + ' Done');
      // console.log(`Processed and renamed file: ${fileName} to ${file.getName()}`);
    }
  }
}

function importCSV(dataOrFile, sheet) {
  var csvData;

  if (typeof dataOrFile === 'string') {
    // If dataOrFile is a string, treat it as CSV data
    csvData = dataOrFile;
  } else {
    // If dataOrFile is a file, extract the data as a string
    csvData = dataOrFile.getBlob().getDataAsString();
  }

  var sheetId = sheet.getSheetId();
  console.log(sheetId);

  var resource = {
    requests: [
      {
        pasteData: {
          data: csvData,
          coordinate: { sheetId: sheetId },
          delimiter: ","
        }
      }
    ]
  };

  Sheets.Spreadsheets.batchUpdate(resource, 'Spreadsheet ID');
}

function convertExcelToCSV(file, sheet) {
  // Get the Excel file as a blob
  var excelBlob = DriveApp.getFileById(file.getId()).getBlob();

  // Convert the Blob into a byte array and read it using the XLSX library
  var data = new Uint8Array(excelBlob.getBytes());
  var workbook = XLSX.read(data, { type: 'array' });

  // Get the first sheet from the workbook
  var firstSheetName = workbook.SheetNames[0];
  var firstSheet = workbook.Sheets[firstSheetName];

  var csvData = XLSX.utils.sheet_to_csv(firstSheet);

  // Import the CSV data into the Google Sheet
  importCSV(csvData, sheet);
}

但该方案持续出现错误：

Error: invalid column -1
encode_col @ SheetJS.gs:4454
sheet_to_csv @ SheetJS.gs:27507

需求 #

现寻求可靠的批量请求实现方式及错误解决方法。

内容的提问来源于stack exchange，提问作者EagleEye