Google Sheets - Importrange

Question

Currently we have one summary sheet called Order_Summary which collects data, one day per column and someone at work creates a new column daily. We now have another google sheet which now records an end of day summary for the whole business and some of that report asks for data that comes from Order_Summary and some from other sheets.

To save everyone in the business a headache of replicating data from one sheet to another, I would like to use the Importrange function which I have used and seems to work BUT I need to enter the formula into each new day so it points to the new Order_Summary column which is created daily.

Is there a quick way of doing this, basically how do I automate Sheet1 which has tabs created daily to pull data from another Google sheet which has columns created daily?

As a one off I can make it work but I cant seem to automate this.

Any help would be much appreciated!

Answer 1

The function that you need is IMPORTRANGE.
Infospired has an excellent tutorial "Google Sheets Importrange Function".

I created two spreadsheets - One called "Daily Summary", another called "EOD" (End of Day). I created some data in the Daily Summary, and imported the summary of that data into EOD.

This is the formula that I used in EOD (obviously, you'll change this for your own spreadsheets). Though there are only one cell containing the formula, it imported two cells. You can modify this as you wish. There are only three components:

=importrange("https://docs.google.com/spreadsheets/d/138La_7RK3e4_YT2owNrSHi_IoyWAo9r6ZcfPfhnpFNE/edit#gid=0","Daily!B17:B18")

1. The IMPORTRANGE command prefix.
2. The url of the source spreadsheet; you copy this direct from the spreadsheet.
3. The sheet and range to be imported.

When you build the formula for the first time, you get a button to "Allow Access"; just press the button and all is done.

On the left is a screen shot of my raw data order data in Daily Orders. On the right is the linked data in EOD. Note that both the Order Qty and Order Total came across.

The Infospired tutorial is very good; I simply followed the bouncing ball. I recommend it to you.