Google Forms Responses to Sheets Transpose Issue

Question

I have been looking and looking for some guidance though the various topics discussed not only here but in other forums and I am beginning to think I am asking too much of Google Sheets.

I work for a police department and I have been tasked with creating a nearly bullet proof way for logging schedules through the use of Google Forms, so there is very little interaction with the spreadsheets they go to. My issue is the supervisor using the Form will fill out the shift roster for each daily shift using 1 form submission. What I would like to do is then take the 1 form submission on the table, break it up by assigned district and place it vertically so that I can run it through a calendar maker.

For the life of me, I can't seem to locate any help (maybe my search strings are wrong when I am looking) on how to take columns and stack them in the spreadsheet the way I need them.

Here is the spreadsheet- Shift Roster

Any help would be greatly appreciated!

Unknown Unknown · Accepted Answer · 2017-01-09T02:05:59

It seems difficult to re-shape existing data with spreadsheet formulas; one may end up with something like this

={
transpose(split(rept(FormSubmissions!A2&"|", 12), "|")),
transpose(split(rept(FormSubmissions!B2&"|", 12), "|")),
transpose(FormSubmissions!C1:N1), 
transpose(FormSubmissions!C2:N2);     
transpose(split(rept(FormSubmissions!A3&"|", 12), "|")),
transpose(split(rept(FormSubmissions!B3&"|", 12), "|")),
transpose(FormSubmissions!C1:N1),
transpose(FormSubmissions!C3:N3);    
transpose(split(rept(FormSubmissions!A4&"|", 12), "|")),
transpose(split(rept(FormSubmissions!B4&"|", 12), "|")),
transpose(FormSubmissions!C1:N1),
transpose(FormSubmissions!C4:N4)
}

where the formula has to be extended according to the number of rows in FormSubmissions (rows 2, 3, 4 are covered above). The number 12 is the number of entries C-N, so it ends up being hardcoded in the formula. Not a great option.

It's easier to reshape the data as it comes in from the form, with a script set to run on form submission (see: triggers). Here is a script one could use:

function reshapeData(e) {
  var sheet = e.range.getSheet();
  var headers = sheet.getRange('1:1').getValues()[0];
  var output = [];
  for (var k = 2; k < e.values.length; k++) {
    output.push([e.values[0], e.values[1], headers[k], e.values[k]]);
  }
  var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FormatForCalendar');
  var row = destination.getLastRow();
  destination.insertRowsAfter(row, output.length);
  destination.getRange(row, 1, output.length, output[0].length).setValues(output); 
}

The script gets column headers from the submissions sheet, but takes the values directly from the form. It then put them into FormatForCalendar sheet (at the bottom) in the specified format: timestamp, shift, the rest of information.

A drawback of this approach is that it does not support edits to form submissions. Also, once we are in the scripts territory, it may be easier to adjust the "calendar maker" to work with the data in the shape coming from this form.

Google Forms Responses to Sheets Transpose Issue

1 Answers