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 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.)
  • And 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.

Please give me a hint. Thank you.

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);
  }
}
5
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Sep 20 at 18:32
  • Welcome to Web Applications. The question needs more focus. Are you looking to send the form submission as they are submitted or all at once? It's important that you clarify if you are trying to use a form submit trigger and the steps that you are following to create it. P.S. Add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Sep 20 at 19:29
  • 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 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..
    – Rubén
    Sep 20 at 20:10
  • Related 1, 2, 3, 4
    – Rubén
    Sep 20 at 20:11
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, more specifically with the Array global object and it's methods.

On the script that is contained by your spreadsheet that is linked to your form instead on 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 whick has an object having a property for each unique question title. This property has an array of values of all the question having the same title.

function respondToFormSubmit(e){
  switch(e.namedValues['Department'][0]){
    case 'Department 1':
      var spreadsheetId = 'put_here_the_spreadsheet_id_1';
    break;
    case 'Department 2':
      var spreadsheetId = 'put_here_the_spreadsheet_id_2';
    break;
    default:
      return;
  }
  appendRow('put_here_the_spreadsheet_id_1',e.values);
}

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 by 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 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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