0

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);
  }
}
3
  • Thank you, @Rubén! Yes, I need the google form to send data each time a user gives feedback. This is already happening - a new row is added in the linked results spreadsheet. But also I want the data to be moved to particular locations where an admin can see the data for a single department. And yes, this must happen each time a new form submit is done. I expect there I will have a custom script - but not sure if it will be linked to the form or to the spreadsheet or both. I am very new with all of these.
    – GhitaB
    Sep 20, 2021 at 19:36
  • The onEdit simple trigger is not triggered when a form submission occurs, instead you should use form submit installable trigger. Since you already have linked the form to you the spreadsheet, the easier way to proceed is to add the code to the spreadsheet.. Sep 20, 2021 at 20:10
  • Related 1, 2, 3, 4 Sep 20, 2021 at 20:11

1 Answer 1

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

1
  • I confirm it works. Thank you very much!
    – GhitaB
    Sep 21, 2021 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.