按列拆分Google Sheet后导出Xlsx为空文件的技术问题

空Excel文件的核心问题是Google Drive的同步延迟：当你创建新表格并写入数据后，Google服务器不会立即持久化这些更改。如果在数据完成同步前触发导出链接，服务器会返回表格的初始空状态。当拆分文件数量超过2个时，这个问题必然出现，因为连续创建文件会让服务器来不及处理每个文件的同步，导出请求就已发出。

此外，循环弹出多个模态对话框会导致浏览器事件竞争，部分导出请求可能在文件同步完成前就被触发。

关键修复点 #

1. 强制同步数据：在写入数据后调用SpreadsheetApp.flush()，确保所有待处理的更改立即保存到Google服务器，消除同步延迟。
2. 优化下载机制：用单个对话框替代多个模态窗口，通过JavaScript按顺序触发下载并加入延迟，给每个文件足够的同步时间，避免浏览器事件冲突。

修改后的完整代码 #

const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const spreadSheet = activeSpreadsheet.getActiveSheet();
const sRange = spreadSheet.getDataRange();
const rawData = sRange.getValues();
const activeRange = spreadSheet.getActiveRange();
const newSheetName = activeRange.getValues();
const rangeCol = activeRange.getColumn();
let outputArray = [];

function removeusingSet(arr) {
  let outputArray = Array.from(new Set(arr));
  return outputArray;
}

let namesLower = removeusingSet(newSheetName.flat());
let names = namesLower.map(x => x.toUpperCase());

function splitSheet() {
  let sheetLink = [];
  
  for (let x = 1; x < names.length; x++) {
    if (names[x] !== '' && names[x] !== null) {
      // 创建新表格
      let newSS = SpreadsheetApp.create(names[x]);
      let newId = newSS.getId();
      let newSheet = SpreadsheetApp.openById(newId);
      let targetSheet = newSheet.getSheetByName('Sheet1');
      targetSheet.setName(names[x]);
      
      // 筛选数据
      let data = [rawData[0]]; // 添加表头
      for (let y = 0; y < rawData.length; y++) {
        if (rawData[y][rangeCol-1].toUpperCase() === names[x]) {
          data.push(rawData[y]);
        }
      }
      
      // 写入数据并强制同步
      targetSheet.clear({contentsOnly: true});
      targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
      SpreadsheetApp.flush(); // 关键：强制数据立即保存到服务器
      
      // 添加导出链接
      sheetLink.push(`https://docs.google.com/spreadsheets/d/${newId}/export?format=xlsx`);
      
      // 可选：创建文件之间添加短暂延迟，降低服务器负载
      Utilities.sleep(500);
    }
  }
  
  // 生成单个对话框处理顺序下载
  let html = HtmlService.createHtmlOutput(`
    <html>
      <body style="word-break:break-word;font-family:sans-serif;">
        <p>正在准备下载...</p>
        <div id="links"></div>
      </body>
      <script>
        const links = ${JSON.stringify(sheetLink)};
        let index = 0;
        
        function triggerDownload() {
          if (index >= links.length) {
            document.getElementById('links').innerHTML = '<p>所有下载已启动！</p>';
            setTimeout(() => google.script.host.close(), 2000);
            return;
          }
          
          const url = links[index];
          const a = document.createElement('a');
          a.href = url;
          a.target = '_blank';
          a.download = '${names[index+1]}.xlsx'; // 用表格名称作为下载文件名
          
          // 触发点击下载
          if (document.createEvent) {
            const event = document.createEvent('MouseEvents');
            event.initEvent('click', true, true);
            a.dispatchEvent(event);
          } else {
            a.click();
          }
          
          // 添加备用可点击链接
          const linkElement = document.createElement('a');
          linkElement.href = url;
          linkElement.target = '_blank';
          linkElement.textContent = `下载文件 ${index + 1}：${names[index+1]}`;
          document.getElementById('links').appendChild(linkElement);
          document.getElementById('links').appendChild(document.createElement('br'));
          
          index++;
          // 每2秒触发下一个下载，确保数据同步完成
          setTimeout(triggerDownload, 2000);
        }
        
        // 页面加载后开始下载
        window.onload = triggerDownload;
        google.script.host.setHeight(200);
        google.script.host.setWidth(400);
      </script>
    </html>
  `);
  
  SpreadsheetApp.getUi().showModalDialog(html, "启动下载");
}

代码修改说明 #

• SpreadsheetApp.flush()：写入数据后立即调用，确保数据同步到服务器，避免导出空文件。
• 单对话框顺序下载：用一个对话框按顺序触发下载，每个下载间隔2秒，给服务器足够的同步时间。
• 备用链接：对话框中显示所有下载链接，自动下载失败时可手动点击。
• 文件名优化：用拆分后的表格名称作为下载文件名，更易识别。
• 创建文件延迟：在循环中添加500毫秒延迟，降低服务器处理压力。

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