Optimize for-loop function in Google Apps Script (Arrays maybe)?

Question

First-time poster here. I would like some insight on some Google App Script code i think could be spruced up a bit.

Long story short.

I have a 2 Google Sheet tables

• A “LOCALE” spreadsheet - listing unique names of locations

• A “FEED” spreadsheet - listing photo descriptions, including the locations. The same location is listed multiple times in this spreadsheet.

• Both of these tables have a “Location” column, which references each other with a Key column.

The problem I want to solve for:

When I edit a location name in the “LOCALE” spreadsheet, it should automatically update all the location names in the “FEED” spreadsheet.

The way I solved this problem:

I used a for loop within a for loop for this. To summarize:

• for every row in "LOCALE"...
• ..go through every row in "FEED"...
• ...If a value in the Key column in the FEED Sheet matches a value in the Key column in the LOCALE Sheet...
• ...but the value in the Location column in the FEED Sheet doesn't match the value in the Location column in the LOCALE Sheet...
• ...update the Location column in the FEED Sheet with the value in the Location column in the LOCALE Sheet.

If you're not confused yet, here's the code i wrote for it:

// for each row in the "Locale" sheet... 
for(var L = LocationsRefValues.length-1;L>=0;L--) {

  // for each row in the "Feed" sheet...
  for(var F = FeedRefValues.length-1;F>=0;F--) {

    if (FeedRefValues[F][97] == LocationsRefValues[L][17] && 
        FeedRefValues[F][10] != LocationsRefValues[L][1]) {
       FeedDataSheet.getRange(F+2,10+1).setValue(LocationsRefValues[L][1]);
    }       
  }
}

Now, this code works perfectly fine, I've had no issues. However, i feel like this a bit clunky, as it takes a while to finish its edits. I'm certain there's any easier way to write this and run this code. I've heard arrays may address this situation, but i don't know how to go about that. Hence, why I'm looking for help. Can anyone assist?

Keep in mind I'm a total Google App Script beginner who got this code working through sheer dumb luck, so the simpler the solution the better. Thanks for any consideration to my problem in advance. Looking forward to hearing from you all.

This is the full function (after i made edits suggested here.)

   function ModeratorStatus() {

  var Data = SpreadsheetApp.getActiveSpreadsheet(); // Local Spreadsheet

  var ModeratorStatusDataSheet = Data.getSheetByName("The Status (Moderators)");
  var ModeratorStatusRange = ModeratorStatusDataSheet.getRange("A2:C");
  var ModeratorStatusRefValues = ModeratorStatusRange.getValues();

 var ModeratorDataSheet = Data.getSheetByName("The Moderator_Numbers");  // DATA "Member" sheet
  //var ModeratorRefValues = ModeratorDataSheet.getRange("A2:AD").getValues();

  var ModeratorStatusObj = {};
for (var MOS = ModeratorStatusRefValues.length-1; MOS>=0; MOS--) {
  ModeratorStatusObj[ModeratorStatusRefValues[MOS][2]] = ModeratorStatusRefValues[MOS][0];
}
  var ModeratorValues = ModeratorDataSheet.getRange("A1:AD").getValues();
for (var MO = ModeratorValues.length-1; MO >=0; MO--) { // for each row in the "Moderator" sheet...
  var ModeratorVal28 = ModeratorValues[MO][28];
  if (ModeratorStatusObj[ModeratorVal28] != ModeratorValues[MO][1]) {

    ModeratorValues[MO][1] = ModeratorStatusObj[ModeratorVal28];
  }
}
var destinationRange = ModeratorDataSheet.getRange(1, 1, ModeratorValues.length, ModeratorValues[0].length);
destinationRange.setValues(ModeratorValues);

I used the code in a different function as a test. To make it easier

LOCALE = MODERATOR STATUS

FEED = MODERATOR

Answer 1

If there are no duplicate [17]s with different [1]s in the LocationsRefValues, you can reduce the computational complexity from O(n ^ 2) to O(n) by creating a mapping object for LocationsRefValues beforehand, whose keys are the LocationsRefValues[L][17]s and whose values are the LocationsRefValues[L][1]s:

var locationObj = {};
for (var L = 0; L < LocationsRefValues.length; L++) {
  locationObj[LocationsRefValues[L][17]] = LocationsRefValues[L][1];
}
for (var F = FeedRefValues.length - 1; F >= 0; F--) { // for each row in the "Feed" sheet...
  var feedVal97 = FeedRefValues[F][97];
  if (locationObj[feedVal97] != FeedRefValues[F][10]) {
    FeedDataSheet.getRange(F + 2, 10 + 1).setValue(locationObj[feedVal97]);
  }
}

Thanks @TheMaster, you can speed this up by calling setValue only once, at the end, rather than calling it in a loop, probably something along the lines of:

var locationObj = {};
for (var L = 0; L < LocationsRefValues.length; L++) {
  locationObj[LocationsRefValues[L][17]] = LocationsRefValues[L][1];
}
var feedValues = FeedDataSheet.getValues();
for (var F = FeedRefValues.length - 1; F >= 0; F--) { // for each row in the "Feed" sheet...
  var feedVal97 = FeedRefValues[F][97];
  if (locationObj[feedVal97] != FeedRefValues[F][10]) {
    feedValues[F + 2][10 + 1] = locationObj[feedVal97];
  }
}
var destinationRange = ss.getRange(1, 1, feedValues.length, feedValues[0].length);
destinationRange.setValues(feedValues);

Answer 2

you can use onEdit(e) trigger to get the reference to the edited cell. In that case you won't need to iterate over the entire Locale" sheet:

function onEdit(e) {
  var range = e.range; // edited cell
  var rowIndex = range.getRow()
  var colIndex = range.getColumn()
  if (rowIndex >= LocaleRange.startRow && rowIndex <= LocaleRange.EndRow && 
      colIndex >= LocaleRange.startColumn && colIndex <= LocaleRange.EndColumn) {
    var index = rowIndex - LocaleRange.startRow
    var keyValue = LocationsRefValues[index][17]
    var newLocValue = range.getValue()
    var newFeedValues = FeedRefValues.map(function (row) {
       return (row[97] == keyValue)  newLocValue ? : row[10]
    })
    FeedDataRange.setValues(newFeedValues)
  }
}

Here are docs on using onEdit trigger: https://developers.google.com/apps-script/guides/triggers