谷歌表单-如何将数据收集到不同的表单

Question

我有一个谷歌表单，它将数据收集到电子表格中（表单响应1）。因为我认为有4m行的限制，所以如何以编程方式创建新的工作表并将响应存储到新创建的工作表中。

注意：这不是将响应复制到新工作表中。

Answer 1

对于“新工作表”，电子表格中的大小限制约为200万个单元格（而不是400万行）。参考

窗体应用脚本参考中提供了创建新电子表格并将其设置为窗体目标的示例：

// 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());

该脚本的这种更复杂的演变可以在达到一些预先设定的目标（例如每100个响应）时自动创建一个新文件，并生成符合谷歌表单命名约定的新文件名：

/**
 * 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;
}

不过有个问题。将电子表格设置为表单的目标时，所有现有响应都会复制到新目标中。如果您担心目标电子表格中的空间不足，那么这显然是没有帮助的。

正因为如此，你不想要的答案——把答案复制到另一张纸上——才是你最好的选择。