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!

  • Welcome. The question is too broad as there are several ways to achieve what you are looking for. i.e. To automate tasks on Google Sheets we could use the macros recorder, Apps Script, the Sheets API, an add-on or a web application. Please add more details about your search/research efforts and specify if what tool will you use. Ref. How to Ask. If you need help to find a web application or an add-on consider to ask on Software Recommendations. – Rubén Sep 14 at 14:41
  • At the moment i am using no scripts or add-ons, if you can recommend any as i'm not familiar with scripts or add-ons, that would be fantastic? – Darth Silva Sep 14 at 16:45
  • Look at arrayformula. Doc – Ron Kloberdanz Sep 14 at 21:23

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.

Order Summary End of Day

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

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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