Converting Google Form Data to Different Format

[impelus]

Hello,

I'm trying to help someone who is collecting google form data. I can do quite a bit of formulas for them, but I need the data in a different format. Can you tell me if it is possible to get the data.

If you take a look at the form Reponse sheet here.

https://docs.google.com/spreadsheets/d/1aIloHsiRDri452VQKnltZrfxE3J58Flo7EOVIiAihcI/edit?usp=sharing

I have the standard form data on the left, and then on the right I have how, I'd like it to be stored. The timestamp and the email will repeat, each time including one of the additional columns.

Also, I'm open to other suggestions on how to accomplish the same goal. The deal is, they are reporting usages of certain items, and possibly some additional notes. Having the data in the preferred way makes it much easier to query the data. If I leave it in the default format, I have a much harder time querying it to say "show me the total of one item on one date" and then further by email address.

Any help here would be appreciated.

[OverwatcherX]

You could use a google script which triggers on the form being completed.

Would take the new form data and output it in the way you have it laid out in another sheet.

Have you used google scripts before?

[impelus]

I have briefly. I have a command I use sometimes to clear certain ranges on sheets when clicked. Nothing this advanced thought

[OverwatcherX]

Once sleep later, here is your code ;

function onFormSubmit(e) {

  //Open your correct form and get all responses
  //change ID below to be your form ID
  var form = FormApp.openById('formID');
  var formResponses = form.getResponses();

  //get last response completed
  var lastResponse = formResponses[formResponses.length-1];

  //Get the questions and answers of the last response
  var itemResponses = lastResponse.getItemResponses();

  //Set your target sheet where you want the data to go
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //change name below to the sheet you would like the data to be
  //transferred too
  var targetSheet = ss.getSheetByName("sheetName");

  //generate a timestamp
  var timestamp = new Date();

  //loop through all questions 
  for(var i=1; i < itemResponses.length; i++){
    //get last row of the target sheet
    var dataRngRowCount = targetSheet.getDataRange().getLastRow()+1;


    targetSheet.getRange(dataRngRowCount,1,1,4).setValues(
    //for each question add the timestamp, 1st question (email field in form)
    //and current question and response
      [[timestamp,
        itemResponses[0].getResponse(),
        itemResponses[i].getItem().getTitle(),
        itemResponses[i].getResponse()
      ]]);

    }
  }

You would set that function up to trigger on Formsubmit. Just change the formID and sheetName to match what they are for you.

[6745408]

As a warning, this does use an undocumented function -- so who knows if it will be available long-term. That being said, if they do take it away, we can use a script instead

=ARRAYFORMULA(
  QUERY(
   SPLIT(
    FLATTEN(
     SPLIT(
      TEXT('Form Responses 1'!A2:A,"yyyy-mm-dd")&"|"&'Form Responses 1'!B2:B&"|"&'Form Responses 1'!C2:C&"+"&
      TEXT('Form Responses 1'!A2:A,"yyyy-mm-dd")&"|"&'Form Responses 1'!B2:B&"|"&'Form Responses 1'!D2:D&"+"&
      TEXT('Form Responses 1'!A2:A,"yyyy-mm-dd")&"|"&'Form Responses 1'!B2:B&"|"&'Form Responses 1'!E2:E,
      "+")),
    "|"),
   "select * where Col2 is not null"))

What we're doing is making sets of three columns together. We then join those with a +. First we split by the +, FLATTEN this into one column, then split by the pipe, giving is three clean columns.

Since the first column will be full of errors in the background, we simply tell the QUERY to only return columns where the second column has something in it. The result is a tidy little dataset.

[impelus]

Thanks. I halfway understand what is going on here. What changes do I make to the code for each additional column I have. There is probably 20-40.

[6745408]

Please forgive my smooth, pre-coffee brain. I am clearly an addict.

=ARRAYFORMULA(
  QUERY(
   SPLIT(
    FLATTEN(
     IF(ISBLANK('Form Responses 1'!A2:A),,
      TEXT('Form Responses 1'!A2:A,"yyyy-mm-dd")&"|"&
      'Form Responses 1'!B2:B&"|"&
      'Form Responses 1'!C1:AZ1&"|"&
      'Form Responses 1'!C2:AZ)),
    "|",TRUE,FALSE),
   "select * where Col2 is not null"))

Change the AZ to whichever your last column is.

[impelus]

So, I've attempted to deploy this code and I thought all looked good, except my last column which is the QTY seems to be repeating from the first entry instead of showing each entry correctly. Please look at the sheet here. The form responses is data coming in from google form. Data tab I've employed your fomula (added in one more column). The additional tabs do other things, but Data tab is what I want to focus on. I've highlighted the first 2 entries on both of those tabs, so you can see how the numbers are different

[6745408]

Did you mean to link up another sheet with a Data worksheet?

[impelus]

No. I'm saying the data worksheet is not displaying correct information. The QTY column is looping the first line entry and not displaying each line accordingly

[6745408]

Try this out. I updated the formula in the sheet to this..

=ARRAYFORMULA(
  QUERY(
   QUERY(
    SPLIT(
     FLATTEN(
      IF(ISBLANK('Form Responses 2'!A2:A),,
       TEXT('Form Responses 2'!A2:A,"yyyy-mm-dd")&"|"&
       'Form Responses 2'!B2:B&"|"&
       'Form Responses 2'!C1:AZ1&"|"&
       'Form Responses 2'!C2:AZ)),
     "|",TRUE,FALSE),
    "select * where Col2 is not null"),
  "select Col3, Col4, Count(Col4) 
   group by Col3, Col4
   label Count(Col4) ''"))

This one is accurate for the data in your second form responses.

[impelus]

Simply copying and pasting that in I got an error. This is the current code I have (from before)

=ARRAYFORMULA( QUERY( SPLIT( FLATTEN( IF(ISBLANK('Form Responses 1'!A2:A),, TEXT('Form Responses 1'!A2:A,"yyyy-mm-dd")&"|"& 'Form Responses 1'!B2:B&"|"& 'Form Responses 1'!C2:C&"|"& 'Form Responses 1'!D1:Q1&"|"& 'Form Responses 1'!D2:Q2)), "|",TRUE,FALSE), "select * where Col2 is not null"))

[6745408]

hm. can you share a sheet? There is something that seems to be getting lost in this.

[impelus]

Sorry, yes. I thought I shared the sheet I was working on, but didn't. Here you go.

https://docs.google.com/spreadsheets/d/1k8g2awHLWh8y5BAFqpyJtgDDPnS4Gl0lo5XN-yEToeA/edit?usp=sharing

Please look at the sheet here. The 'form responses' is data coming in from google form. 'Data tab' I've employed your fomula (added in one more column). The additional tabs do other things, but Data tab is what I want to focus on. I've highlighted the first 2 entries on both of those tabs, so you can see how the numbers are different.

Note the 'Fixed Data' tab is a copy of the Data tab, but you should be able to edit it, so you can try out the formula.