Google Sheets脚本导出异常：Button1导出错误表格求助

问题原因 #

你在同一个Google Apps Script项目中定义了两个同名的createDataUrl函数，在JavaScript（包括Apps Script）环境中，后定义的函数会完全覆盖先定义的。因此当button1触发的侧边栏调用createDataUrl时，实际执行的是第二个针对「Employee Salaries」工作表的函数，导致导出错误的文件。

修复方案 #

推荐两种修复方式，其中方案二更简洁且易维护：

方案一：为函数重命名，区分不同工作表 #

1. 修改第一个脚本的函数名（对应「Employee Names」工作表）：

function openSidebar() {
  const html = HtmlService.createHtmlOutputFromFile("index").setTitle("Download");
  SpreadsheetApp.getUi().showSidebar(html);
}

// 重命名为createDataUrlForNames
function createDataUrlForNames(type) {
  const mimeTypes = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Employee Names");
  let url = null;
  if (type == "csv") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet1.getSheetId()}`;
  } else if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${sheet1.getSheetId()}`;
  }
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet1.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };
}

2. 修改第二个脚本的函数名（对应「Employee Salaries」工作表）：

function openSidebarns() {
  const html = HtmlService.createHtmlOutputFromFile("indexns").setTitle("Download");
  SpreadsheetApp.getUi().showSidebar(html);
}

// 重命名为createDataUrlForSalaries
function createDataUrlForSalaries(type) {
  const mimeTypes = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Employee Salaries");
  let url = null;
  if (type == "csv") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet.getSheetId()}`;
  } else if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${sheet.getSheetId()}`;
  }
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };
}

3. 分别修改两个HTML文件的调用：

• index.html中调用createDataUrlForNames(type)
• indexns.html中调用createDataUrlForSalaries(type)

方案二：合并为通用函数，接受工作表名称参数（更推荐） #

1. 删除重复的createDataUrl函数，保留一个通用版本：

function openSidebar() {
  const html = HtmlService.createHtmlOutputFromFile("index").setTitle("Download");
  SpreadsheetApp.getUi().showSidebar(html);
}

function openSidebarns() {
  const html = HtmlService.createHtmlOutputFromFile("indexns").setTitle("Download");
  SpreadsheetApp.getUi().showSidebar(html);
}

// 新增sheetName参数，支持动态指定工作表
function createDataUrl(type, sheetName) {
  const mimeTypes = { csv: MimeType.MICROSOFT_EXCEL, pdf: MimeType.PDF };
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) return { data: null, filename: null };
  
  let url = null;
  if (type == "csv") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet.getSheetId()}`;
  } else if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${sheet.getSheetId()}`;
  }
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };
}

2. 修改HTML文件的调用：

• index.html中传入「Employee Names」：google.script.run.withSuccessHandler(...).createDataUrl(type, "Employee Names")
• indexns.html中传入「Employee Salaries」：google.script.run.withSuccessHandler(...).createDataUrl(type, "Employee Salaries")

这种方式既解决了函数覆盖问题，还减少了代码冗余，后续新增工作表只需修改HTML调用即可。

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