0

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.

2
  • Welcome to Web Applications. While links to sample spreadsheets might be helpful questions on this site should be self contained. One of the reasons for this is that the external content could be retired and make the question unclear. This is important because questions should be helpful to a wide audience not only to the direct participants in the Q/A. By the other hand, the question includes google-apps-script but it doesn't mention how it is used. If you want to use Google Apps Script, please show what you tried to use it.
    – Rubén
    Aug 26 at 16:05
  • 1
    Thank you for the clarification. I have included the sheets in the post itself and left the links to the sheets. I also would like to mentioned that I left the google-apps-script tag by mistake, not sure why I didn't delete it. I did try to find a way on how to do it or if it was possible using google apps scripts but couldn't find anything specific on it or something that I could wrap my head around. Thanks!
    – Jorge Diaz
    Aug 26 at 17:00
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.