Google Forms – Map Specific Parts to Different Spreadsheets

google sheetsgoogle-apps-scriptgoogle-forms

Can I map specific parts of a Google Form to one spreadsheet, and other parts to another spreadsheet? Some of the responses on one will be public and viewable by anyone, but others will be private.

Best Answer

You can run a script that will catch the submitted responses, then add a row to the public spreadsheet with the appropriate data. If you need any help writing this, let me know.

Edit

Something like this should do it:

/**
 * This will 
 * set a trigger for this as described here: 
 * https://developers.google.com/apps-script/understanding_triggers#ActionTriggers
 * @param {Object} e The event parameter for form submission to a spreadsheet;
 *     see https://developers.google.com/apps-script/understanding_events
 */

function onSubmit(e){
  properties = {publicSheetId:'PUT YOUR PUBLIC SHEET HERE',
                publicFields:['Question 1', 'Question 2?']};
  var form = FormApp.getActiveForm();
  var responses = form.getResponses();
  Logger.log(responses);
  var lastResponse = responses[responses.length -1];
  var publicSheet = SpreadsheetApp.openById(properties.publicSheetId);
  //form = FormApp.getActiveForm();
  //e = FormApp.getActiveForm().getResponse()
  Logger.log(lastResponse.getId());
  var itemResponses = lastResponse.getItemResponses()
  var publicItemResponses = []
  for (var i = 0; i < properties.publicFields.length; i++){
    var publicField = properties.publicFields[i]
    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];
      Logger.log(itemResponse.getItem().getTitle());
      Logger.log(publicField);
      if(itemResponse.getItem().getTitle() == publicField){
        publicItemResponses.push(itemResponse.getResponse());
        break;
      }
    }
  }
  publicSheet.getSheets()[0].appendRow(publicItemResponses);
}
Related Topic