Comments on: Merge several Google sheets into one without copy-pasting

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. Continue reading →

Comments page 2. Total comments: 205

  1. Please guide me to arrange combined data alphabetically

  2. Tank's, it was really helpful. straightforward & very easy to follow.

  3. Dear Natalia Sharashova,

    I need to import only last Sunday to Saturday or (Last 7 days) newly added data from main sheet to another sheet and this function will repeat every week to extract weekly report is there any formula to do this kindly do needful.

    With Thanks & Regards,
    Manikandan Selvaraj

    1. Dear Manikandan Selvaraj,

      For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) including 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.

      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 help.

  4. Dear Natalia Sharashova,

    is there any function key to show the automatic last update of the google sheet in a specific column when the user updates their google sheets?

    with Thanks & Regards,
    Manikandan Selvaraj

    1. Dear Manikandan,

      I believe it depends on what update you'd like to see exactly (time stamp of the last edit or the values from particular columns themselves). Could you please specify?

  5. Dear Natalia Sharashova,
    Thanks a lot for your valuable help its working as per our need. In this file, there is another issue when we use some of the QUERY IMPORTRANGE to pull the data from the main sheet to user sheets some of the rows are not filling. For further details pls check the user 1 sheet in column date some column dates are not filled automatically. (for your access I shared the file in support@apps4gs.com)
    With Thanks & Regards,
    Maniikandan Selvaraj

    1. Hello Manikandan,

      According to the QUERY documentation, "In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values."

      In your main sheet, there are cells with dates formatted as text. You'll find them if you double-click each cell. Those where a calendar appears are formatted as dates, those without the calendar are formatted as text. That's why they don't show up in the users' sheets - they are a minority data type.

      Fix the format and they should appear in the users' sheets.

  6. Dear Natalia Sharashova,

    I have created a process management google sheet (Main sheet) which is handled by the manager to allocate work to the freelancers.
    I have used “IMPORTRANGE” “QUERY” “FILTER” functions to pull only specific columns (ex.:Col1, Col3, Col4, Col9) to show to the specific freelancer (Name of the freelancer), like these I have created 5 different sheets for individual freelancer (User sheet).
    In the main sheet, there is a column called STATUS OF THE PROJECT (ex.Col9) which is to be filled by the freelancer in the user sheet. Is there any function or formula to change the status in the user sheet which is to be reflected in the main sheet?
    Note: We have tried to include IMPORTRANGE function in the main sheet to pull the status from the user sheet. Unfortunately, the function does not work as expected. If a user changed the status of Row 5 in the user sheet, which is not exactly changing the status of the project in the main sheet.

    With Thanks & Regards,
    Manikandan Selvaraj

    1. Dear Manikandan,

      If I understand your task correctly, you pull Col9 from the Main sheet to each User sheet. The users can't fill this column on their sheets because it is returned by the formula. If they try and enter anything in Col9, it will break the entire formula. Also, you can't enter another formula into Col9 in the Main sheet and refer to the same Col9 in other users sheets because that would cause circular dependency.

      So, to solve your task, I'd advise you to avoid pulling Col9 from the Main sheet, add a status column on each user sheet manually, and then collect the required data from this column to the Main sheet.

      1. Dear Natalia Sharashova,

        Thanks for the valuable information,

        As per your advice, I create a status column for each user sheet so the user can change or update the status of the project. But there is a problem the project assigned for each user is shuffled in the main sheet so individual users can see only the allocated projects.

        For example project in serial no 3 in the main sheet will be allocated to one of the users as project serial no 1, when the user changes the status of his project serial no 1 in his user sheet (Row 1) it will change the status of project serial no 1 in the main sheet not in an exact project serial no 3.

        with thanks & Regards

        1. Dear Manikandan,

          For me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) including 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 help.

          1. Dear Natalia Sharashova,

            AS per your requirement, I send the supporting spreadsheet for further query testing to your (support@apps4gs.com) mail id.

            WITH THANKS & REGARDS,
            Manikandan Selvaraj

            1. I've got the files, Manikandan, thank you.

              However, I can see the same project numbers in each User sheet as they are in the Main sheet. The number don't change as you describe they would. Project 3 in the Main sheet is still project 3 in the User 3 sheet.

              1. Dear Natalia Sharashova,

                We have reversed 1 step backward for better understanding. We got a problem with project status, not worried about serial no.

                In the main user sheet, there is a column called Project status. That is changed by each user. I need a way how a user can change status in the main user sheet via user sheet.

                With Thanks & Regards,
                Manikandan Selvaraj.

              2. Manikandan,

                I've looked into your files once again. I've adjusted the Query the way I suggested earlier and added the Project Status column manually in the User 1 file (you'll need to do the same in other User files).

                I also added the formula to K2 in your Main sheet. It will check the user in column I and will go to the required spreadsheet for the status of that project.

                Hope this solution suits you.

  7. 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!

    1. 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?

      1. 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. :)

        1. 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")

  8. 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.

    1. 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.

  9. 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.

    1. 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.

  10. 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

  11. hi I want to link new entries in multiple sheets at the bottom of the master sheet. pls help

    1. Hi Pooja,

      Sorry, I'm not sure I fully understand your task. Could you please describe it in detail?

  12. 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.

    1. 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

  13. 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?

  14. 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?

  15. 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

    1. 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.

  16. 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.

    1. 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)

  17. 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.

    1. 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)

      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'?

        2. 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.

  18. 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?

    1. 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.

      1. thanks, but if this date changes everyday how can it be done without editing the query formula everyday?

        1. Can you specify how the timestamps look exactly? Maybe there are some date/time formulas you'll be able to incorporate.

  19. 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?

  20. 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

    1. Hi Atiqah,

      There are few ways, actually, and I mention them all in this blog post :)

      1. QUERY + IMPORTRANGE. If you use an endless range (e.g. A1:D), all new rows added to the table will appear in the result.
      2. Combine Sheets add-on can combine your data with a formula. This way, your master sheet will dynamically change based on your original contents.
      3. The same is for Consolidate Sheets. It can also consolidate data with a formula in order to take into account all new future records.

  21. 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!

  22. 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.

    1. 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.

  23. 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?

    1. 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!

  24. 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!

  25. Thank you so much for this article! It has been extremely helpful. I'm fairly new at google spreadsheets but is there a way that I can pull cells B2:B5 on every spreadsheet that I make (I have a sheet that is an original so it will always have the formula and we make a copy to enter in customer info) to another sheet that would be a master sheet for all our customer info?
    Thanks again!

  26. =QUERY({asset!$A$1:$M; dealership!A1:M}, "SELECT Col4, Col11", 1)

    i have this function that i want to use to get data from sheet namely asset and namely dealership, Col4 and Col13 is only for asset sheet but am unable to get data from dealership columns, how do i get data from both so that i have a column from both asset and dealership sheets

    reply to email too

    1. Hello Fadhili,

      I'm sorry but via email, we answer questions related to our add-ons only.

      As for your formula, I can see you use 'A1:M' ranges without indicating the last row to take. In this case, I'd advise you to specify to return only rows with data (not blanks). Since you haven't, the function pulls not only data but all empty rows from sheet 'asset' as well. Thus, the data from your second table is somewhere under those empty rows. You'll find it if you scroll the sheet down.

      To sum it all up: you need to either limit the range to rows with data only (e.g. A1:M50) or make QUERY return only cells with data (e.g. ..."select Col4, Col11 where Col4 is not null")

      Please see this blog post for more info on QUERY with formula examples.

    1. Hello Peter,

      Please make sure you select the option to Consider column headers on Step2 of the add-on. Also, if there are mixed data types (e.g. numbers and text) within a column, the QUERY function will return only the majority data type leaving the rest as empty cells. We described it here in the help page for the add-on as well.

      If these don't help, please share at least one of the source tables with us: support@apps4gs.com. I will look into it and see if something else causes problems. Thank you.

  28. Hi thank you! this is useful,.

    Does Googlesheet have a capability like PowerQuery in Excel whereby you could schedule a refresh daily and append data from a source into a historical log?

    1. Hi John,

      you can schedule a daily refresh using Google Apps Script only. You may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview

      As for appending data from a historical log, I believe Google Data Studio is the most related service you will find. You can learn more about it here: https://support.google.com/datastudio/answer/6283323?hl=en

  29. Hello. Thank u for this wonderful info. My question is that, I use comma as separator so I can see them side by side , however there is a gap between them (At least 8 columns) .

    How to fix this?

  30. Hi Natalia,

    We have a new daily google sheet that gets created. We are trying to take the rows from that new sheet and consolidate them into a master spreadsheet. How can we automatically, recognise we have a new sheet that has been created and then import that data into the master sheet?

    Thanks so much in advance

    1. Hi Gareth,

      If you create a new sheet daily, I'm afraid you won't see its records in the master sheet automatically. You have to add the reference to this new sheet into the formula so it could pull the records.

  31. Hi Natalia

    I have few questions

    1) I was using a combination of Array sum, Query and Import-range to merge data from 4 different sheets into a master sheet.

    However the contact numbers in a column separated by commas were not displayed in the results. Only those without commas were displayed. Is there a way around this?

    2) Also I then tried a combination of Array sum, Filter function and Import range using "" as the condition. It works but sometimes the latest fields are not displayed. I need to refresh the page and then it's displayed. Do you know the reason behind this? I delete old data and add new data as well.

    Is there a better alternative? The formula has become enormous.

    3) As the amount of data is increasing in each of the 4 sheets the lag is also increasing. I used a few measures like deleting the excess blank cells and shifting the master sheet to different sheet and linking both. I am also going to upgrade my machine from i3 4gb ram hhd to i5 8gb ram ssd. Will this make any difference or does it solely depend on the internet speed? Does complex formulae make a difference?

    Regards

    1. Hi Swapnil,

      1) If there are several numbers separated by a comma within a cell, Google will treat such data as text. As a result, you will have a column with mixed data: numbers and text. In cases like this, QUERY pulls only the majority data type into the result – numbers in your case.

      2) IMPORTRANGE needs time to upload all data. Especially if you filter everything at the same time.
      As an alternative, I suggest you try our Combine Sheets add-on. We've just introduced our own formula there so your result could update automatically upon changes in source sheets. Please visit the help page for more details.

      3) The more complex your formula gets and the more data it processes, the more time it is required to get the result. Of course, a strong and stable Internet connection is vital here. But the power of your machine is as important.

  32. Hi Natalia,

    I am using four survey forms that export its results to four different google sheets. The surveys constantly get filled out and google sheets get new data on a daily basis.

    I would like all of the results to be combined into one master sheet but is it even possible for the file to be autopopulated whenever one of the sheets with results gets a new entry?

    1. HI Oleg,

      I guess you'll get what you described if you use the Combine Sheets add-on and use the "Use formula" option on the last step of the add-on. That option was designed exactly for that case.

    1. Hi Mauro,

      The thing is, you work with Italy locale in the spreadsheet: it requires a semicolon as a delimiter. In my formula, there's a comma before "select" since I work with a different locale. Just replace that comma with the semicolon symbol, and the formula will work on your side:
      =QUERY({'Spring 2019'!A2:D7;'Summer 2019'!A2:D7};"select * where Col1''")

  34. Hello,
    Is there a way to write the query formula to bring in the data regardless of of it is text, date, number, etc.? I have some columns that contain a mix of both numeric and text data and it appears that only the numbers are being brought in. This question is in reference to the query section above:

    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"

    1. Hello Jordan,

      Unfortunately, QUERY has a limit regarding mixed data in a column. Each column can only hold one data type. If there are mixed data in a column, the function processes the majority data type returning the rest as empty cells.
      Thus, if numbers are your majority type in the column, I'm afraid there's no way to make QUERY pull the rest data from the same column as well.

  35. i create a new tab each day taht is a duplicate template of the previous. the difference being the volumes used in each tab. each tab is the day. i would like to combine the data from each date on one spreadsheet so I can see the totals for the month for each column that interests me. i have an idea of how to do it but i dont know how to execute my idea. any help would be great thanks

    1. Hello Colin,

      For us to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 3 sheets: a couple of sheets with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
      We'll look into it.

  36. Hi,

    I have two sheets named sheet1 & sheet2.
    I want to apply formula/function in sheet1 so that it can pull data from sheet2.

    Waiting for your tips.

    Thanks.

  37. Thank you for this forum. I have successfully combined multiple sheets into one document using your help! I have 2 data sheets that people add names to a list and I have combined both sheets into a Master doc. I have a need to add on an additional columns to track notes in the Master data. My problem is when new names are added onto the two other data sheets, the feed to the Master data tab is not in order and the notes are shifting in the column I created only on the Master tab, messing up the notes and making it not applicable to row once new data is added. Do you have a solution or work around please? Thank you so much!

    1. Hello Kitesha,

      I'm afraid I need more details to be able to help you out. Please consider sharing a small sample of your Master spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. 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, please do not email there. Once you share the file, just confirm by replying here.

      I'll look into your data and do my best to help you.

  38. I am using Query to pull info from multiple tabs, how can I also pull in the color of the cell in the imported information.

  39. Hi Natalia,
    Thank you for providing such helpful information.
    My question is similar to question 12: I have 3 Google Forms that I want to merge into one document with separate 3 tabs. These are active forms that I want to still capture incoming information. Is this possible?

    Thanks in advance!

    2. Hi Ahtziri,

      Glad to know our blog is helpful!
      Unfortunately, we haven't come up with a way for our add-ons to solve this task yet. So I'm afraid for now my answer is the same: QUERY and IMPORTRANGE is the best way to pull data and make sure it updates along with source tables.

  40. Natalia! this has been so helpful. Thank you very much.
    However xD
    I tried to combine two tabs from different Spreadsheet. Getting an Error (ARRAY_LITERAL, an Array Literal was missing values for one or more rows)
    I wonder if its because a large amount of data? Since both of them has up to Column BU, 6100 & 700 rows respectively. My first file has couple of blank rows (4-5) within the data.

    Any insight? and thank you once again =)

    1. Hello NC,

      First, please make sure all IMPORTRANGE functions you use have permissions to pull data. I’d advise you to enter each IMPORTRANGE on a separate sheet and grant access to each of them.
      If this doesn't help, try to create a formula like this:
      ={IMPORTRANGE();IMPORTRANGE()}

      If it doesn't work as well, then I'm afraid there's a problem on Google side preventing loading data quickly and correctly. You can also try clearing cache in your browser.

  41. Thank you so much for this. I often pull rows of data from google form submissions in a sheet into other tabs within the sheet based on a specific answer in a certain column. In this case, I am looking to pull the rows of data with the word "Katski" in column AF. This formula works when I remove one of the sheets to pull from, but not when I have both listed. For example, this formula works:

    =QUERY(Haynes!A2:AF, "Select * Where AF = 'Katski'")

    When I add the second sheet to attempt to pull from, as seen in the formula below, it says no column AF which does in fact exist in both sheets.

    =QUERY({Haynes!A2:AF,Jitiam!A2:AF}, "Select * Where AF = 'Katski'")

    Changing the comma between the two names of the sheets to a semicolon does not do anything either. Any ideas? Thanks!

    1. Hello Justin,

      When listing conditions (select, where, etc), please replace column labels (A, B,..., AF) with order numbers (Col1, Col2,... Col32) if pulling data from multiple sheets, like this:
      =QUERY({Haynes!A2:AF,Jitiam!A2:AF}, "select * where Col32 = 'Katski'")

      Please look for more examples in this part of the article above.

      1. Hi Natalia,
        That makes perfect sense. Changing it to Col32 did the track. Thank you so much for your prompt reply!!
        Justin

  42. Hi there,

    thank you for the insightful article. I am currently trying to make my import range document work that it would import range based on two conditions. I managed to make it work under one condition (simply added "where Col35='x'" at the end of the query - full function below). Now I would like to add an additional condition, to only importrange where Col36 is either 'In Progress' OR 'Outstanding' OR 'Urgent'. I am trying to make a comprehensive to-do list when everyone could see only the tasks that are not finished yet (esentially the only option that would not import would be 'Complete') Is there some quick way to do this?

    =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/BLAH BLAH BLAH","Comprehensive publishing calendar!B:AK"),"where Col35='x'")

    1. Thank you very much in advance for your help!!
      Alex

      1. Hi Alex,

        Sure, there is a way. You just need to list all your conditions in your QUERY like this:
        "where Col35='x' and Col36 !='Complete'"

        This will skip all rows with the status Complete pulling all the rest.
        Feel free to read more about QUERY and its Where clause in this blog post.

        1. Excellent, this helped a lot!! Thank you very much! :)

  43. Hi, Thank you for your article and for providing a space in which to ask questions.
    My situation is this, I have 2 spreadsheets:
    > Spreadsheet 1 (Budget) is a summary of yearly expenses for each month (from different categories (eg. rent, utilities, grocery spending, etc) that are the rows and columns are the months of the year.
    > Spreadsheet 2 (Varaible Expenses) has 12 tabs (January to December). In each tab there are columns for each spending category that can vary each month. So Groceries is a header in two merged cells, and under it are two columns, one for the store name, and one for amount spent. At the bottom (row 40) there is a total of the expense for the category. There are about 15 categories and totals. But between each total, there is a blank cell because the store names are in that column.
    My issue is that I want to import the totals from each category for each month into the Budget spreadsheet. For example, in the Budget spreadsheet, there is a row call Groceries, and there are 12 columns for each month. So 12 cells to fill. I want to Fill those 12 cells with the Groceries total on the January tab, the February tab, March tab etc.
    I appreciate any advice you can offer. Thanks again for providing this service.

  44. Hello,
    I can't seem to figure this one out.
    =SUMPRODUCT(IMPORTRANGE("15PUcrFFxb6OI40m6KI0iJczjSF-1-v3VSEWvftZa1uQ","PO #001!E18:E35"),IMPORTRANGE("15PUcrFFxb6OI40m6KI0iJczjSF-1-v3VSEWvftZa1uQ","PO #001!A18:A35")=A7)
    How can I grab PO # 003, PO #004, PO #005 etc. from this same workbook so that it adds the totals from all the sheets into one cell on my budget sheet?

    1. Hello Jared,
      There's no need to incorporate IMPORTRANGE to sum the numbers unless you need to combine all records on one sheet and then calculate there.

      For me to understand your task better, please share a small sample spreadsheet with us (support@apps4gs.com): please include an example of your source sheets (up to 3) and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. You can replace any confidential info 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 here.

      I'll look into your task and try my best to help.

  45. Hi
    I have multiple sheets in one google sheet
    Response will save according to radio button selected in Google form in respective sheet.
    how can I do this?

  46. Hi Natalia,
    I am looking to combine multiple sheets into one using your Query method. I am running into issues with the ,"select * where Col1''". Before I enter this portion of the formula, I have content in my sheet, but it is very spaced out with many rows in between. It was my hope that the addition of "select * where Col1''", would remove any blank cells, but when I add it, all information from the sheet disappears with the exception of the header column.

    Ultimately, I would like to take 9 separate sheets and combine them into one. The 9 sheets are actively collecting responses from Google Forms, and I would like the Master sheet to populate with new responses in a row automatically. Any input?

    I see the add ons have to be run every time the sheets are updated, other wise I would use them.
    THANK YOU!

  47. Hi Natalia,
    Thank you for sharing this information. I have a question in regards to a google sheet project Im currently working on that I was hoping you may be able to help with.

    My question is how can I combine multiple sheets into one 'master sheet' without having duplicated names, age etc? So that any changes (adding and subtracting names and information) in the tabs automatically updates the master slide. What would you advise to be best way to go about this?

    Im have multiple tabs referencing years (2021, 2022, 2023 and so on) and some of the information, in particular names, appear on more than one occasion across the tabs. I tried a Query formula but that resulted in having multiple duplicates in my master sheet that I wasn't able to remove without effecting the specific yearly tab.

    1. Hi Jarrod,

      The result of the QUERY cannot be deduplicated because it's a formula. If you convert it to values, you'll be able to remove duplicates without affecting the source data.
      I believe it's the best way if you don't want to use add-ons and are not familiar with Google Apps Script.

  48. Hi Natasha,
    I used the =IMPORTRANGE and it worked but I had a few of the tabs in the original google sheet highlighted. When I imported the data, it was no longer highlighted. Do you know how I can do that?

    Best,
    Sarah

  49. Hi Natalia,

    I need to combine responses from multiple google forms into one google spreadsheet, on one tab. Will this work considering that the google forms are continuously being filled out?

    Thanks,
    Jade

  50. Hello Natalia,
    Thank you for sharing this. I've been using importrange for a while and it's been working great for us.
    I'm hoping i can step things up a notch.

    Q: Can we have a list of spreadsheet IDs (or URL) that a Script uses to add to a QUERY of several IMPORTRANGE as oppose to having to manually edit to formula to add each added spreadsheet?

    Thank you!

    1. Hello Guillaume,

      Technically, Script lets you automate lots of different tasks. But sadly, we do not cover the programming area (script-related questions).

      You may try to find an answer here – an overview of Google Apps Script with a lot of helpful content and links:
      https://developers.google.com/apps-script/overview

      I hope you’ll find this information helpful. I wish I could assist you better.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)