Hi, everyone! I am cleaning up a Google Sheet with 100k leads and I must only retain those data that are in the United States. Can someone help me please?

[himenoswift]

Here's an example dataset. I need to clean it by retaining only the office locations within the United States. I'm currently using the following code in Google Sheets, but it ends up removing most of the data. Please assist, and thank you!

  function filterLawyersInUS() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var filteredData = [];

  // Define a list of US states and cities (you can add more as needed).
  var usLocations = ["Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado", "Connecticut", "Delaware", "Florida", "Georgia", "Hawaii", "Idaho", "Illinois", "Indiana", "Iowa", "Kansas", "Kentucky", "Louisiana", "Maine", "Maryland", "Massachusetts", "Michigan", "Minnesota", "Mississippi", "Missouri", "Montana", "Nebraska", "Nevada", "New Hampshire", "New Jersey", "New Mexico", "New York", "North Carolina", "North Dakota", "Ohio", "Oklahoma", "Oregon", "Pennsylvania", "Rhode Island", "South Carolina", "South Dakota", "Tennessee", "Texas", "Utah", "Vermont", "Virginia", "Washington", "West Virginia", "Wisconsin", "Wyoming"];

  for (var i = 0; i < data.length; i++) {
    var location = data[i][1]; // Assuming the location is in the second column (B).
    if (isLocationInUS(location, usLocations)) {
      filteredData.push(data[i]);
    }
  }

  // Clear the existing data in the sheet.
  sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn()).clearContent();

  // Write the filtered data back to the sheet, starting from row 2.
  sheet.getRange(2, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
}

// Function to check if a location is in the US.
function isLocationInUS(location, usLocations) {
  for (var i = 0; i < usLocations.length; i++) {
    if (location.includes(usLocations[i])) {
      return true;
    }
  }
  return false;
}

}`




<img width="1409" alt="Screenshot 2023-10-24 at 6 54 07 PM" src="https://github.com/smartcontractkit/full-blockchain-solidity-course-js/assets/143093227/66de49c3-1e24-47de-9e7d-91ca25002aee">