I am a sales manager of multiple locations, each location uses a google drive excel sheet to record their results for each customer that they serve. Is it possible for me to be able to pull the data from these sheets into one sheet that will allow me to see in live time what is happening at all of my locations?
Thank you.
Simple 3-step process:
1.) Collect the URL's of the sheets you need to source from:
https://docs.google.com/spreadsheets/d/1m79q2GTwvXDvQQBlnfWW_vwTrMJ8BY9f7a_ie7Lpkxo/edit#gid=2110495538
(you do not need the "/edit#gid=NNNNNNNNN" part)
2.) Use the IMPORTRANGE function to insert the Sheet/Tab/Column content you need, into your sheet:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1m79q2GTwvXDvQQBlnfWW_vwTrMJ8BY9f7a_ie7Lpkxo", "Sheet1!A5:A1000")
3.) The function will not work until you "submit" it (i.e. click out of the cell), and then you have to click back on it and click "Connect these sheets").
Notes / Gotchas:
a.) If the sheets are in different accounts, you may need to get the account owners to confirm permissions. If the sheets are already shared with you, it should just work as soon as you click "Connect these sheets".
b.) If there is ANY data in your sheet, that would be overwritten by the IMPORTRANGE, then IMPORTRANGE will NOT trigger until you delete the data. In other words, if you're trying to import into Column A, and you have anything in A5, A10, or whatever, the function will just "hang" (with the "#REF!" message) until you delete the "offending" data.
c.) You can import multiple columns, as long as they're contiguous - just use "Sheet1!A1:C300", for example, to import everything in Columns A, B, and C, from Row 1 to Row 300.
Caveat 1: same as "b.)", if there's any data in the "intended landing area" of the data you're trying to import, the function will hang with "#REF!" error.
Caveat 2: if you're importing multiple columns, don't put anything into the header of the 2nd/3rd/4th/etc column. I.e. if your cell A1 has an IMPORTRANGE(...A1:D100), your B1, C1, and D1 cells must also be empty.
d.) If the tabs in the source sheets have been renamed, you will need to change them in your IMPORTRANGE syntax as well:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1m79q2GTwvXDvQQBlnfWW_vwTrMJ8BY9f7a_ie7Lpkxo", "ThisHasBeenRenamedToSomething!A5:A1000")
There is one feature of Google spreadsheets called ImportRange that allows you to pull data out of one sheet into another sheet.
Check this tutorial and sample video for you to understand the following steps on how to achieve this.
Also check this tutorial for another approach using ImportRange.
Note: This process is not needed any coding, if you want to create your own code to pull data from one sheet to another, then you can start by reading this documentation about Google Apps Script and Google Sheets API.
