Creating a report from google sheet data (issue)

[Guillaume_D]

What are you trying to achieve?

Hello, I’m working on automating a process to generate monthly reports on employee movements (new hires and departures) for two entities within the company group.

Current Process:
Every month, I extract raw data from our ERP system, which contains all employee movements. I then manually filter and structure this data into two separate reports—one for each entity.

Desired Output (Excel Report Format):
The report starts with general information (Name, Date, Entity) in rows 1-3.
The section for New Employees begins in row 5, with a header followed by the list of new hires.
After the last new hire, there should be two empty rows before starting the section for Exit Employees, which also has a header followed by the list of departures.
The report should be clean and structured, making it easy to review and share.

Goal:
I want to fully automate this process so that the system:
Checks the entity (to generate separate reports for each)
Identifies new hires and departures
Places them in the correct sections with the right structure

Steps taken so far

I’ve uploaded the raw data into a Google Sheet and created a process with routers to identify the entity of the group and whether the employee is a new hire or an exit employee. I’ve set up two Google Sheets, one for each entity, and within each sheet, there are two tabs: one for new employees and one for exit employees.

However, I’m now facing a challenge. I can’t figure out how to automatically either:

• Generate the report from a template that will automatically populate the data into the right rows in each sheet (i.e., filling in new employees in the “new employees” section and exit employees in the “exit employees” section 2 rows after).
• Or, create a new sheet within each entity-specific Google Sheet to compile the report with the correct data and structure.

Could anyone suggest how I can proceed with automating this? (I’m a beginner with a general understanding of the concepts, but I’m not very experienced with the tools, so I might have missed a module or feature that could help.)

Thanks a lot!

Screenshots: scenario setup, module configuration, errors

[momomomonmon]

@Guillaume_D
Hi, You are planning a great deal of automation. That’s great.
I can see from the text that you are working very carefully.

Steps

I think roughly the following steps need to be taken I will use the format Service Name - Module to be used: Execution details.

1. Spreadsheet - Create a sheet from Template: Create a sheet for the report you have screenshot.
2. Unknown Module: Count the number of exit employees of the month.
3. Unknown Module: Count the number of New employees of the month.
4. Tools - Set multiple Variables: Create an Index from the counts in Steps 2 and 3 (Index to be used in Step.5)
5. Spreadsheet - Make an API Call: add rows to Spreadsheet using API (Endpoint: /spreadsheets/SPREADSHEET_ID:batchUpdate)
6. Spreadsheet - Update a Row: Add the information of the person joining and leaving the company to the row added in #5.

Supplemental

Step 1.

• If you use the Spreadsheet - Create a sheet from TemplateModule, a new Spreadsheet will be created.
• In other words, a separate sheet will be created for each month.
• If you want to copy a Sheet, you must use the Make an API Call Module.
• See Method: spreadsheets.sheets.copyTo for the Endpoint.
  Method: spreadsheets.sheets.copyTo  |  Google Sheets  |  Google for Developers

Step2 and 3

The reason why the module is marked as Unknown Module is because I do not know where the data on which the number of cases is calculated is stored.
If you are retrieving the data directly from ERP, you can use the Module for ERP. If you are storing the data exported from ERP in Spreadsheet, you can use the Spreadsheet - Search RecordsModule.
In any case, it is no problem if you use the most appropriate Module.

Step4.

If you want to temporarily store New / Exit employees information in Spreadsheet, I believe that you can also use the Spreadsheet - Search RowsModule.

Step 5

For details, see “Append empty rows or columns” at the following URL

Row & column operations  |  Google Sheets  |  Google...

The sheetId specified in the Request Body can be obtained from the gid parameter of the Spreadsheet URL.

For your reference, here are the screenshots of the Request Body and Spreadsheet that I used for verification.

{
  "requests": [
    {
      "insertDimension": {
        "range": {
          "sheetId": "0",
          "dimension": "ROWS",
          "startIndex": 8,
          "endIndex": 11
        },
        "inheritFromBefore": false
      }
    },
    {
      "insertDimension": {
        "range": {
          "sheetId": "0",
          "dimension": "ROWS",
          "startIndex": 2,
          "endIndex": 5
        },
        "inheritFromBefore": false
      }
    }
  ],
}

Step6.

When using the Update a Row Module, it is necessary to specify the row number, which will be calculated from the Index obtained in Steps 2 through 4.
Since Update a Row needs to loop, the number of times it loops also needs to be calculated from the number of New / Exit employees and specified.

Caution

I have not actually created and tested Scenario.

[Guillaume_D]

Hi @momomomonmon,
Thanks a lot for the detailed reply and help, I managed to follow most of the steps. I am facing some challenges with step 5 (see attached). I wanted to test if this worked without adding yet the variables but it doesn’t seem to be connected (I checked on google console that the Google sheet API is active). Do you have any suggestions ?

Thanks !

[momomomonmon]

@Guillaume_D
I am glad to hear that things are going well!
Thanks for the screenshots too. Perhaps we can solve this problem soon.

There are two configuration items that are incorrect.

1. the URL should be in the format /spreadsheets/${spreadsheetId}:batchUpdate. Replace ${spreadsheetId} with your Spreadsheet ID.
2. Method must be POST.

For your reference, here is a screenshot of the test in my environment.