How to Merge Sheets in Google Sheets

Here, we will create an Apps Script that will merge multiple sheets into one. It will work even though different sheets have different column header casing. Meaning, even if the column header in one sheet is called item and Item on another, it will still work. It will also work even if the columns are not in order. For example, in one sheet, the ordering of columns is: id, item, description and the ordering for another is: ID, Description, ITEM. Our solution should still work even if this is the case.

Let’s get started.

Open your Google Sheets file that contains the sheets that you want to merge. Click Extensions > Apps Script.

You will see a screen like this. This is a Code.gs file where we can put Apps Script codes.

Copy this Code.gs file and paste it to replace everything in your Code.gs.

Your Code.gs file will now look something like this.

Click save.

Back to Google Sheets, refresh the page and wait for the Merge menu to appear.

Click it, then Start.

Click OK when prompted by Authorization required.

Login to your Google account. Take note that the title of your Apps Script project will show here.

Click Advanced.

Click Go to <project-name> (unsafe).

Take note that the above is fine even if it says not verified or unsafe. It’s your apps script, so it’s fine.

Read the permissions that you will allow (might not be the same as the screenshot) before proceeding. Then click Allow.

After authorization, click the Merge > Start again to actually run the script.

Wait for the script to finish.

When finished, you’ll have a new sheet named combined that contains all your sheets.