Google App Script Export CSV onEdit, not onOpen

Question

I've created a form that populates all field data into a google form and I have a script that will export the spreadsheet to a csv every time the spreadsheet is edited. But, the script only runs when the sheet is open and is edited on the sheet. If I submit my form that edits the spread sheet, the trigger doesn't fire and the csv is not created.

Under my current project triggers I have my event to run onedit.

How can I make my triggers fire when the sheet is not open?

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

Will this work even if this is an external form? Not a google form? — cgrouge
I'll try changing var sheet = SpreadsheetApp.getActiveSpreadsheet(); for openById — Harold
@Harold I wouldn't need it to be open. I need the sheet to update automatically and the trigger to fire when the sheet is not open. — cgrouge
@iJay I wrote the function for form submit and did not see any results to fire the trigger. — cgrouge

cgrouge cgrouge · Accepted Answer · 2016-08-23T22:08:06

After getting help from comments above and adjust my triggers I have a solution.

Here is the script:

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

After saving the script file I needed to change my trigger from onEdit to a timed trigger. Resources > all your triggers I deleted the current saveAsCSV function and created a new one. Then selected time-driven, minutes timer, every minute.

Now when the sheet is closed, the function will run and create a new CSV file every minute.

Google App Script Export CSV onEdit, not onOpen

1 Answers