首页 文章

Google App脚本导出CSV onEdit，而不是onOpen

提问于
浏览 次
0

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

在我当前的项目触发器下，我有我的事件运行onedit .

当工作表未打开时，如何使触发器触发？

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);
  }
}
javascript csv google-apps-script

1 回答

  • 0

    在获得上述评论的帮助并调整我的触发器后，我有一个解决方案 .

    这是脚本：

    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文件 .

    回复于

相关问题