CODEWITHSUNDEEP
formsgoogle-apps-script
ep7735
ep7735

Reputation: 1

Reformatting spreadsheet responses into a new tab on form submit

Here are my spreadsheet responses from a form: https://docs.google.com/spreadsheets/d/1a9H2HqAwl29IY6-aCvCKs12Xb3vDcZHCOoNugx81PTA/edit#gid=1939572907

The form data generates in the "raw data" tab of the above spreadsheet. However, I'd like to automatically rearrange the form responses in a different format on the "teacher list" tab of the spreadsheet on form submissions. We are trying to keep track of how often we visit a teacher's room and so want all of the timestamps to appear next to the teacher's name.

I do not know if I should be using formulas or a script to get the job done.

To show you our end goal, I have two form submissions that I have typed into the cells where'd we like them to appear on the "teacher list" tab.

Any suggestions or resources to help me accomplish this would be very much appreciated!

Upvotes: 0

Views: 79

Answers (1)

Shyam Kansagra
Shyam Kansagra

Reputation: 912

This should give you a good start. And, I have removed the merging of the cells in G column in teacher list tab.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Raw Data');
  var data = sheet.getDataRange().getValues();

  var formatSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Teacher List');
  var formatData = formatSheet.getDataRange().getValues();

  var name = data[sheet.getLastRow()-1][2];
  var flag = 0, index;

  for(var i=1; i<formatData.length; i++)
  {
    if(name == formatData[i][0])
    {
      flag = 1;
      index = i;
      break;
    }
  }

  if(flag == 1)
  {
    for(var i=1; i<=5; i++)
    {
      if(formatData[index][i] == "")
      {
        formatSheet.getRange(index+1, i+1).setValue(data[sheet.getLastRow()-1][0]);
        formatSheet.getRange(index+1, 7).setValue(formatData[index][6].concat('; '+data[sheet.getLastRow()-1][3]));
        break;
      }
    }
  }
}

But is there more than 5 visits possible? Is first column of teacher list tab is going to remian same throughout? Do you want to add new row if no match is found for 'Teacher or PLC Observed' from Raw Data with first column of Teacher List tab? If answer to these questions is positive, you need to tweak a code little bit, try it. I'll help if you're stuck.

Edit: Please set the appscript trigger as: From form -> onSubmit.

Upvotes: 1

Related Questions