by Every once in a while each Google Sheets user faces the inevitable: combining several sheets into one. Copy-pasting is tedious and time-consuming, so there has to be another way. And you're right — there are several ways, in fact. So get your tables ready and follow the steps from this article.
All the ways I describe can be used to process large tables. But to keep this guide as clear as possible, I'll keep my tables short and am going to cut down to a couple of sheets.
Reference cells in Google Sheets to pull data from another tab
The easiest way comes first. You can pull entire tables to one file by referencing cells with data from other sheets.
Note. This will do if you need to merge two or more sheets within one Google spreadsheet. To merge multiple Google spreadsheets (files) into one, jump right to the next method.
So, my data is scattered all over different sheets: June, July, August. I'd like to pull data from July and August into June to have one table as a result:
- Find the first blank cell right after your table (the June sheet for me) and place the cursor there.
- Enter your first cell reference. The first table I want to retrieve starts from A2 in the July sheet. So I put:
=July!A2Note. If there are spaces in your sheet name, you must wrap it in single quotes like this:
='July 2022'!A2This immediately replicates whatever lies in that cell:
Note. Use relative cell reference so it changes itself when copied to other cells. Otherwise, it will return incorrect data.
- Make sure the cell with the reference is selected and click on that little blue square at its bottom right corner. The mouse cursor will turn into a big black plus sign. Keep the mouse pressed and drag the cursor to as many columns to the right as you need to fill them with new records:
- Select this entire new row, click that little blue square once again, hold and drag your mouse down — this time to fill entire rows with cell references and bring new data from another sheet:
Though this is probably the first way you may think of to pull data from another tab, it's not the most elegant and quick. Luckily, Google prepared other instruments specially for this purpose.
Copy the tabs into one spreadsheet
One of the standard ways is to copy the tabs of interest into the destination spreadsheet:
- Open the file that contains the sheet(s) you want to transfer.
- Right-click the first tab you need to export and choose Copy to > Existing spreadsheet:
- The next thing you'll see is the pop-up window inviting you to select the spreadsheet. Browse for it, click on it to highlight it, and press Select when you're ready:
- Once the sheet is copied, you'll get a corresponding confirmation message:
- You can either hit OK and continue with the current sheet or follow the link called Open spreadsheet. It will instantly get you to another spreadsheet with the first sheet already there:
Export/import sheets
Another way to import data from multiple Google Sheets is to export each sheet first, and then import them all to a necessary file:
- Open the spreadsheet that contains the sheet you'd like to pull the data from.
- Make the sheet of interest active by selecting it.
- Go to File > Download > Comma-separated values (.csv):
The file will be downloaded to your computer.
- Then open another spreadsheet — the one you'd like to add the sheet to.
- This time, pick File > Import from the menu and go to the Upload tab in the Import file window:
- Hit Select a file from your device and find the sheet you've downloaded just now.
- Once the file is uploaded, you'll see a window with additional options for importing the sheet. To add the contents of that another sheet after your existing table, pick Append to current sheet:
Tip. Among other settings, feel free to specify the separator and convert text to numbers, dates, and formulas.
- As a result, you'll get two sheets merged — one table under another:
But since it is a .csv file you need to import, the second table remains formatted in a standard way. You will have to spend some time formatting it as you need.
Google Sheets functions to combine data from multiple spreadsheets
Of course, it wouldn't be Google if it didn't have functions to merge data in Google Sheets.
IMPORTRANGE to import data from multiple Google sheets
As the name of the function suggests, IMPORTRANGE imports data from multiple Google spreadsheets into one sheet.
Tip. The function helps Google Sheets pull data from another document as well as from other tabs from the same file.
Here's what the function requires:
- spreadsheet_url is nothing else than the link to the spreadsheet from where you need to pull the data. It must always be put between double-quotes.
- range_string stands for those cells specifically that you need to bring to your current sheet.
And here's the pattern I follow to import data from multiple Google Sheets using IMPORTRANGE:
- Open the spreadsheet from which you want to pull the data.
Note. Make sure you have at least viewing access to that file.
- Click the browser URL bar and copy the link to this file right till the hash sign (#):
- Return to the spreadsheet where you want to add the info to, enter the IMPORTRANGE to where the borrowed table should appear, and insert the link as the first argument. Then separate it from the next part with a comma:
- For the second part of the formula, type in the name of the sheet and the exact range that you want to pull. Confirm by pressing Enter.
Note. Wrap the second argument in double quotes as well:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/XYZk0274gRlmluCTfMbzbMQWKiAeq1va77X4/edit","May!A2:D5") - Though the formula looks ready now, it will return the #REF error from the start. That's because the first time you try to pull data from some spreadsheet, IMPORTRANGE will ask for access to it. Once the permission is granted, you will easily import records from other sheets of that file.
Click the cell with the error and press that blue Allow access prompt:
- Once the formula connects to that other sheet, it will import data from there:
Note. You'll need this URL even if you're going to combine sheets from the same file.
Tip. Though Google says the function requires the whole URL, you can easily get by with a key — a part of the URL between /d/ and /edit:
...google.com/spreadsheets/d/XYZk0274gRlmluCTfMbzbMQWKiAeq1va77X4/edit
Note. Remember, the link should be surrounded by double quotes.
Note. By allowing access, you let the Sheets know you don't mind any existing or potential collaborators on this spreadsheet accessing data from another file.
Note. IMPORTRANGE doesn't pull the formatting of the cells, only values. You will need to apply formatting manually afterwards.
Tip. If the tables are rather big, just allow some time for the formula to pull all records.
Note. The records returned by the function will be updated automatically if you change them in the original file.
Google Sheets QUERY to import ranges from multiple sheets
And thus, without haste, we've come to the QUERY function once again. :) It is so versatile that can be used in Google spreadsheets to combine data from multiple sheets (within the same file) as well.
So, I want to merge three different Google sheets (from one file): Winter 2022, Spring 2022, and Summer 2022. They contain the names of all employees who became best in their jobs in different months.
I go to the first sheet — Winter 2022 — and add my QUERY right under the existing table:
=QUERY({'Spring 2022'!A2:D7;'Summer 2022'!A2:D7},"select * where Col1 <>''")
Let's see what it all means:
- {'Spring 2022'!A2:D7;'Summer 2022'!A2:D7} — are all the sheets and ranges I need to import.
Note. The sheets should be written in between the curly brackets. If their names contain spaces, use single quotes to list the names.
Tip. Separate the ranges with a semicolon to pull data from different tabs one under another. Use commas instead to have them imported side by side.
Tip. Feel free to use such infinite ranges as A2:D.
- select * where Col1 <>'' — I tell the formula to import all records (select *) only if cells in the first column of the tables (where Col1) are not blank (<>''). I use a pair of single quotes to indicate the non-blanks.
Note. I use <>'' because my column contains text. If your column contains other data type (e.g. date or time, etc.), you need to use is not null instead: "select * where Col1 is not null"
As a result, two tables from other sheets have been consolidated into one sheet one under another:
Tip. If you'd like to use Google Sheets QUERY to import ranges from multiple separate spreadsheets (files), you will have to implement IMPORTRANGE. Here's a formula to pull your data from other documents:
=QUERY({IMPORTRANGE("XYZk0274gRlmluCTfMbzbMQWKiAeq1va77X4","Mar-Apr-May!A2:D6");IMPORTRANGE("XYZahJZHSlhMGLSW_xA6ZBqNmt1I0ADo4N4M","Jun-Jul-Aug!A2:D4")},"select * where Col1<>''")
Tip. I use the keys from URLs rather than entire links in this long-enough formula. If you're not sure what that is, please read here.
Tip. You can also use QUERY to merge two Google sheets, update cells, add related columns & non-matching rows. Check this out in this blog post.
3 quickest ways to merge multiple Google sheets
If standard ways of Google spreadsheets to combine data from multiple sheets seem dull, and the functions scare you off, there's an easier approach.
Combine Sheets add-on
This first special add-on — Combine Sheets — was designed with a single purpose: import data from multiple Google sheets.
It's clever enough to recognize the same columns in different sheets and bring data together accordingly if you need.
All you are to do is:
- Select sheets or entire spreadsheets to merge and specify the ranges if necessary. A quick search across your Drive makes this even faster.
- Choose how to pull the data:
- as a formula. Mark the checkbox called Use a formula to combine sheets if you want to have a master sheet that will dynamically change based on your original contents.
Although you won't be able to edit the resulting table, its formula will be always linked to the source sheets: edit a cell or add/remove entire rows there, and the master sheet will be altered accordingly.
- as values. If editing the resulting table manually is more important, ignore the above option and all data will be combined as values.
Extra options are here for fine-tuning:
- join records from the same columns into one column
- keep the formatting
- add a blank line between different ranges to notice them right away
- as a formula. Mark the checkbox called Use a formula to combine sheets if you want to have a master sheet that will dynamically change based on your original contents.
- Decide where to place the merged table: new spreadsheet, new sheet, or in a location of your choice.
Here's a quick demonstration of how I combined my three small tables with the add-on:
Of course, your tables can be much bigger and you can merge lots of different sheets as long as the resulting file doesn't exceed the 10M cell-limit.
One of the options this add-on offers is to add more sheets to your previously combined data. In this case on step 1, you need to pick not only the data to combine but also the existing result. Here's how it looks:
Video: How to combine multiple Google sheets into one
Check out the help page for Combine Sheets or watch this 3,5-minute tutorial:
Consolidate Sheets add-on
Consolidate Sheets is another handy tool among our add-ons. Its main difference from the aforementioned tool is the ability to add up data in columns in Google Sheets (or rows, or single cells, for that matter).
Consolidate Sheets also recognizes common headers in all the Google sheets to join, even if they are in the leftmost column and/or the first row. There's always an option to merge Google sheets and calculate cells based on their place in the tables.
Let me break it down into steps for you as well:
- Select sheets to consolidate. Import more files from Drive if necessary straight from the add-on.
- Pick the function to consolidate in Google Sheets.
- Choose the way to add up cells in Google Sheets: by labels (header labels, left column labels, or both) or position.
- Decide where to place the consolidated data: new spreadsheet, new sheet, or any specific location within the opened file.
Here's how this process looks:
There's also an option to consolidate all your sheets using a formula. This way your result will change in sync with the values in the source sheets:
Note. There are some peculiarities you need to know about how the formula works. For example, if you consolidate from multiple different files, there will be an extra step to connect the sheets for the IMPORTRANGE in use. Please visit the instructional page for Consolidate Sheets for these and other details.
Video: How to consolidate multiple Google sheets into one
Here's a 4-minute demo-video about the add-on work:
I truly encourage you try this add-on. You'll see for yourself how much extra time you'll have after incorporating this tool to your daily work.
Merge Sheets add-on
There's one more add-on worth mentioning — Merge Sheets. It matches records from the same column in several sheets/documents and then pulls related data from the lookup sheets/documents into the main one. Hence, you always have an up-to-date spreadsheet at hand.
There are 5 straightforward steps:
- Select your main sheet.
- Select your lookup sheet (even if it's in another spreadsheet).
- Choose columns where matching records may occur.
- Tick of the columns with records to update.
- Tweak any additional options that will help you merge two sheets and achieve the best result possible.
You can even save the settings into reusable scenarios and run them later in a click.
Video: How to merge Google sheets
I know a picture is worth a thousand words, so here's a 3-minute video tutorial on Merge Sheets for you:
Ready to try it for yourself?
or visit this help page for details about each step and setting.
On this note, I'm going to finish this article. Hope these ways of pulling data from multiple different sheets into one will be of use. As always, looking forward to your comments!
234 comments
I have a sheet that is connected to a form. When people fill out the form, it populates in the sheet correctly. I have that sheet connected to another sheet. We use this form for people requesting to make reservations for a part of our building. The sheet that I have connected is supposed to copy the info from the form response sheet and then I have added columns for us to record when we contact the person back, etc. Everything works great, except this keeps happening---the people keep filling in the form and it goes to the first sheet correctly. It doesn't immediately appear on the second sheet though. For some reason, the cells show connected, for example A1, A2, A3, A4 and then it will go to A6. When I come in each week I keep having to reset it so that it goes A5, then A6, etc. So, I'm filling in the missing cell. At some point after that, it goes away again. Next week I will come in and find A1, A2, A3, A4, A6 again. Why won't my cell pull stick? Why does it keep skipping a row? How can I fix it so that it will stay fixed? Thanks!
Hello Kim,
How do you pull records to your second sheet? Do you use some of our add-ons to pick up the data? Or do you refer to the first sheet with some manually-built formulas? Which ones?
I use
=('THIS TAB DOES NOT NEED USED'!A21)
in the cell
The first sheet is called THIS TAB DOES NOT NEED USED--it is the info directly from the form. I had to call it that because my chief kept going in and changing things there instead of where he was supposed to be. :)
Thank you for replying, Kim.
You see, when someone fills in the form, their responses don't go into empty rows on the response sheet. They appear as new inserted rows.
Here's an example: suppose that the last response in your sheet is in row 20. Your formula on the second sheet is ready to pull the next data whenever it appears in row A21. But the response doesn't fall there. It is being inserted as a new row above that 21st row. Your 21st row becomes 22nd, and your formula adjusts itself automatically.
I'm afraid I don't know for a way to stop formulas from auto-adjusting themselves, it's a standard behaviour for spreadsheets. But you can try and wrap all your cell references into INDIRECT, e.g. =INDIRECT("'THIS TAB DOES NOT NEED USED'!A21")
Hello,
I have a simple question without a simple answer I am assuming.
I have a number of dynamic sheets that I want to consolidate into one sheet. Also I want to capture any new data that is added. So if any of the numerous sheets has a new row populated in it, it will automatically get populated in what I am calling my dashboard sheet. Basically consolidating and updating several differant sheets into one. Anytime a new row is added to any number of sheets it populates the next available row in the dashboard sheet. Almost like any new data is appended to the dashboard sheet. Any suggestions.
Hello Jamil,
Our Consolidate Sheets add-on will help you out. It will consolidate your data using a formula so all changes in the source sheets appear in the result summary table automatically.
Hello. Tell me how to create a database in GS? I want to collect rows that will be unique based on two columns. For example, if data enters table 1 (it is dynamic), then new rows are written in table 2. If data gets into table 1 again and they are already in table 2, then the record is not made. If there are fewer records in table 1, then records in table 2 do not disappear.
Hello Vince,
You can use a formula like this:
=SORTN(Sheet1!A2:D100,9^9,2,Sheet1!B2:B100&Sheet1!C2:C100,FALSE) - where B & C are columns with duplicates. However, this formula will also sort your rows alphabetically. If you'd rather avoid that, then use our Remove Duplicates add-on and its scenarios instead.
How do you take several sheet (tabbed at the bottom of the google sheet) and make them into one group of 4 or 5 sheets. Basically you click on this one tab and there are "sub-tabs" that contain different spreadsheets within it. I can not figure out how to do this and I know there has to be a way. Please help
Hello AJ,
I'm afraid it's impossible to group sheets as you describe in Google Sheets.
hi I want to link new entries in multiple sheets at the bottom of the master sheet. pls help
Hi Pooja,
Sorry, I'm not sure I fully understand your task. Could you please describe it in detail?
Hi,
I would like to use a dynamic value for query function using indirect reference. Please have a look at the below formula:
AK1 = Template!A13:AI50
AK2 = 'Template (2)'!A13:AI50
AK3 = 'Template (3)'!A13:AI50
=query({indirect(AK1)},"Select * where Col1 is not null",1)
The above formula works, and I would like to concatenate the ranges using a formula and replace the indirect with all the above ranges. Please advise how do I do that? I tried using concatenate combinations and I am not arriving at a solution.
Hi Antony,
I mentioned a way to concatenate ranges in QUERY in this part of the blog post. It should look like this:
=QUERY({INDIRECT(AK1);INDIRECT(AK2)},"...")
Also, if you put 'Template (2)'!A13:AI50 into a cell, your spreadsheet will "remove" the first single quote as it's used to treat the entered value as a text. So to keep the references correct for INDIRECT, make sure to put two single quotes: ''Template (2)'!A13:AI50
I created a master sheet using IMPORTRANGE; however, I want to use and edit the master sheet rather than shuffling through the original worksheets. Is there a way to now edit and make changes to the master sheet after combining worksheets?
Hello,
There's no way to keep the formula and edit its contents at the same time in Google Sheets. To be able to edit, you need to either convert your formula into values first or combine your data without the formula using the Combine Sheets add-on.
Hello, I'm trying to combine about 100 sheets in a spreadsheet with:
=query({'SWE 21/01/21'!A2:G4;'SWE 19/01/21'!A2:G12;'SWE 17/12/20'!A2:G15;'SWE 15/12/20'!A2:G13;'SWE 3/12/20'!A2:G12;'SWE 1/12/20'!A2:G11;'SWE 26/11/20'!A2:G14;'SWE 24/11/20'!A2:G13;'SWE 19/11/20'!A2:G16;'SWE 17/11/20'!A2:G16;'SWE 12/11/20'!A2:G14;'SWE 11/12/20'!A2:G16;'SWE 5/11/20'!A2:G18;'SWE 3/11/20'!A2:G22;'SWE 29/10/20'!A2:G17;'SWE 27/10/20'!A2:G22;'SWE 20/10/20'!A2:G20;'SWE 15/10/20'!A2:G17;'SWE 13/10/20'!A2:G20;'UI/UX 9/10/20'!A2:G13;'SWE 8/10/20'!A2:G19;'UI/UX 7/10/20'!A2:G18;'DSCI 7/10/20'!A2:G7;'SWE 6/10/20'!A2:G17;'UI/UX 2/10/20'!A2:G14;'UI/UX 30/9/20'!A2:G14;'DSCI 30/9/20'!A2:G9;'SWE 29/9/30'!A2:G23;'DSCI 28/9/20'!A2:G10;'UI/UX 25/09/20'!A2:G23;'SWE 24/09/20'!A2:G23;'DSCI 23/09/20'!A2:G9;'UI/UX 23/09/20'!A2:G15;'SWE 22/09/20'!A2:G23;'DSCI 21/09/20'!A2:G5;'UI/UX 18/09/20'!A2:G16;'SWE 17/09/20'!A2:G21;'UI/UX 16/09/20'!A2:G18;'DSCI 16/09/20'!A2:G9;'SWE 15/09/20'!A2:G17;'DSCI 14/09/20'!A2:G9;'UI/UX 11/9/20'!A2:G21;'SWE 10/09/20'!A2:G26;'DSCI 9/09/20'!A2:G9;'UI/UX 9/09/20'!A2:G13;'SWE 8/09/20'!A2:G28;'DSCI 7/9/20'!A2:G6;'UI/UX 4/9/20'!A2:G20;'SWE 3/09/20'!A2:G22;'DSCI 2/9/20'!A2:G8;'UI/UX 2/09/20'!A2:G19;'SWE 1/09/20'!A2:G26;'DSCI 31/08/20'!A2:G9;'UI/UX 28/08/20'!A2:G19;'SWE 27/08/20'!A2:G27;'DSCI 26/08/20'!A2:G9;'UI/UX 26/08/20'!A2:G18;'SWE 25/08/20'!A2:G25;'DSCI 24/08/20'!A2:G9;'UI/UX 21/08/20'!A2:G22;'SWE 20/08/20'!A2:G25;'DSCI 19/8/20'!A2:G6;'UI/UX 19/8/2020'!A2:G19;'SWE 18/8/20'!A2:G25;'DSCI 17/08/20'!A2:G7;'UI/UX 14/8/20'!A2:G20;'SWE 13/08/20'!A2:G27;'UI/UX 12/8/20 '!A2:G16;'DSCI 12/8/20'!A2:G8;'SWE 11/8/20'!A2:G25;'DSCI 10/8/20'!A2:G11;'UI/UX 7/08/20'!A2:G21;'SWE 6/08/20'!2:24;'UI/UX 5/08/20'!A2:G19;'DSCI 5/08/20'!A2:G10;'SWE 4/08/20'!A2:G22;'DSCI 3/08/20'!A2:G12;'SWE 30/07/20'!A2:G21;'UI/UX 29/07/20'!A2:G24;'DSCI 29/07/20'!A2:G11;'SWE 28/7/20'!A2:G23;'DSCI 27/7/20'!A2:G10;'UI/UX 24/07/20'!A2:G20;'SWE 23/07/20'!A2:G27;'UI/UX 22/07/20'!A2:G25;'DSCI 22/07/20'!A2:G10;'SWE 21/07/20'!A2:G25;'DSCI 20/07/20'!A2:G10;'UI/UX 17/07/20'!A2:G22;'SWE 16/7/20'!A2:G27;'DSCI 15/7/20'!A2:G11;'UI/UX 15/7/20'!A2:G25;'SWE 14/7/20'!A2:G27;'DSCI 13/7/20'!A2:G12;'UI/UX 10/7/20'!A2:G23;'SWE 9/7/20'!A2:G27;'DSCI 8/7/20'!A2:G11;'UI/UX 8/7/20'!A2:G22;'SWE 7/7/20'!A2:G29;'DSCI 6/7/20'!A2:G11;'UI/UX 3/7/20'!A2:G29;'SWE 2/7/20'!A2:G29;'UI/UX 1/7/20'!A2:G23;'DSCI 1/7/20'!A2:G11;'SWE 30/6/20'!A2:G27;'DCSI 29/6/20'!B2:G11;'UI/UX 26/6/20'!A2:G20;'SWE 25/6/20'!A2:G27},"select * where Col1 is not null)")
but it keeps telling me "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows" what could be the problem?
Hello Ayo,
This error usually occurs whenever there is a blank sheet or a sheet with only a header row among your original tables. Will you be able to check that?
Hi, i want to apply IMPORTRANGE only once, and not to keep the destination sheet in sync with the source sheet, How can I do that?
Hi David,
Just convert your IMPORTRANGE formula to values right after entering the formula and getting the result.
Hello -
Seeking Vertical calendar tracking of events across 4 tabs.
Goal -
"combined data" tab where : column A is date (static), column B is day of week (static) and then C, D, E, and F are each data pulled from tabs.
For example : data would show in this format....
1| Date | Day | XYZ-(tab a) | XYZ-(tab b) | XYZ-(tab c) | XYZ-(tab d) |
2| 11/15/2020 |Sunday | [blank] | Mail | [blank] | Jacob |
3| 11/16/2020 |Monday | Winner! | [blank] | Dice | [blank] |
4| 11/17/2020 |Tuesday | [blank] | Post | Cards | Susan |
When I have tried to use IMPORTRANGE it wants to move horizontally and Query wants to pull all data and stack.
I am trying to pull all actions from the same date on the same row..
Is this the incorrect function to be using? (I am more familiar with Excel - is there a Vlookup or Hlookup that would work better?)
Thank you for ANY help you can provide
Hello Sunday,
For me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) including 4 tabs you want to combine AND an example of the result you'd like to get (the result sheet is of great importance and often gives us a better understanding than any text description).
I kindly ask you to shorten the tables to 10-20 rows.
If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
Note. We keep that Google account for file sharing only and don’t monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
I’ll look into your task and try to come up with a formula.
I was able to figure it out with this:
=QUERY({Kindergarten!A2:E;'1st'!A2:E;'2nd'!A2:E;'3rd'!A2:E;'4th'!A2:E;'5th'!A2:E;'6th'!A2:E;'7th'!A2:E;'8th'!A2:E;'9th'!A2:E;'10th'!A2:E;'11th'!A2:E;'12th'!A2:E},"select * where Col1>=date '"&TEXT(TODAY(),"YYYY-MM-DD")&"' ",1)
Thank you so much for your help!
My next task is to take this queried data and put it in time order. Right now it is in order of the sheets brought in.
This formula takes not only records for 'today's date' but all next days as well since you use the >= condition. But I'm glad if it works for you.
As for ordering data, add the 'order by' clause:
=QUERY({Kindergarten!A2:E;'1st'!A2:E;'2nd'!A2:E;'3rd'!A2:E;'4th'!A2:E;'5th'!A2:E;'6th'!A2:E;'7th'!A2:E;'8th'!A2:E;'9th'!A2:E;'10th'!A2:E;'11th'!A2:E;'12th'!A2:E},"select * where Col1>=date '"&TEXT(TODAY(),"YYYY-MM-DD")&"' order by Col1 ",1)
This what what my timestamp looks like: Thu, Jul 29, 2021 @ 10:19 AM
Basically I have created a query based on information of students checking in and out of school. Students check using a google form which gives me a time stamp when converted to google sheets. Then it is filtered out into differnet tabs where columns are deleted for the each teacher based upon a grade. I want the query an office report that pulls over from each teacher tab only today's students and not those who checked in and out yesterday. How do I query based on that time stamp to report only students who enter and exit my room today? My data columns are A (Timestamp), B In or Out), C (Grade), D (Name), E (reason). I need this to automatically filter/query everday.
My current formula looks like this:
=QUERY({Kindergarten!A2:E;'1st'!A2:E;'2nd'!A2:E;'3rd'!A2:E;'4th'!A2:E;'5th'!A2:E;'6th'!A2:E;'7th'!A2:E;'8th'!A2:E;'9th'!A2:E;'10th'!A2:E;'11th'!A2:E;'12th'!A2:E},"select * where Col1 is not Null")
This works, but it pulls all data over. I am pretty sure that I need the change the area after "select", but not sure what to change it to to only pull over those with a timestamp for today.
You're right, it's the part after 'select *' that needs changes. Try this one:
=QUERY({Kindergarten!A2:E;'1st'!A2:E;'2nd'!A2:E;'3rd'!A2:E;'4th'!A2:E;'5th'!A2:E;'6th'!A2:E;'7th'!A2:E;'8th'!A2:E;'9th'!A2:E;'10th'!A2:E;'11th'!A2:E;'12th'!A2:E},"select * where (Col1 >= datetime '"&TEXT(TODAY(),"yyyy-mm-dd HH:mm:ss")&"' and C <= datetime '"&TEXT(TODAY()+0.99999,"yyyy-mm-dd HH:mm:ss")&"')", 1)
I am getting a value error message stating: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: C
Should: and C <= datetime '"&TEXT(TODAY()+0.99999,"yyyy-mm-dd HH:mm:ss")&"')", 1) be 'A'?
I was able to figure it out with this:
=QUERY({Kindergarten!A2:E;'1st'!A2:E;'2nd'!A2:E;'3rd'!A2:E;'4th'!A2:E;'5th'!A2:E;'6th'!A2:E;'7th'!A2:E;'8th'!A2:E;'9th'!A2:E;'10th'!A2:E;'11th'!A2:E;'12th'!A2:E},"select * where Col1>=date '"&TEXT(TODAY(),"YYYY-MM-DD")&"' ",1)
Thank you so much for your help!
My next task is to take this queried data and put it in time order. Right now it is in order of the sheets brought in.
Yes, my apologies, there should be your column with dates instead of C: 'A' or even 'Col1'
I am trying to pull in data from13 diferent tabs from one worksheet to another. The data on these tabs will change daily and it includes an automatic timestamp. The data I want bring over from these tabs is only when the timestamp is for today. How do I go about doing that?
Hello Shawna,
Since you need to include the date as a condition, you should use formulas for your task.
You can either build a QUERY formula with the 'where' clause to pull only when there's a certain date in a certain column, or use our Combine Sheets to combine data with a formula first and then edit this formula by adding the same condition for column+date with the 'where' clause.
thanks, but if this date changes everyday how can it be done without editing the query formula everyday?
Can you specify how the timestamps look exactly? Maybe there are some date/time formulas you'll be able to incorporate.
Hey there,
I am having trouble wrapping my head around this so hopefully you can help.
I currently have a "master" project sheet that takes data from each sheet in the workbook and combines it adding to the list each time a new sheet is duplicated (using a template sheet)
1. Duplicate the "customer project data sheet" which would hold info such as expenses job costing, time on job etc.
2. Within this sheet I will grab totals and have all of the details essentially for that one project.
3. Now the trouble I am having is, if I have a template that talks back to the master sheet correctly I need it to continue to take the "totals" of the data in each customer project data sheet and enter it in the correct cells on the master sheet automatically as long as the data is entered in the correct locations on the template "customer project data sheet" (which would get named upon duplication with the client name)
So is it possible for gSheets to know that
1. After I duplicate a template it will know that all cells from the "template customer data" being pushed to master will do the same on each template sheet each time it is duplicated and data is entered in the cells requested within that sheet?
2. Then possibly easier fix, if the above can happen how do we get it to essentially continue adding the data but not sending it to the exact same cell, but rather continue to go down adding the data in a list form. Which then on the master project sheet I will be able to see the overall details, totals etc?
Overall I will have: Master Project Sheet (all needed data from customer sheets routed to this sheet), "Client Project Data TEMPLATE sheet" (used to duplicate every time we have a new project to have info filled out) and then every sheet after that will essentially be duplicates of the template but named (client name) as the projects are completed...
I hope that makes sense:)
Hi Chad,
Based on the desired outcome you described, it looks like our Combine Sheets could help. It can pull data using a formula in order to keep the master sheet dependent on source sheets. You can test the add-on for 30 days for free to see if it suits your needs.
In the meantime, you can share a sample spreadsheet with us (support@apps4gs.com) with 4 sheets: 1) an example of the template sheet, 2) & 3) a couple of sheets with data you're putting together 4) the result sheet – an example of your master sheet (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
I'll look into your task and do my best to help.
Hi,
I need some help to combine different sheet files. I have 27 sheet files in a folder so I'd like to put all this sheet files in one google spreadsheet, but I really need that each one of the 27 become a tab in this new google spreadsheet. How can I do that?
Hi Henrique,
You can either copy all sheets to the required spreadsheet or export the required sheets and import them back as tabs to a required file.
hi there! this information really helps me, thank you very much. however i there a way that i can merge information from google sheets automatically (which is responses from google form) to a new spreadsheet. this is one of my problem that i cant seem to find solution ?. tq
Hi Atiqah,
There are few ways, actually, and I mention them all in this blog post :)
Thanks for this information! Extremely useful for what I need!
I work for 2 companies. I'm using 2 Google Forms for them and will use the add-on to consolidate my data into 1 spreadsheet; however, I need to be able to tell which company the responses came from. I don't want to add a question to the form "which manufacturer?" because neither knows about the other and I want to try to keep it that way.
Is there a way to include dynamic text on their forms that will show up on the response worksheet (ie. Company A and Company B) in a filterable column?
Thanks in advance!
Thank you for your feedback, Pam!
I'm sorry but we don't work with Google Forms and have no tools or particular workarounds. You may try to find a solution in Google Docs Community though: https://support.google.com/docs/threads?hl=en&thread_filter=(category:docs_forms)
Is it possible to use the query formula or a different formula to pull in rows of data based on more than one column in the same sheet? So the word may occur in the first, second, or third column.
Yes you can. You can utilize the OR case statement in Query and re iterate your query three times each with a unique column. For example. SELECT * WHERE Col1 = 'yes' OR Col2 = 'Yes'......etc.
Hi,
I am working on a dynamic dashboard in my company and I need some help for doing so.
I have a google sheet that used API to fetch the status of job created and job completed from a 3rd party software. This data is stored in different sheets of the same spreadsheet. every day when job new jobs are created and completed, new row is added in the corresponding sheet, updating the latest status of the job.
Here is what is want to build upon that;
1. Fetching all the row items of all sheets into one sheet (please note that these individual sheets will be updating on daily basis and I want this to update in the consolidated sheet as well)
Can you please guide me further on this?
Hi Bipin,
You can try combining data using the QUERY function. To include all future rows, just use the ranges like this – A2:A
Alternatively, try our Combine Sheets add-on. Its result can be returned by a formula that will dynamically change with the source data.
If you want to use scripts, I'm afraid I can't help with that. You may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links.
Hope this helps!
Hi Natalia,
Thanks for the fantastic instructions.
I set up an IMPORTRANGE function, but when I try to allow access, the spinner just spins and nothing further happens. I have editing privileges on the source spreadsheet but am not the owner. Is that a problem? Or did I do something wrong with my formula? =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1gFzmWiMZwwViLMkKRNQZidkdW78bS6eL/edit", "Alameda!L18:L18")
Thanks!
Hi Jan,
Your formula looks fine. Editing permissions are also enough to pull data. You can try the things described in this help thread to fix the problem.