Quickstart: Library

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

Before you begin

To use this sample, you need the following prerequisites:

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

Set it up

To build the library, take the following steps:

  1. Sign in to your Google Account.
  2. To open the script editor, go to script.google.com.
  3. At the top left, click New 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.
     */
    function removeDuplicates() {
      let sheet = SpreadsheetApp.getActiveSheet();
      let data = sheet.getDataRange().getValues();
      let newData = [];
      for (let i in data) {
        let row = data[i];
        let duplicate = false;
        for (let j in newData) {
          if (row.join() == newData[j].join()) {
            duplicate = true;
          }
        }
        if (!duplicate) {
          newData.push(row);
        }
      }
      sheet.clearContents();
      sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
    }
  5. Click Save Save icon.

  6. At the top left, click Untitled project.

  7. Name your script Remove duplicate rows and click Rename.

  8. Click Deploy > New deployment.

  9. Next to Select type click Enable deployment types The icon for enable deployment types > Library.

  10. Enter a description of the library, such as Remove duplicate rows. Anyone with access to the library can view this description.

  11. Click Deploy.

  12. At the left, click Project settings The icon for project settings.

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

Try it out

To use the library, take the following steps:

  1. Open a Google Sheets spreadsheet that has data with duplicate rows. To use a sample document, make a copy of the Sample duplicate rows spreadsheet.
  2. Click Extensions > Apps Script.
  3. Next to Libraries, click Add a library .
  4. In the Script ID section, paste the script ID from the library Apps Script project you copied in the previous section.
  5. Click Look up.
  6. In the Version section, select 1.
  7. Click Add.
  8. Delete any code in the script editor and paste in the code below.

    function runLibrary() {
     LibraryQS.removeDuplicates();
    }
    
  9. In the function dropdown, select runLibrary.

  10. Click Run.

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

Review the code

Learn about the code used in this library sample

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

sheets/removingDuplicates/removingDuplicates.gs
let sheet = SpreadsheetApp.getActiveSheet();
let data = sheet.getDataRange().getValues();

The variable data is a JavaScript 2-dimensional array that contains all the values in the sheet. newData is an empty array where the script puts all the non-duplicate rows.

sheets/removingDuplicates/removingDuplicates.gs
let newData = [];

The first for loop iterates over each row in the data 2-dimensional array. For each row, the second loop tests if another row with matching data already exists in the newData array. If it’s not a duplicate, the row is pushed into the newData array.

sheets/removingDuplicates/removingDuplicates.gs
if (!duplicate) {
  newData.push(row);
}

Finally, the script deletes the existing content of the sheet and inserts the content of the newData array.

sheets/removingDuplicates/removingDuplicates.gs
sheet.clearContents();
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 an optional modification.

Remove rows with matching data in some columns

Instead of removing rows that match entirely, you might want to remove rows with matching data in just one or two of the columns. To do that, you can change the conditional 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 the same data in the first and second columns of the sheet.

Contributors

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

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