Copying and Manipulating Data with Google Apps Script

by

In this blog post, we will explore how to use Google Apps Script to copy data from one Google Sheet to another. Specifically, we will copy five columns, excluding one column, and ensure that our destination sheet has an auto-incrementing number for the first column. Additionally, we will eliminate any duplicate rows in the process. This can be incredibly useful for organizing and cleaning up your data in Google Sheets.

Sample Data Table

Let’s start with a sample data table in a Google Sheet named SourceSheet. This table has the following columns:

ABCDEF
NameAgeOccupationIgnoreCountrySalary
John25EngineerIgnore1USA60000
Alice30DoctorIgnore2UK70000
Bob22StudentIgnore3Canada20000
John25EngineerIgnore4USA60000
Alice30DoctorIgnore5UK70000

Goals

  1. Copy columns A, B, C, E, and F from SourceSheet to DestinationSheet.
  2. Add an auto-incrementing number as the first column in DestinationSheet.
  3. Eliminate duplicate rows in DestinationSheet.

Google Apps Script Code

Below is the Google Apps Script code to accomplish this task:

function copyAndFilterData() {
// Open the spreadsheet and sheets
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName(‘SourceSheet’);
var destSheet = ss.getSheetByName(‘DestinationSheet’);

// Get the data from the source sheet
var sourceData = sourceSheet.getDataRange().getValues();

// Extract the relevant columns (A, B, C, E, F)
var filteredData = sourceData.map(function(row) {
return [row[0], row[1], row[2], row[4], row[5]];
});

// Remove header row
var header = filteredData.shift(); // Remove header row
// Remove duplicates
var uniqueData = removeDuplicates(filteredData);

// Add auto-incrementing numbers as the first column
var numberedData = uniqueData.map(function(row, index) {
return [index + 1].concat(row);
});

// Add header to the numberedData
numberedData.unshift([‘ID’].concat(header));

// Clear the destination sheet and set the new data
destSheet.clear();
destSheet.getRange(1, 1, numberedData.length, numberedData[0].length).setValues(numberedData);
}

function removeDuplicates(data) {
var seen = {};
return data.filter(function(row) {
var key = row.join(‘-‘);
return seen.hasOwnProperty(key) ? false : (seen[key] = true);
});
}

Explanation

  1. Extract the Relevant Columns:var filteredData = sourceData.map(function(row) { return [row[0], row[1], row[2], row[4], row[5]]; }); This ensures that we extract the necessary columns: A, B, C, E, and F.
  2. Remove Header Row:var header = filteredData.shift(); This removes the header row from the filtered data and stores it separately.
  3. Remove Duplicates:var uniqueData = removeDuplicates(filteredData); This ensures that any duplicate rows are removed.
  4. Add Auto-Incrementing Numbers:var numberedData = uniqueData.map(function(row, index) { return [index + 1].concat(row); }); This adds an auto-incrementing number as the first column for each row in the unique data.
  5. Add Header to Numbered Data:numberedData.unshift(['ID'].concat(header)); This adds the header back to the top of the numbered data array.
  6. Clear and Set New Data in Destination Sheet:destSheet.clear(); destSheet.getRange(1, 1, numberedData.length, numberedData[0].length).setValues(numberedData); This clears the DestinationSheet and sets the new data with the header, auto-incrementing ID, and filtered columns.

Conclusion

This corrected script ensures that all rows, including the second row, are processed and correctly added to the DestinationSheet. By following the steps outlined in this blog post, you can efficiently manage your data in Google Sheets using Google Apps Script. Happy scripting!