Hello,
I would like to consolidate 8 different but same formatted tables that are made in 8 different “Google sheets” into one Excel sheet by using UiPath.
As the row numbers of each sheet differs from both each-other and not fixed, it totally depends on the person who fills the sheet, I could not understand how should I write the range as “A2:?” in UiPath. I would be very happy if you could help me on both of these questions. Thank you in advance. Best regards.
A1:Z to include all columns and let it automatically detect the last row.DataTable. Let’s call it dataTable1.DataTable variables (dataTable2, dataTable3, etc.).DataTable variables into one consolidated DataTable. Set the “Destination” property to a new DataTable variable. Let’s call it consolidatedDataTable.dataTable1.dataTable1 to consolidatedDataTable.DataTable (i.e., dataTable2, dataTable3, etc.), adding the rows to consolidatedDataTable.consolidatedDataTable to an Excel file. Specify the range as A1 to start writing from the first cell in the Excel sheet.Hope it helps!!
Hello @pravallikapaluri, thank you so much for your support and help! However could you please explain the 3rd item in detail? In “Read Range” activity of UiPath Gsuite.Activities.ReadRange, how can I add and use DataTable variable? To “Save to” part? Thanks, Best.
A1:Z to include all columns and let it automatically detect the last row.DataTable. Let’s call it dataTable1.DataTable variables (dataTable2, dataTable3, etc.).Hope it helps!!
It would be simpler to just For Each through the sheets, reading into one tempDT and merging into one finalDT each time. No need to make a separate datatable for every sheet.
As @postwick mentioned you can go with for loop and read each sheet and use merge datatable…One caution for the first time you need to clone the dt using mergeddt = dt.Clone() to ensure structure is provided. For that use if condtion with index=0 and index would be coming from the for loop variables
In Gsuite if you give A2 then it would automaticall read till end of the sheet so no need to give end
Hope this helps
cheers
Hello, thank you very much for all of your support!
@pravallikapaluri, @Anil_G the problem is I’m using studio x and I cannot find data table variable. Please find the screenshot attached of my sequence. How should I continue with “Save to” part? I couldn’t select data table variable value. I would be very happy if you could help me upon this issue and again thank you so much, kind regards.
Here’s how you can consolidate multiple Google Sheets into one Excel sheet using UiPath StudioX:
Install the “UiPath.GSuite.Activities” package from the UiPath Marketplace. This package provides activities to work with Google Sheets.
Use the “Authorize” activity from the GSuite activities to authenticate your Google account and allow access to Google Sheets.
Use the “Get Sheets” activity to retrieve the data from each Google Sheet. Specify the Google Sheet ID or file URL to access the sheet.
Within a loop or by using separate activities for each Google Sheet, retrieve the data and store them in separate DataTable variables.
Use the “Merge Data Table” activity to combine the data from all the DataTable variables into a single DataTable.
Use the “Excel Application Scope” activity to create an Excel application scope.
Use the “Write Range” activity within the Excel Application Scope to write the consolidated DataTable into the Excel file.
Here’s an example of the workflow in UiPath StudioX:
Authorize:
- Input: Google Application Scope (create one if not available)
Get Sheets:
- Input: Google Sheet ID or URL
- Output: dataTable (DataTable variable)
// Repeat the above "Get Sheets" activity for each Google Sheet
Merge Data Table:
- Input: List of DataTable variables (from "Get Sheets" activities)
- Output: consolidatedDataTable (DataTable variable)
Excel Application Scope:
- Input: Path to the Excel file
Write Range:
- Input: consolidatedDataTable
- Sheet Name: Specify the sheet where you want to write the data
- Range: Leave empty to write to the entire sheet
Ensure that you adjust the activities, variables, and configuration based on your specific requirements.
By following these steps, you can consolidate data from multiple Google Sheets into one Excel sheet using UiPath StudioX.
If you just use the Merge Data Table activity then none of this is necessary.
In save to field you can create a variable to save in datatable
@postwick we need to clone or for first time read into a datatable which we are merging
cheers
You can read the first DT then merge into finalDT without finalDT having any data in it.
Read the first sheet, merge into finalDT. Read the second sheet, merge into finalDT. Read the third sheet…
That is false. It will get the structure from the first datatable you merge. I do this all the time.
And if you want to define the structure before merging you can just use Build Data Table.
Let me verify on your first comment…I doubt if that is possible will check and get back
And for second…clone is much more easier as it takes the structure rather than using build and adding all columns and where there is a small change also change it again in build…
Cheers
As I said, it works fine. I used Build to make firstDT and then merged with second DT and now secondDT matches firstDT:
It does work your way…
only Initialization need to be done thats it…clone is not needed
cheers