Import data automatically into a proper report on Google Sheets

Question

I am feeling this is a long shot, but I found an answer to a previous issue that I felt it was complicated, so I'll give it a shot of asking the following.

Each month, I work manually on a report to get the time people spent working the previous month (we use a time tracking tool).

Thanks to this post I have managed to export a report that had the name/project/duration per project into CSV and convert it to a single row per person and total duration.

However, what I am not really sure on how to do it or if it's even possible is, how can I import that into a different sheet that is divided by teams in each tab?

For example, the export and the duration totals look like this, and the report where I need to import the times look like this. (Included format of tables below for clarity/in case sheets are deleted).

Sheet: Exported CSV Tab: June

|Organization|  Time Zone|  Member          |Time    |
|------------|-----------|------------------|--------|
|ABC         |UTC        |Juanito Cruz      |15:20:30|
|ABC         |UTC        |Alberto Ramirez   |5:30:55 |
|ABC         |UTC        |Nicole Estevez    |9:25:00 |
|ABC         |UTC        |Mafalda Dominga   |20:00   |
|ABC         |UTC        |Telesforo Jacinto |16:20:01|
|ABC         |UTC        |Ainara Kevin      |10:09:25|
|ABC         |UTC        |Selena Rafael     |5:00:00 |
|ABC         |UTC        |Eugenio Sandalio  |6:23:40 |
|ABC         |UTC        |José Luis Graciano|8:00:00 |

Sheet: Monthly Report Tab: Team 1

|Name   |       |January|February |March |April |May |June |July |August |September |October |November |December|
|-------|-------|-------|---------|------|------|----|-----|-----|-------|----------|--------|---------|--------|
|Juanito|Cruz   |       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:|       |   0   |      0  |0     |  0   |   0|  0  |   0 |  0    |    0     |     0  |  0      |    0   |
|Alberto|Ramirez|       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:|       |  0    |      0  |0     |   0  |   0|  0  |   0 |  0    |    0     |     0  |   0     |   0    |
|Nicole |Estevez|       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:|       |  0    |      0  |0     |   0  |   0|  0  |   0 |  0    |    0     |     0  |   0     |   0    |

Sheet: Monthly Report Tab: Team 2

|Name   |       |January|February |March |April |May |June |July |August |September |October |November |December|
|-------|-------|-------|---------|------|------|----|-----|-----|-------|----------|--------|---------|--------|
|Mafalda|Dominga|       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:|       |   0   |      0  |0     |  0   |   0|  0  |   0 |  0    |    0     |     0  |  0      |    0   |
|Telesfo|Jacinto|       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:|       |  0    |      0  |0     |   0  |   0|  0  |   0 |  0    |    0     |     0  |   0     |   0    |
|Ainara |Kevin  |       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:|       |  0    |      0  |0     |   0  |   0|  0  |   0 |  0    |    0     |     0  |   0     |   0    |

Sheet: Monthly Report Tab: Team 3

|Name     |        |January|February |March |April |May |June |July |August |September |October |November |December|
|-------  |--------|-------|---------|------|------|----|-----|-----|-------|----------|--------|---------|--------|
|Selena   |Rafael  |       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:  |        |   0   |       0 |0     |   0  |   0|  0  |   0 |  0    |    0     |     0  |   0     | 0      |
|Eugenio  |Sandalio|       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:  |        |  0    |      0  |0     |   0  |   0|  0  |   0 |  0    |    0     |     0  |   0     |   0    |
|Jose Luis|Graciano|       |         |      |      |    |     |     |       |          |        |         |        |
| Hours:  |        |  0    |      0  |0     |   0  |   0|  0  |   0 |  0    |    0     |     0  |   0     |   0    |

I gather that I might need to change things like the names, as you can see there, in the monthly report, the name and last name is on a different column but in the exported report, it shows full name on one column.

In the Exported CSV, the tab name is the month I am looking to import, so in Monthly Report sheet, the times should be imported on column H.

Also, if it's easier, I would not mind adding the exported report in the monthly report workbook if it's possible to delete it after the data has been imported.

Thanks in advance, and apologies if this was something more simple than I could see.

Edit: Included sheets format here for context in case they are removed or not accessible and removed google-apps-script tag.

Edit 2: Fixed format of tables.

Answer 1

From the question:

However, what I am not really sure on how to do it or if it's even possible is, how can I import that into a different sheet that is divided by teams in each tab?

Google Sheets built-in import feature doesn't do what you are looking for, so you will have to do someway is:

1. import/read the data
   This could be done using the Google Sheets built-in import feature but it will be added to a new sheet
2. transform the data into the shape that you need
3. put the data in to the corresponding place

For 2 and 3 you could find formulas and the Google Sheets array handling feature vey handy. One way to do these steps is to merge the data newly imported data with the old data from the destination sheet then replace overwrite the data on the destination sheet.

One way among others to automate the above is to use Google Apps Script but you should know a bit about handling arrays using JavaScript .

Resources

• Using arrays in Google Sheets
• https://developers.google.com/apps-script/guides/sheets