Is there a way I could take all rows from two different Google Sheets and create a third (new) Sheet containing all this data? My first thought was to use a Google Drive activity that finds all the Google Sheets that contain the same two words, such as “RPA Unsub”. These two sheets would have other words in each sheet name, but they would always have the prefix “RPA Unsub”. Once those files are found, is there a way to store those two data tables together and paste them onto a new Google Sheet? Then once that is done, delete the original two Sheets.
Hello Anil - Thank you for the reply. Once I have my output for all the files from the Drive activity “FindFiles”, I have a “ForEach” loop. How do I separate the two data tables once the “Read Range” happens? Wouldn’t the first dt be overwritten when the loop happens again for the second sheet?
create a second datatable and after retreiving the data using read range do a merge datatable on to the second datatable in the loop. So that the second time the first dt gets over written and the merge will merge it with the previous data which is already present in it
It is the counter or the index variable from the for loop… You need a structure so I am cloning the datatable only for the first time and that if condition will take care of it.
You can as well create a Boolean and use it like below(This just to use as switch for first iteration and subsequent iterations)
I appreciate the help very much. I feel like I’m getting closer, but after I do what was in your screenshots and exit the loop, I then have Gdrive make a new sheet, write to that sheet, write dt2, but it only writes one data table. I feel like the second time in the loop is getting overwritten somewhere. Let me gather some screenshots.
Ah! You were right about the scope, but it was the scope of the bool_Run variable. I needed to expand that out further. Now it works! Thank you very much! One last question. I assume this will only work if there are 2 sheets found. Do you have a method to combine the dt for as many sheets that are found? More than 2?
This is not for two sheets it will merge any number of sheets it finds. So merge datatable will contain all the old datatables in it and dt1 will always have the new and will be merged
It should become false …Clone should happen only once. That is the reason we added bool to be false in first if condition it self
This is how it works
so merge datatable needs some structure to merge so we are cloning the structure when we get the data for the first time.
And for all subsequent loops the datatable already has a structure and has data in it so we need not clone again…we just need to append the data that we are getting
prashant1603765
yedukondaluaregala
sharazkm32
ashokkarale
sonaliaggarwal47
Rahul_Rajendran
V_Roboto_V
revathi3
juan_haro
goag2413