Google form save responses in multiple files based on condition on submit

Question

I have this problem:

• A Google Form that saves data into one file (spreadsheet)
• Multiple users (admins) that access this file (all of them can see all rows)
• Let's say the first column is called Department, and there are rows like ("Department A", "User input related to department A"), ("Department B", "User input related to department B"), etc.
• So, for the moment, any admin can see all rows for all departments. (This is my problem.)
• I want the admin to see only the information related to a specific department.

Can you suggest me a solution?

I'm trying to find a way to send data from Google form to multiple spreadsheets (I mean multiple different files I can share later, one by one).

Or to create a script to copy from Google Sheets file to other files information for a single department.

And I'm trying to use only the script editors for sheets and forms, and I am very confused.

Update:

• I found how to add a script that runs on editing the spreadsheet. Also, I found how to move a row in a new tab in the same file.

In Tools - Script Editor:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(r.getValue() == "somevaluehere") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("NewSheet");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Answer 1

You will need to get familiar with the basics of the Google Apps Script platform, in this case, more specifically with installable triggers, so please read https://developers.google.com/apps-script/guides/triggers and https://developers.google.com/apps-script/guides/triggers/installable. Also, you should get familiar with the JavaScript basics, specifically the Array global object and its methods.

On the script contained by your spreadsheet linked to your form, instead of the onEdit simple trigger, you have to create a function to be called by a form submit installable trigger. This function might take advantage of the form submit event object for spreadsheets as it contains the namedValues property, which has an object having a property for each unique question title. This property has an array of values of all the questions having the same title.

/**
 * Function to be called from an installable trigger. 
 * The event source should be a spreadsheet; the type should be on form submit.
 *
 * @param {Object} e Spreadsheet on form submit event object.
 */
function respondToFormSubmit(e){
  let spreadsheetId;
  switch(e.namedValues['Department'][0]){
    case 'Department 1':
      spreadsheetId = 'put_here_the_spreadsheet_id_1';
    break;
    case 'Department 2':
      spreadsheetId = 'put_here_the_spreadsheet_id_2';
    break;
    default:
      return;
  }
  appendRow(spreadsheetId,e.values);
}
/**
 * 
 * Appends values in a row to the Form Responses sheet of
 * the specified spreadsheet
 *
 * @param {string} spreadsheetId Spreadsheet Id of the target spreadsheet
 * @param {string|number|Boolean|Date[]} rowContent An Array of values of type string, number, Boolean and Date objects
 */
function appendRow(spreadsheetId,rowContent){
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName('Form Responses');
  sheet.appendRow(rowContent);
}

Note:

1. Don't forget to create the installable trigger on the script contained in the spreadsheet. It should call the respondToFormSubmit function.
2. The respondToFormSubmit function should not be directly executed as it requires the form submit event object.

Related

• How do you create an installable trigger for a Google Apps Script?