Library quickstart

  • Learn how to build and use an Apps Script library to remove duplicate rows in Google Sheets.

  • Set up the script by creating a new Apps Script project, adding the provided code, and deploying it as a library.

  • Run the script in a Google Sheet by adding the library to your project, calling theremoveDuplicates function, and executing the script.

  • Understand the code that retrieves data from the sheet, identifies unique rows, clears the sheet content, and inserts the unique data back into the sheet.

  • Modify the script to remove duplicates based on specific columns instead of entire rows.

Build anApps Script library that you can use to remove duplicate rows in spreadsheet data.

Objectives

  • Set up the script.
  • Run the script.

Prerequisites

To use this sample, you need the following prerequisites:

  • A Google Account (Google Workspace accounts mightrequire administrator approval).
  • A web browser with access to the internet.

Set up the script

To build the library, take the following steps:

  1. Sign in to your Google Account.
  2. To open the script editor, go toscript.google.com.
  3. At the top left, clickNew project.

  4. Delete any code in the script editor and paste in the code below.

    sheets/removingDuplicates/removingDuplicates.gs
    /** * Removes duplicate rows from the current sheet. */functionremoveDuplicates(){constsheet=SpreadsheetApp.getActiveSheet();constdata=sheet.getDataRange().getValues();constuniqueData={};for(constrowofdata){constkey=row.join();uniqueData[key]=uniqueData[key]||row;}sheet.clearContents();constnewData=Object.values(uniqueData);sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);}

  5. Click SaveSave icon.

  6. At the top left, clickUntitled project.

  7. Name your scriptRemove duplicate rows and clickRename.

  8. ClickDeploy>New deployment.

  9. Next toSelect type click Enable deployment typesThe icon for enable deployment types>Library.

  10. Enter a description of the library, such asRemove duplicate rows. Anyonewith access to the library can view this description.

  11. ClickDeploy.

  12. At the left, clickProject settingsThe icon for project settings.

  13. UnderIDs, copy the script ID for use in a later step.

Run the script

To use a library, you must have at least view permissions for itsApps Script project. Since you created the library, you have therequired permissionsto use it. If you want to let others use the library, give them view permissionfor the Apps Script project.

To use the library, take the following steps:

  1. Open a Google Sheets spreadsheet that has data with duplicate rows. To usea sample spreadsheet,make a copy of theSample duplicate rows spreadsheet.
  2. ClickExtensions>Apps Script.
  3. Next toLibraries, click Add a library.
  4. In theScript ID section, paste the script ID from the library Apps Scriptproject you copied in the previous section.
  5. ClickLook up.
  6. In theVersion section, select1.
  7. ClickAdd.

  8. Delete any code in the script editor and paste in the code below.

    function runLibrary() { Removeduplicaterows.removeDuplicates();}

  9. In the function dropdown, selectrunLibrary.

  10. ClickRun.

  11. Return to the spreadsheet to view the updated data without duplicate rows.

Review the code

To review the Apps Script code for this solution, clickView source codebelow:

View the source code

First, the script makes a single call to the spreadsheet to retrieve all thedata. You can choose to read the sheet row by row, but JavaScript operations areconsiderably faster than talking to other services like Spreadsheet. The fewercalls you make, the faster it goes. This is important because each scriptexecution has a maximum run time of 6 minutes.

sheets/removingDuplicates/removingDuplicates.gs
constsheet=SpreadsheetApp.getActiveSheet();constdata=sheet.getDataRange().getValues();

The variabledata is a JavaScript 2-dimensional array that containsall the values in the sheet.newData is an empty array where thescript puts all the non-duplicate rows.

sheets/removingDuplicates/removingDuplicates.gs
constnewData=Object.values(uniqueData);

The firstfor loop iterates over each row in thedata2-dimensional array. For each row, the second loop tests if another row withmatching data already exists in thenewData array. If it’s not aduplicate, the row is pushed into thenewData array.

sheets/removingDuplicates/removingDuplicates.gs
uniqueData[key]=uniqueData[key]||row;

Finally, the script deletes the existing content of the sheet and insertsthe content of thenewData array.

sheets/removingDuplicates/removingDuplicates.gs
sheet.clearContents();constnewData=Object.values(uniqueData);sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);

Modifications

You can edit the library as much as you'd like to fit your needs. Below is anoptional modification.

Remove rows with matching data in some columns

Instead of removing rows that match entirely, you might want to remove rows withmatching data in just one or two of the columns. To do that, you can change theconditional statement.

In the sample code, update the following line:

if(row.join()==newData[j].join()){duplicate=true;}

Replace the line with the following code:

if(row[0]==newData[j][0]&&row[1]==newData[j][1]){duplicate=true;}

The above conditional statement finds duplicates each time two rows have thesame data in the first and second columns of the sheet.

Contributors

This sample was created by Romain Vialard, a Google Developer Expert. FollowRomain on Twitter@romain_vialard.

This sample is maintained by Google with the help of Google Developer Experts.

Next steps

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-11 UTC.