google form - how to collect data to different sheet

Question

I have a google form which collects data into a spreadsheet (form response 1). Because I think there is a limit of 4m rows, how can I programmatically create a new sheet and store the response into the newly created sheet.

Note: this is not copying the response into new sheet.

Mogsdad Mogsdad · Accepted Answer · 2014-11-26T04:36:04

With "New Sheets", the size limit is approximately 2 million cells in a Spreadsheet (not 4 million rows). Reference.

An example of creating a new Spreadsheet and setting it as a form destination is provided in the Forms Apps Script Reference:

// Open a form by ID and create a new spreadsheet.
 var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
 var ss = SpreadsheetApp.create('Spreadsheet Name');

 // Update the form's response destination.
 form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());

This more sophisticated evolution of that script can automatically create a new file when some pre-set goal is met (e.g. every 100 responses), and generate new file names consistent with Google Form naming conventions:

/**
 * Check number of responses and switch to new destination if needed.
 * Set up as a trigger function to run on Form submission.
 */
function checkResponseCount(e) {
  var form = e.source;
  var numResponses = form.getResponses().length;

  // Start a new destination spreadsheet after each 100 responses
  if (numResponses % 100 == 0) {
    startNewDestinationSpreadsheet();
  }
}

/**
 * Google Form script to start a new spreadsheet in the user's "root"
 * directory, for the form's responses.
 *
 * If no new name is provided, it will be automatically created based on the
 * name of the current spreadsheet destination, by incrementing the counter
 * in a standard destination name, e.g. "My Form (Responses 5)".
 *
 * @param {string}    OPTIONAL New spreadsheet name.
 *
 * @returns {string}           ID of new spreadsheet
 */
function startNewDestinationSpreadsheet(ssName) {
  var form = FormApp.getActiveForm();
  var curDest = DriveApp.getFileById(form.getDestinationId());
  var curName = curDest.getName();

  // Expect that current name is either in standard form (ENGLISH - adjust as needed)
  // or that a new name has been provided as ssName.
  if (!ssName || ssName === '') {
    if (curName.search(/\(Responses.*\)/) == -1) {
      ssName = curName+" (Responses 2)";
    }
    else if (curName.indexOf("(Responses)") !== -1) {
      ssName = curName.replace("Responses","Responses 2");
    }
    else {
      // Increment number
      ssName = curName.replace(/(\d+)+/g, function(match, number) {
         return parseInt(number)+1;
      });
    }
  }

  // Create new spreadsheet, and set as form destination
  var newSheetId = SpreadsheetApp.create(ssName).getId();
  form.setDestination(FormApp.DestinationType.SPREADSHEET, newSheetId);

  // The new destination just had ALL existing responses copied to it - wipe them
  Utilities.sleep(10000); // Wait for sheet to be updated
  var sheet = SpreadsheetApp.openById(newSheetId).getActiveSheet();
  sheet.deleteRows(2, sheet.getMaxRows()-2);  // Cannot delete all non-frozen rows, so leave one

  return newSheetId;
}

Caveat Emptor

There's a problem, though. When you set a spreadsheet as a destination for a form, all the existing responses get copied into the new destination. If your concern is that you'll run out of space in your destination spreadsheet, then this is obviously not going to help.

Because of that, the answer you don't want - copy the responses to another sheet - is really your best option.

google form - how to collect data to different sheet

1 Answers

Caveat Emptor