I have two tables in two different sheets of my Google Spreadsheet that contain the same information. I now want to have a third sheet where I combine these two tables into one and sort them by a column.

Is there a way to achieve that that is not manually adding the second range at the end of the first range of data?


Providing you have your column headers in the first row, this script should work perfectly.

I have tried to explain every step I am taking but if theres anything that does not work or you do not understand let me know.

I am new to scripts myself but this is one I have written a few times.

function merge() {
  //Start by getting the ID of the first spreadsheet you wish to copy from and also make sure to change the sheet name
 var s1 = SpreadsheetApp.openById("place ID here").getSheetByName("Sheet1");
  //Now do the same for the second sheet you wish to copy from
 var s2 = SpreadsheetApp.openById("place ID here").getSheetByName("Sheet1");
  //Again for the sheet you wish to copy to
 var s3 = SpreadsheetApp.openById("Place ID here").getSheetByName("Sheet1");

  //Now we are going to define the ranges we wish to copy as so, row,column,row,column, these are numeric so starting with A1 and finsihing with C2 would be 1,1,2,3
  //I am going to assume you want to start with A2 and collect all the data you have on the sheet

var r1 = s1.getRange(2,1,s1.getLastRow(),s1.getLastColumn()).getValues()
var r2 = s2.getRange(2,1,s2.getLastRow(),s2.getLastColumn()).getValues()

//Now we will paste that data, starting at the last blank row
var p1 = s3.getRange(2,1,s1.getLastRow(),s1.getLastColumn()).setValues(r1)
var p2 = s3.getRange(s3.getLastRow(),1,s2.getLastRow(),s2.getLastColumn()).setValues(r2)
  • Thanks, definitely will try that out! Somewhat strange, that this is not provided by standard.
    – jk31
    Feb 15 at 9:42

You could use the following formulas

To import the data from two spreadsheets use import range on two sheets, example below, =importrange("pasteYourUrlHere","Sheet1!A2:J")

Then on the third sheet we can merge the two sheets and filter by column A using a filter function, see example below

={filter(Sheet1!A7:J, len(Sheet1!A7:A)); filter(Sheet2!A7:K, len(Sheet2!A7:A))

If this answer works please click on the tick and up vote my answer

  • Not exactly, what I am looking for. I have two tables with the exact same column names. It's for a roster of our basketball online league. So just as an example, we have: "Forename", "Name" and "Position". In the second sheet, again, we have "Forename", "Name" and "Position". I now want a third sheet where both other sheets are merged, so that again I only have these three columns "Forename", "Name" and "Position", but now it combines all entries from both other sheets. Your solution gives me 6 columns in the end ("Forename1", "Name1", "Position1", "Forename2", "Name2" and "Position2").
    – jk31
    Feb 17 at 13:37
  • In that case the ={filter function I used in my answer would work, you would just need to change the sheets names and the range to suit your spreadsheet. I have created a test sheet so you can see it in action, if its what your looking for make a copy and the formulas will carry over. docs.google.com/spreadsheets/d/… Feb 17 at 16:05
  • strange, I used that yesterday after you proposed it (in your first answer) and it only pasted the results side by side increasing the column number to 6. Now that I copied it from you, it works. Thanks! Don't know what I did wrong...Nice and easy solution.
    – jk31
    Feb 19 at 7:27
  • Glad I could be of help, could you mark it as answered by uising the tick on my answer please? Feb 22 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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