谷歌应用程序脚本导出CSV onEdit,而不是onOpen

Google App Script Export CSV onEdit, not onOpen

本文关键字:onOpen onEdit CSV 应用程序 脚本 谷歌      更新时间:2023-09-26

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

在我当前的项目触发器下,我有我的事件来运行。

当床单未打开时,我如何使触发器触发?

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);
  }
}

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

脚本如下:

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更改为定时触发器。我删除了当前的saveAsCSV函数并创建了一个新的。然后选择时间驱动,分计时器,每分钟。

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