Comments on: How to 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: 234

  1. Hi Natalia,

    Just wanted to thank you for this article and helpful tips. Merging sheets - it is exactly what I’ve been looking for. Helps a lot.

    Thanks again,
    Nikolai

  2. Hi Natalia,

    I am having difficulties extracting data from 2 different google sheet workbooks into 1 (new) masterfile that would have 2 drop downs for categorizing the needed data. Would you be able to help me out?

    1. Hello Tuhin,

      The IDs in your last sheet are formatted as text, while in other sheets they are numbers. Since you use QUERY, you should know that 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.
      Hence, you need to make sure the data in ID columns across all combined sheets are formatted the same: as numbers.

  4. How would we use IMPORTRANGE if we dont know the exact cell id ?
    We want to get the data from another sheet from the cell with the same cell id.
    It's clear using specific cell id but on copy/paste you have to correct it.
    We tried using as cell ID ADDRESS(row(),cell()) but we get #REF! error.

  5. Dear Natalia Sharashova,

    I would like to monitor the workflow time scale of workflow. I need to add an extra date column or data will save in another separate Spreadsheet based on User Status and Developer Status in the sheet. For example, if a user raises a new ticket in user status that will be recorded in a new spreadsheet with the date in the same ticket no user changes the user states it recorded in the next column with a date as the same developer status also. For your reference here I share my sample sheet URL. Kindly do need full

    https://docs.google.com/spreadsheets/d/1wBheKbbMIJtM3zlmbWqOTIftsgttp0cH1K_7HKY5-VI/edit#gid=0

    With Thanks and Regards,
    Manikandan Selvaraj.

    1. Dear Manikandan Selvaraj,

      I'm sorry, I don't have access to your spreadsheet. To grant us access, please open the file, press the Share button at the upper right corner of Google Sheets and enter support@apps4gs.com. Please also make sure that your spreadsheet contains the example of the result you'd like to get. It is of great importance as it gives us a better understanding than any text description. Once you share the file, please reply to this comment. I'll look into it and try to help.

  6. While using Merge sheet/ combine sheet/ summery sheet add on , can I get source cell background color in master sheet or only cell values will be synchronized ?

    1. Thank you for your question, Jenis.

      As for Merge Sheets, if you paste the results to a new spreadsheet, the colors of the original main table will be pulled as well. Yet, the add-on doesn't overwrite the colors of the main sheet with the colors of the lookup tables.

      As for Combine Sheets, there's a special option for that – preserve formatting. However, it's not available if you combine using a formula, since formulas in Google Sheets don't support formatting.

      Consolidate Sheets, on its turn, doesn't pull formatting because it creates one aggregated report from multiple different tables and each of them can have its own formatting.

      Anyways, all add-ons offer fully-functional 30-day trial period. You can test them out and see if they work as you need.

  7. Please guide me to arrange combined data alphabetically

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  34. =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.

  35. =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

    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.

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

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

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

  40. Excellent. Thank you so much..
    The Combo of Query and Importrange solved the purpose.

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

  42. 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''")

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

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

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

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

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

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

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)