Google Sheets Apps Script导入CSV仅所有者可用，求排查解决

我是电商顾问，制作了一款Google表格用于收集客户物流信息，支持上传CSV文件导入订单与物流数据，功能基于Tanaike的代码开发。表格所有者账号操作时，HTML弹窗选择CSV可正常完成导入；但共享给客户或测试账号后，用户等待15分钟仍无法完成导入。已尝试以下操作但问题依旧：

• 创建可安装触发器
• 移除所有相关单元格保护
• 更换浏览器

附上相关代码，恳请排查问题原因：

CreateTrigger.gs #

function createSpreadsheetEditTrigger() {
  const ss = SpreadsheetApp.openById('SPREADSHEET_ID');
  ScriptApp.newTrigger('onEditScript')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

Main.gs #

function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu("importCSV")
        .addItem("importCSV", "importCSV")
        .addToUi();
}

function importCSV(e) {
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "Choose CSV");
    return;
  }
  SpreadsheetApp.getUi().alert('csv read will start');
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  SpreadsheetApp.getUi().alert('csv read successful');
  UserImportWizardSheet.getRange('A2:K').clearContent();
  insertCSV(csv);
}


function insertCSV(csv) {
  SpreadsheetApp.getUi().alert('insert csv started');
  MasterCsvSheet.getDataRange().clearContent();
  var CsvRange = MasterCsvSheet.getRange(1, 1, csv.length, csv[0].length);
  CsvRange.setValues(csv);
}

onEditScript.gs #

function onEditScript(e) {

  var sheetName = e.range.getSheet().getName();
  var value = e.value;
  var a1notation = e.range.getA1Notation();
  
  if (sheetName == 'WelcomeScreen') {

    if (a1notation == 'B2') {
      ...
    }

    if (a1notation == 'B4') {
      SpreadsheetApp.getActiveSheet().getRange('B4').setValue(false);
      importCSV();
    }

    if (a1notation == 'B6') {
      ...
    }

  }
}

index.html #

<form>
  <input type="file" name="file" onchange="importCSV(this.parentNode)" accept=".csv,text/csv">
  <div id="progress">Waiting</div>
</form>
<script>
  function importCSV(e) {
  const div = document.getElementById("progress");
  div.innerHTML = "Please wait while we process your file...";
  const file = e.file.files[0];
  const f = new FileReader();
  f.onload = d => google.script.run.withSuccessHandler(google.script.host.close).importCSV([[...new Int8Array(d.target.result)], file.type, file.name]);
  f.readAsArrayBuffer(file);
}
</script>

1. 未声明全局表变量导致权限失败 #

代码中UserImportWizardSheet、MasterCsvSheet属于未显式声明的全局变量，当共享用户调用代码时，会因无法解析表对象导致静默失败。

• 修复方案：在importCSV和insertCSV中显式获取工作表：

function importCSV(e) {
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "Choose CSV");
    return;
  }
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const UserImportWizardSheet = ss.getSheetByName('UserImportWizard'); // 替换为实际表名
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  UserImportWizardSheet.getRange('A2:K').clearContent();
  insertCSV(csv, ss);
}

function insertCSV(csv, ss) {
  const MasterCsvSheet = ss.getSheetByName('MasterCsv'); // 替换为实际表名
  MasterCsvSheet.getDataRange().clearContent();
  var CsvRange = MasterCsvSheet.getRange(1, 1, csv.length, csv[0].length);
  CsvRange.setValues(csv);
}

2. UI弹窗阻塞脚本执行 #

共享用户调用代码时，SpreadsheetApp.getUi().alert()会因权限或上下文问题无法正常显示，导致脚本挂起。

• 修复方案：移除所有alert，改用控制台日志调试，同时在HTML中添加错误处理：

// 修改Main.gs中的importCSV和insertCSV
function importCSV(e) {
  if (!e) {
    SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "Choose CSV");
    return;
  }
  console.log('csv read will start');
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const UserImportWizardSheet = ss.getSheetByName('UserImportWizard');
  const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString());
  console.log('csv read successful');
  UserImportWizardSheet.getRange('A2:K').clearContent();
  insertCSV(csv, ss);
}

function insertCSV(csv, ss) {
  console.log('insert csv started');
  const MasterCsvSheet = ss.getSheetByName('MasterCsv');
  MasterCsvSheet.getDataRange().clearContent();
  var CsvRange = MasterCsvSheet.getRange(1, 1, csv.length, csv[0].length);
  CsvRange.setValues(csv);
}

<!-- 修改index.html中的脚本 -->
<script>
  function importCSV(e) {
    const div = document.getElementById("progress");
    div.innerHTML = "Please wait while we process your file...";
    const file = e.file.files[0];
    const f = new FileReader();
    f.onload = d => {
      google.script.run
        .withSuccessHandler(() => google.script.host.close())
        .withFailureHandler(err => {
          div.innerHTML = `Error: ${err.message}`;
          console.error(err);
        })
        .importCSV([[...new Int8Array(d.target.result)], file.type, file.name]);
    };
    f.readAsArrayBuffer(file);
  }
</script>

3. 触发器调用UI方法导致失败 #

当共享用户通过WelcomeScreen的B4单元格触发onEditScript调用importCSV时，触发器运行在服务器端，无UI上下文，调用showModalDialog会直接报错。

• 修复方案：在importCSV中添加上下文判断，避免触发器调用UI：

function importCSV(e) {
  if (!e) {
    try {
      SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutputFromFile("index"), "Choose CSV");
    } catch (err) {
      console.error('Cannot show dialog from trigger:', err);
      SpreadsheetApp.getActiveSpreadsheet().toast('请通过顶部菜单的「importCSV」选项上传文件', '操作提示');
    }
    return;
  }
  // 后续逻辑...
}

4. CSV编码解析问题 #

若CSV文件非UTF-8编码，getDataAsString()会解析失败导致脚本卡住。

• 修复方案：指定编码解析：

const csv = Utilities.parseCsv(Utilities.newBlob(...e).getDataAsString('UTF-8'));

若文件为GBK等其他编码，替换为对应编码值即可。

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