I am trying to use IMPORTXML in Google Sheets to get about 2000 records. Every page contains 10 records, so I need 10 empty cells below the first IMPORTXML function.
I have two choices in my undertrsanding.
First one is: I have to move the function string every 10th cell down in order to leave empty cells for the data and in the mean time to get the new data starting in the 11th cell and again 10 down etc. This will be crazy manual copy and paste about 200 times every 10th cell.
My second choice would be somehow to overwrite the error given when the cell below contains formula even being empty
.
Error
Array result was not expanded because it would overwrite data in C3.
This way I can sequentially copy the function string as it will auto increment the cell numbers and the data will take as much space as needed and next 10 data cells etc.
=IMPORTXML($C2, "//span[@class='hidden']/span[@class='lead']")
I am already using a formula for the urls and I managed to copy a url string every 10th cell so the empty cell will have no value in them so it wont be an issue to overwrite just the formulas.
=IF(MOD(CELL("row",A10),10)=0,"urlString","")
However, this one doesn't work for importxml function since it does not increment the cell number but just pastes it as a string.
Any help and, or ideas will be appreciated here. I am very new to this and I got stack.
