2

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 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 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 at 20:30
  • 1
    nice chatting on the sheet. good luck. – MattKing Feb 6 at 21:14
  • 1
    You are now free to upvote. – marikamitsos Feb 7 at 17:04
3

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:

=ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(TRANSPOSE(QUERY(B2:F,,9^99)),"|"))))

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 at 21:38
  • Nice work here. – marikamitsos Feb 7 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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