Organising and analysing data in Google Sheets is easy, but dealing with duplicate entries can be frustrating. But don’t worry; Google’s scripting platform, Apps Script, provides an easy way to remove duplicates in your spreadsheets. With basic coding, you can automate the process of identifying and deleting duplicate rows, saving time and hassle.
In this article, I’ll show you how to use Apps Script to quickly and efficiently remove duplicates from your Google Sheets. Whether working with a small dataset or a large one, this powerful tool can help you keep your data clean and organised.
Table of Contents
What is Apps Script?
Apps Script is a JavaScript-based programming language that allows you to add custom functionality to your Google Apps. With Apps Script, you can connect and automate different Google services and even connect to external services and APIs.
In simple words, Apps Script is a cloud-based platform that enables you to create scripts and automate tasks in your Google Workspace (formerly known as G Suite) products. This includes Google apps, such as Google Sheets, Docs, Forms, and Gmail.
How to Use Apps Script in Google Sheets
To use Apps Script in Google Sheets, you need a Google account and open the Script Editor from within one of the Google Apps that support it. Then, you can create a new script and start writing your code. After writing your script, you can test and run it directly from the Script Editor.
Apps Script is easy to learn, especially if you already have experience with JavaScript. The syntax is similar to JavaScript, and the platform provides a set of built-in libraries and functions that make it easy to interact with Google services. Also, a large community of developers creates and shares scripts and tutorials on how to use the platform.
How to Remove Duplicates in Google Sheets with Apps Script
To remove duplicates in Google Sheets, you have to open the sheet. Then, select Extensions > Apps Script > write the remove duplicates code into the script editor > click Save. Review the data before deleting, then run the removeDuplicates function. After that, the code execution will remove duplicates from the sheet if you have allowed its access via your Google account.
Apps Script is a powerful tool to add custom functionality to your Google Sheets. With some coding, you can use it to remove duplicates from your spreadsheets. Here’s how:
- Open the Google Sheet you want to remove duplicates from.
- Select Extensions from the top bar.
- Select Apps Script from the dropdown menu.
- The Apps Script editor will open in a new tab.
- Paste the following code in the script editor:
function removeDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var newData = new Array();
for(i in data){
var row = data[i];
var duplicate = false;
for(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);
}- Press the Save button to save the script.
- Go back to your Google Sheet to run the script and tap Extensions > Apps Script.
- Click the removeDuplicates function and then press the Run button.
- A prompt will appear asking for confirmation before removing duplicates. Tap Review permissions to review the data before deleting.
- You’ll be asked to log in to your Google account.
- After selecting your Google account, you must Allow access to the Google Sheet.
- As soon as you click Allow, the code execution will start. When it completes, your Google Sheet will have no duplicates.
And that’s it. Your duplicates will be removed, leaving clean and organised data.
5 Quick Tips and Best Practices when using Apps Script
Some quick tips and best practices when using Apps Script to remove duplicates in Google Sheets are to always test on a copy of data, be patient with large datasets, and check for errors. Moreover, you should run the script on each sheet separately or use a loop. Also, establish a consistent naming convention to prevent future duplicate data.
Here are a few tips and best practices to keep in mind when using Apps Script to remove duplicates in Google Sheets:
- Test the script on a copy of your data first to prevent accidentally deleting important information.
- A large dataset may take longer for the script to run. Be patient, and don’t interrupt the script while it’s running.
- If you encounter any errors, check the script for typos or mistakes, and ensure you’ve followed all the steps correctly.
- If you want to remove duplicates across multiple sheets, you can run the script on each sheet separately or use a loop to run the script on all sheets in the workbook.
- To prevent the creation of duplicate data in the first place, establish a consistent naming convention for your files and review and clean your data regularly.
Removing duplicates from your Google Sheets data can be a real pain, but with the help of Apps Script, it doesn’t have to be. You can easily remove duplicates in Google Sheets to keep your data clean and organized using the script provided in this article.
Remember to test the script on your data copy first and be patient if you have a large dataset. Also, remember to implement best practices for data management and organization to prevent the creation of duplicate data in the first place.
What Tasks can Apps Scripts Perform?
Apps Scripts can perform a wide range of tasks, from automating simple tasks to building complex applications with less time and effort. It also creates custom add-ons and macros for Google Docs and Sheets to extend the functionality of these apps.
For example, you can sort and organise data automatically in a Google Sheet or send personalised emails to a list of contacts in a Google Sheet. Some other examples of what you can do with Apps Script include:
- Automating repetitive tasks, such as data entry or email sending
- Connecting Google Sheets to external services and APIs
- Creating custom add-ons and macros for Google Docs and Sheets
- Building web applications that are accessible through a web browser
- Creating custom scripts to integrate different Google services
Overall, Apps Script is a powerful platform to automate tasks, extend the functionality of Google Apps, and connect to external services, making it a valuable tool for personal and professional use.
Why is it Important to Remove Duplicates in Google Sheets?
When it comes to data analysis, accuracy is the key. As duplicate data in your spreadsheet can skew your results, which makes it difficult to get a clear picture of what’s exactly happening. That’s why it is important to remove duplicates in Google Sheets.
Besides that, it saves space in your spreadsheet and makes your data easier to read. Also, a well-organised spreadsheet saves time and reduces human error, especially if you are working with a large dataset. Finally, it can improve your spreadsheet’s performance, as Google Sheets may have to process a large number of duplicates which can slow down the processing time.
Frequently Asked Questions
Can I use Apps script to remove duplicates in Google Sheets with multiple worksheets?
Yes, you can use Apps script to remove duplicates in a Google Sheet with multiple worksheets by modifying the script to select a specific worksheet. Remember that the script will only remove duplicates within a worksheet, not across multiple worksheets.
How can I ensure that my data is not lost after running the script?
Always back up your data before running the script. You can also test the script on a copy of your data first. The script also includes a prompt asking for confirmation before removing duplicates, allowing you to review the data before deletion.
Can I use this script to remove duplicates across multiple sheets?
Unfortunately, the script is designed to remove duplicates within a single sheet. However, you can run the script on each sheet separately or use a loop to run the script on all sheets in the workbook.
How to remove duplicates in Google Sheets?
To remove duplicates in Google Sheets, you have to:
1. Open Google Sheets > select Extensions > Apps Script.
2. Copy the remove duplicates code from the article above and paste it into the script editor.
3. Next, click the save icon in the top navigation bar.
3. Review the data before deleting, then run the removeDuplicates function in the script editor.
4. Log in to your Google account and allow access to your Google sheet.
5. Now, the code execution will remove duplicates from your Google Sheet.
