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.