Google Sheets - Using IMPORTRANGE with a variable number of rows ...is it possible to add the required number of blank rows to avoid the IMPORTRANGE's overwrite error?

Test Input: https://docs.google.com/spreadsheets/d/1xEGDDG3BiXHtkU7r8M8uKbyhO9zUKLslKmC5NBK2B8A/edit?usp=sharing

Test Database: https://docs.google.com/spreadsheets/d/1_YZM1INB2VxLFiV60U8koqPLFxhPmDbea2oviM4Dks8/edit#gid=0

In the Test Database sheet, I have a function in B2 that will retrieve the rows of data from Test Input. This function will figure out how many rows there are and insert the appropriate amount. The difficulty is that if I leave that function in each row in column B, then when a link is dropped into column A to work as the IMPORTRANGE source, it will error because the cells that the function now want to fill have the next functions in the way. I am hoping that there's something I can add to the function so that it creates and inserts the appropriate amount of blank rows to avoid that error. Alternatively, a way that when the function runs, it deposits a fresh version of the function in the next available cell in column B.

Any help would be super appreciated.

/edit: Someone asked for additional unique input docs: Doc2 https://docs.google.com/spreadsheets/d/1Gj4-DeZCs8D9iFkFrcSM6kHL_78pGvXPorvjn-aXdpY/edit#gid=0 Doc3 https://docs.google.com/spreadsheets/d/12DAvPs1TVDTuxs0VDZUPRBQuRhyVeOdhgukxNk29vQw/edit#gid=0 Doc4 https://docs.google.com/spreadsheets/d/1cDegqQ63VtSSMUFR8pBW9z8A430NZ6z3hTVJFnFctu8/edit#gid=0

  • 1
    It's possible there's a scripting solution to this, but i have a workaround that could work depending on the actual size of the pulls that are getting made by the query. How many rows, on average, are getting pulled by your query? And is is just the 4 columns?
    – MattKing
    Feb 6, 2020 at 20:14
  • The maximum number of rows that it would pull would be 30. On average I would expect the number of rows to be less than 10. On the actual project, it would be 5 total columns rather than the 4 I used in the example.
    – Kevin R
    Feb 6, 2020 at 20:19
  • 1
    ok, i think i can do it for you. want to make up a few more example sheets and paste in their links to the correct column in the database? I think 4 ought to be enough to test the idea.
    – MattKing
    Feb 6, 2020 at 20:30
  • 1
    nice chatting on the sheet. good luck.
    – MattKing
    Feb 6, 2020 at 21:14
  • 1
    You are now free to upvote. Feb 7, 2020 at 17:04

1 Answer 1


This formula, Dragged down in column B:

=ARRAYFORMULA(IF(A2="",,QUERY(QUERY(IMPORTRANGE(A2,"Sheet1!B2:F10"), "select * where Col1 is not null" )&"|",,9^99)))

And then this formula just one time in cell H2:


Should allow you to build the aggregated dataset you're looking for.

  • Thanks so much for your help! I upvoted, but it told me I was too new for it to count hahaha, have a good one!
    – Kevin R
    Feb 6, 2020 at 21:38
  • Nice work here. Feb 7, 2020 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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