Google-sheets – Automatically creating sheets across different Google Spreadsheets

google sheetsgoogle-forms

I have one spreadsheet that collects the Google form data that is submitted. I then created spreadsheets titled "A-D", "E-H", "I-L", and so on to create the alphabet.

I would like students to use Google form to apply for a mentoring position. After submitting the form I would like the system to automatically create a sheet within the specific spreadsheet that corresponds to the first initial of their first name. I would also like the sheet to automatically be title as, "first name", "last initial".

Is this something that can be done through Google?

I hope my plan made sense!

Best Answer

This can be done with the following Google Apps Script. It should be added to the spreadsheet that receives form submissions (go to Tools > Script Editor). After entering the code (including the URLs of the spreadsheets A-D, E-L, etc), go to Resources > Current Project's Triggets and add a trigger "createSheet | From Spreadsheet | On form submit". 

function createSheet(e) {
  var firstName = e.namedValues['First Name'];
  var firstInitial = firstName.slice(0,1).toUpperCase();
  var lastName = e.namedValues['Last Name'];
  var lastInitial = lastName.slice(0,1).toUpperCase();
  var ss;
  if (firstInitial >= 'A' && firstInitial <= 'D') {
    ss = SpreadsheetApp.openByUrl('url of your A-D sheet here');
  }
  if (firstInitial >= 'E' && firstInitial <= 'L') {
    ss = SpreadsheetApp.openByUrl('url of your E-L sheet here');
  }
  // ... and so on  
  ss.insertSheet(firstName + ',' + lastInitial);
}

The script assumes that your form has fields "First Name" and "Last Name". If these are named differently, change these strings in the code.

If the same student submits the form twice (or you have multiple students with the same First name + Last initial combination), the script will fail to create a new sheet, since one by that name already exists.

Related Topic