Google App Script导出CSV onEdit， not onOpen

Question

我创建了一个表单，将所有字段数据填充到google表单中，我有一个脚本，每次编辑电子表格时，它都会将电子表格导出到csv。但是，脚本仅在图纸打开并在图纸上编辑时运行。如果我提交编辑电子表格的表单，则不会触发触发器，也不会创建csv。

在当前项目触发器下，我有要运行OneEdit的事件。

当床单未打开时，如何使触发器触发？

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Download Data",
    functionName : "saveAsCSV"
  }];
  sheet.addMenu("Script Center Menu", entries);
};

function saveAsCSV() {
   // Trash the previous csv file that was last updated.
 var files = DriveApp.getFilesByName('myCSVFile.csv');
 while (files.hasNext()) {
   var file = files.next();
   if (file.getLastUpdated()) {
     file.setTrashed(true);
   }
 }
  // Creates the file name
  var fileName = ("myCSVFile");
  // Check that the file name entered wasn't empty
  if (fileName.length !== 0) {
    // Add the ".csv" extension to the file name
    fileName = fileName + ".csv";
    // Convert the range data to CSV format
    var csvFile = convertRangeToCsvFile_(fileName);
    // Create a file in the Docs List with the given name and the CSV data
    DriveApp.createFile(fileName, csvFile);
  }
  else {
    Browser.msgBox("Error: Please enter a CSV file name.");
  }
}

function convertRangeToCsvFile_(csvFileName) {
  try {
    var csvFile = undefined;

    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var data = rows.getValues();

    // Loop through the data in the range and build a string with the CSV data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // Join each row's columns
        // Add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

Answer 1

从上面的评论中得到帮助并调整触发器后，我有了一个解决方案。

以下是脚本：

function onEdit() {
  var sheet = SpreadsheetApp.openById("id");
  var entries = [{
    name : "Download Data",
    functionName : "saveAsCSV"
  }];
  sheet.addMenu("Script Center Menu", entries);
};

function saveAsCSV() {
   // Trash the previous csv file that was last updated.
 var files = DriveApp.getFilesByName('myCSVFile.csv');
 while (files.hasNext()) {
   var file = files.next();
   if (file.getLastUpdated()) {
     file.setTrashed(true);
   }
 }
  // Creates the file name
  var fileName = ("myCSVFile");
  // Check that the file name entered wasn't empty
  if (fileName.length !== 0) {
    // Add the ".csv" extension to the file name
    fileName = fileName + ".csv";
    // Convert the range data to CSV format
    var csvFile = convertRangeToCsvFile_(fileName);
    // Create a file in the Docs List with the given name and the CSV data
    DriveApp.createFile(fileName, csvFile);
  }
  else {
    Browser.msgBox("Error: Please enter a CSV file name.");
  }
}

function convertRangeToCsvFile_(csvFileName) {
  try {
    var csvFile = undefined;

    var sheet = SpreadsheetApp.getActiveSheet();
    var rows = sheet.getDataRange();
    var numRows = rows.getNumRows();
    var data = rows.getValues();

    // Loop through the data in the range and build a string with the CSV data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // Join each row's columns
        // Add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

保存脚本文件后，我需要将触发器从onEdit更改为定时触发器。资源

现在，当工作表关闭时，该函数将每分钟运行并创建一个新的CSV文件。