Organize employee time log into a table with a row for each employee

Question

I want to get all the Timestamp of an employee (EmpID) from one sheet to another using Google apps script or otherwise.

Explanation

In Google Sheets, Sheet1 has the following items in 3 columns | EmpID | In/Out | Timestamp | | 100 | In | 6:00 | | 200 | In | 6:01 | | 100 | Out | 7:03 | | 100 | In | 7:20 | | 200 | Out | 14:02 | | 100 | Out | 14:05 |

I have another sheet, Sheet2 where I have the output of function =unique() of the first column. I want the rest of the columns to be filled using Google app script | EmpID | In | Out | In | Out | In | Out | | 100 | 6:00 | 7:03 | 7:20 | 14:05| | | | 200 | 6:01 | 14:02| | | | |

I'm guessing that the value of the first column (100) will go through a for loop and lookup the range in Sheet1. Whenever the value 100 occurs in the range it copies the timestamp value to Sheet2. While doing so if the cell is not empty it copies it to the next column.

In the above example the EmpID 100 occurs 4 times hence 4 columns need to be filled. For EmpID 101 only 2 columns need to be filled.

I tried using =Transpose(Query(Sheet1!A2:C7,"SELECT C WHERE A='100' ",1)) but it shows only one value 6:00 instead of four values.

Answer 1

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;
}

Answer 2

I solved this by using a simple query function (I added it as an edit in my question originally):

query(Sheet1!$A$2:$C7,"Select C Where (A="&A2&")",-1)

This basically queries Sheet1 Range A2:C7 with the value of column A in Sheet1 matching A2 ("&A2&") in Sheet2 and wherever it matches in column A, it selects the value from column C.

Also the -1 at the end of this query lets google decide if the content has a header or not. In this case it decided there was no header. More info.

The problem was that the results were showing up vertically. I used transpose to make it horizontal. Now the formula on Sheet2, cell B2 looks like this:

transpose(query(Sheet1!$A$2:$C7,"Select C Where (A="&A2&")",-1))

The results show up exactly the way I intended it to.