CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jetatwork
jetatwork

Reputation: 43

Export spreadsheet or CSV text file from Google Drive Forms using script?

We have a Google Form that saves its submits to a Google Spreadsheet.

Using the Script manager, how do we export that spreadsheet contents or the latest form submit message to a local Excel spreadsheet or tab-delimited text file on my harddrive?

This would be a 2 step process:

  1. catch form submit or spreadsheet change event and externalize the data into the cloud Drive.
  2. place that data on my local hard drive.

How do we do #1 and/or #2, using the Google spreadsheet script?

We have created a OnFormSubmit callback function on the spreadsheet, but we can only log the event message (the form submitted data) to a popup window in Drive.

function OnFormSubmit(e) {

  var sheet = SpreadsheetApp.getActiveSheet();
  var row =  SpreadsheetApp.getActiveSheet().getLastRow();

  var newMessage = e;

  Logger.log(newMessage);
}

There is a CreateFile method, but its not clear how I could use that.

Upvotes: 1

Views: 7228

Answers (1)

faulk86
faulk86

Reputation: 36

I was able to use the code below to create a tab delimited text file on my Google Drive that's a copy of the original Google spreadsheet that collects the data from the form.

The code is triggered on the Google spreadsheet change event. This allows the code to run when a new form is submitted, but it also will update the text file on any change event in the spreadsheet. This allows you to edit existing entries.

Once the file is on your drive, a simple scheduled batch file can copy the file to any location outside your google drive.

function saveAsTabDelimitedTextFile() {
  // get Spreadsheet Name
  var fileName = SpreadsheetApp.getActiveSheet().getSheetName();

  // Add the ".txt" extension to the file name
  fileName = fileName + ".txt";

  // Convert the range data to tab-delimited format
  var txtFile = convertRangeToTxtFile_(fileName);

  // Delete existing file
  deleteDocByName(fileName);

  // Create a file in the Docs List with the given name and the data
  DocsList.createFile(fileName, txtFile);
}


function convertRangeToTxtFile_(txtFileName) {
  try {
    var txtFile = undefined;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var rows = sheet.getDataRange();
    var data = rows.getValues();

    // Loop through the data in the range and build a string with the data
    if (data.length > 1) {
      var txt = "";
      for (var row = 0; row < data.length; row++) {
        // Join each row's columns and add a carriage return to end of each row
        txt += data[row].join("\t") + "\r\n";
      }
      txtFile = txt;
    }
    return txtFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

Upvotes: 2

Related Questions