Google-sheets – Converting multiple columns to list

google sheetsgoogle-sheets-arraysgoogle-sheets-query

I'm currently using Google Sheets and I'm capturing data with Google Forms.

What I'm receiving from Forms, inside the corresponding sheets is an entry with timestamp, clientID, item1, quantity1, item2, quantity2 (…itemN, quantityN). Where N at this time is 5, but I'm still solving how it happens for 1 and 2 before I add 3 through 5.

What I'm trying to do is merge the lists and quantity into a unified column(s)

Example

in:

timestampA, clientIDA, item1, quantity1, item2, quantity2... itemN, quantityN

timestampB, clientIDB, item1, quantity1, item2, quantity2... itemN, quantityN

out:

timestampA, clientIDA, item1, quantity1

timestampA, clientIDA, item2, quantity2

...

timestampA, clientIDA, itemN, quantityN

timestampB, clientIDB, item1, quantity1

timestampB, clientIDB, item2, quantity2

...

timestampB, clientIDB, itemN, quantityN

item1 and quantity1 are required, item2 through itemN and quantity2 through quantityN are not.

It's also important that I don't need to manually transpose as the data entering is continuous and I won't be monitoring it.

I tried to do it with pivots, but as item1 and item2 have different column names it won't aggregate. If I added a row on top and added new titles then execute the query below, it only calls 1 column (item1 or item2) not (item1 and item2) as they're not unique.

Does anyone have any idea how I can do this?

Best Answer

The solution was that I didn't need to unpivot, just reorder.

=query({'Order entry'!A1:E; {'Order entry'!A2:C, 'Order entry'!F2:G}},"SELECT * WHERE Col4 is not null AND Col5 is not null order by Col1")

Also added order by Col1 to sort by date.

Related Solutions

Google-sheets – Auto entering functions in sheets managed by forms

I have a sheet-bound script to generate and email templated documents populated with form response data. What you'll see below is definitely NOT pretty/efficient/possibly even advisable..? However, it does accomplish what you are attempting in this post.

It's more or less a sledgehammer grade function to pin a percentage on each submission based on yes/no/blank values for a variety of checksheet-style questions.

The trick - I think - to getting it in the appropriate row and not overwriting data is the placement. The following lines:

var calc = '=round(if(iserror(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")/(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")+countifs(INDIRECT("N"&row()&":BA"&row()),"=NO"))),"",countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")/(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")+countifs(INDIRECT("N"&row()&":BA"&row()),"=NO")))*100,1) & "%"';

var calcc = calc.toString();

var sheet = SpreadsheetApp.getActiveSheet();

var numRo = sheet.getLastRow()
sheet.getRange(numRo, 1).setValue(calcc);

precede the main function:

function onEdit() {... etc

which is triggered on a form submit.

Google Sheets – Organize Employee Time Log into a Table

This is probably best handled by a custom function. The function I wrote takes the data range (including the header row) as an argument and outputs the new table, with Ids and its header. Usage example: =inOut(A1:C20). It also checks data for consistency: the times must begin with In and then alternate between In and Out.

function inOut(arr) {
  var data = {};
  var maxLen = 0;
  for (var i = 1; i < arr.length; i++) {
    var id = arr[i][0];
    if (!data[id]) {
      data[id] = [];
    }
    var expectedType = (data[id].length % 2 ? 'Out' : 'In');
    if (arr[i][1] != expectedType) {
      throw new Error('Inconsistent data on line ' + i + ': expected ' + expectedType + ' and got ' + arr[i][1]);
    }
    data[id].push(arr[i][2]);
    if (data[id].length > maxLen) {
      maxLen = data[id].length;
    }
  }

  // End of data collection; proceed to form output

  var keys = Object.keys(data).sort();
  var headerRow = ['EmpId'];
  for (i = 0; i < maxLen; i++) {
    headerRow.push(i % 2 ? 'Out' : 'In');
  }
  var output = [headerRow];
  for (i = 0; i < keys.length; i++) {
    id = keys[i];
    var row = [id].concat(data[id]);
    while (row.length < maxLen + 1) {
      row.push('');
    }
    output.push(row);
  }
  return output;
}

Related Topic